Goals프로시저와 함수의 차이, 함수 선언, 호출, 문자열을 합치는( CONCAT or || ) 함수 만들기, 팩토리얼 함수 만들기Parameter(매개변수, 인자) vs Argument(인수, 전달인자)패키지 - 명세, 본체
Notes
- 프로시저 (Procedure) vs 함수 (Function)
- Procedure
(1) 정의 : 특정한 로직을 수행하기만 하고, 명시적으로 반환(return)하지 않는 서브 프로그램.
(2) 실행 : 다른 PL/SQL (프로그램) 내부에서 실행됨. SQL 문에서는 실행할 수 없음. --> 반환값이 없기 때문.
(3) 파라미터 : IN, OUT, IN OUT 모드들을 사용할 수 있다.
(4) 반환 : return문을 사용한 반환할 수 없음. OUT/ IN OUT 파라미터를 활용해서 반환 가능.
- Function
(1) 정의 : 특정한 기능을 수행한 뒤 반드시 결과값을 반환(return)하는 서브 프로그램.
(2) 실행 : 다른 PL/SQL (프로그램) 내부나, SQL 문장 안에서 실행 될 수 있다.
(3) 파라미터 : IN 모드의 파라미터만 사용 가능. IN은 생략.
OUT 파라미터를 사용한 FUNCTION은 문법적으로 문제가 없지만 SQL 문장에서 사용할 수 없다.
오라클에서는 OUT 파라미터를 사용하지 말 것을 권장.
(4) 반환 : 반드시 return문을 사용해서 1개의 값을 반환해야 한다.
- 함수 선언 : -- [ ] 생략가능
create [ or replace ] function 함수 이름 [(
파라미터1 타입 [:= 기본값1],
파라미터2 타입 [:= 기본값2],
...
)] return 리턴타입
is
begin
[exception-- 예외처리부]
end;
- 선언 예제 :
create or replace function my_add(
p_x number,
p_y number
) return number
is
v_result number; -- return값을 저장하기 위한 변수
begin
v_result := p_x + p_y;
return v_result; -- 함수를 호출한 곳으로 값을 반환(return). 리턴할 값을 명시.
end; -- p_x+p_y 를 리턴하고 v_result 변수를 선언하지 않아도 됨.
- 호출
-- SQL문에서 오라클 내장 함수의 결과값을 사용할 때 dual 더미 테이블을 사용.
select my_add(1,3) from dual;
-- 함수는 PL/SQL에서도 호출 가능
declare
v_result number;
begin
v_result := my_add(100,200);
DBMS_OUTPUT.put_line(v_result);
end;
/
- 문자열 합치기
-- 문자 두 개를 받아서 두 문자열을 하나로 합친 문자열을 리턴 : CONCAT or ||
create or replace function my_char_link(
str1 varchar2, -- 함수 타입에서는 파라미터의 길이 제한을 두지 못한다
str2 varchar2
) return varchar2
is
v_result varchar2(100); -- return값의 길이는 지정해야 한다.
begin
(1) select concat(str1, str2) into v_result from dual; --둘 다 가능
(2) v_result := concat(str1, str2);
return v_result; -- 대신에 str1||str2 를 return하면 concat함수를 쓰지 않아도 된다.
end;
/
--SQL 함수 호출
select my_char_link('Hello','World') from dual;
--PL/SQL 함수 호출
declare
v_rslt varchar2(100);
begin
select my_char_link(ename, job) into v_rslt
from emp where empno = 7654;
dbms_output.put_line(v_rslt);
end;
/
- Oracle Factorial 계산하는 함수 만들기
create or replace function fact(
p_num number
) return number
is
rslt number := 1 ;
begin
for i in 1..p_num loop -- p_num <= 0일 때 for문은 false가 되어 바로 loop가 종료. rslt = 1이 된다.
rslt := rslt * i;
end loop;
return rslt;
end;
/
- Parameter(매개변수, 인자) vs Argument(인수, 전달인자)
(1) Parameter : 함수, 프로시저를 선언할 때 전달받는 값을 저장하기 위해서 선언하는 변수
ex) create procedure proc_name ( param1 number, param2 varchar2 ... )
create function func_name ( pram1 number, param2 varchar2 ...) return number
(2) Argument : 함수, 프로시저를 호출할 때 전달하는 값
ex) proc_name( 123, 'abc' );
func_name(100, '가나다');
- Package(패키지)
기능 면에서 연관성이 높은 프로시저, 함수, 타입(레코드, 컬렉션), 커서 등을 모아둔 서브 프로그램
-- 패키지를 생성할 때는 패키지의 명세(specification) 와 본문(body)를 선언해야 한다.
(1) 패키지 명세 :
create [ or replace ] package 패키지이름
is
타입 선언;
변수 선언;
프로시저 프로토타입 선언;
함수 프로토타입 선언;
end;
/
(2) 패키지 본문(본체) :
create [ or replace ] package body 패키지 이름
is
패키지 명세(스펙)에서 선언된 프로시저/함수 등을 구현
end;
/
-- 패키지 명세 작성
create or replace package my_pkg
is
-- 타입 선언
type rec_emp_sal is record (
empno emp.empno%type,
ename emp.ename%type,
sal emp.sal%type
);
-- 변수 선언
num1 number := 0;
-- 프로시저 프로토타입(prototype) 선언
procedure my_proc(p_num number);
-- 함수 프로토타입 선언
function my_add(p_x number, p_y number) return number;
end;
--패키지의 본문(본체,body)를 작성
create or replace package body my_pkg
is
-- 패키지 명세에 선언된 프로시저 구현
procedure my_proc(p_empno number)
is
v_result my_pkg.rec_emp_sal;
begin
select empno, ename, sal
into v_result
from emp
where empno = p_empno;
dbms_output.put_line(v_result.empno || ', '
|| v_result.ename || ', '
|| v_result.sal);
end my_proc;
-- 패키지 명세에 선언된 함수 구현
function my_add(p_x number, p_y number) return number
is
begin
return p_x + p_y;
end my_add; -- 패키지에는 end가 많으므로 end 옆에 이름을 써준다.
end;
/
-- 패키지에 접근
declare
v_result my_pkg.rec_emp_sal;
begin
dbms_output.put_line(my_pkg.num1);
my_pkg.my_proc(7788);
end;
/
select my_pkg.my_add(1,2) from dual;
연습문제
-- 패키지 명세(specification) 작성
create or replace package my_stat
is
-- 숫자 타입을 저장하고, 인덱스는 양의 정수인 index-by table 을 선언 : NumberArray
type NumberArray is table of number
index by PLS_INTEGER;
/*
* 함수 sum : 파라미터에 전달받은 배열에 있는 모든 원소들의 합을 리턴하는 함수
* 파라미터 arr NumberArray
*/
function f_sum(arr NumberArray) return number;
/*
* 함수 avg : 파라미터에 전달받은 배열에 있는 모든 원소들의 평균을 리턴하는 함수
* 파라미터 arr NumberArray
* 파라미터2 digit number 리턴값(평균)의 소숫점 이하 자릿수, 기본값은 3
*/
function f_avg(
arr NumberArray,
digit number := 3
) return number;
/*
* 함수 var : 파라미터에 전달받은 배열에 있는 모든 원소들의 분산을 리턴하는 함수
* 파라미터 arr NumberArray
* 파라미터2 digit number 리턴값(분산)의 소숫점 이하 자릿수, 기본값은 3
*/
function f_var(
arr NumberArray,
digit number := 3
) return number;
/*
* 함수 std : 파라미터에 전달받은 배열에 있는 모든 원소들의 표준편차를 리턴하는 함수
* 파라미터 arr NumberArray
* 파라미터2 digit number 리턴값(표준편차)의 소숫점 이하 자릿수, 기본값은 3
*/
function f_std(
arr NumberArray,
digit number := 3
) return number;
/*
* 함수 max : 파라미터에 전달받은 배열에 있는 모든 원소들의 최댓값을 리턴하는 함수
* 파라미터 arr NumberArray
*/
function f_max(
arr NumberArray
) return number;
/*
* 함수 min : 파라미터에 전달받은 배열에 있는 모든 원소들의 최솟값을 리턴하는 함수
* 파라미터 arr NumberArray
*/
function f_min(
arr NumberArray
) return number;
end my_stat;
/
-- 패키지 본문 (body) 작성
create or replace package body my_stat
is
-- func f_sum
function f_sum(arr NumberArray) return number --input하는 연관배열이 NumberArray타입이어야 함
is
v_sum number := 0;
begin
for i in 1.. arr.count loop
v_sum := v_sum + arr(i);
end loop;
return v_sum;
end f_sum;
--func f_avg (14)
function f_avg(arr NumberArray, digit number) return number
is
v_avg number := 0;
begin
v_avg := trunc( my_stat.f_sum(arr) / arr.count, digit );
return v_avg;
end f_avg;
--func f_var(26)
function f_var(arr NumberArray, digit number) return number
is
v_sum number := 0;
v_var number := 0;
begin
for i in 1.. arr.count loop
v_sum := v_sum + power((arr(i)-my_stat.f_avg(arr)),2);
end loop;
v_var := trunc(v_sum/(arr.count-1),digit);
return v_var;
end f_var;
--func std (46)
function f_std(arr NumberArray, digit number) return number
is
v_std number := 0;
begin
v_std := trunc( sqrt ( my_stat.f_var(arr) ),digit ) ;
return v_std;
end f_std;
--func f_max
function f_max(arr NumberArray) return number
is
v_num number;
begin
v_num := arr(arr.first);
for i in arr.first.. arr.last loop
if v_num<arr(i) then
v_num := arr(i);
end if;
end loop;
return v_num;
end f_max;
--func min
function f_min(arr NumberArray) return number
is
v_num number;
begin
v_num := arr(arr.first);
for i in arr.first.. arr.last loop
if v_num>arr(i) then
v_num := arr(i);
end if;
end loop;
return v_num;
end f_min;
end my_stat;
/
-- 함수 테스트 my_stat.f_***
declare
v_scores my_stat.NumberArray;
begin
for i in 1..10 loop
v_scores(i) := trunc( dbms_random.value (0,11) );
dbms_output.put( v_scores(i) || ' ' );
end loop;
dbms_output.new_line;
dbms_output.put_line('sum : '||my_stat.f_sum(v_scores));
dbms_output.put_line('avg : '||my_stat.f_avg(v_scores,5));
dbms_output.put_line('var : '|| my_stat.f_var(v_scores,5));
dbms_output.put_line('std : '||my_stat.f_std(v_scores,5));
dbms_output.put_line('max : '||my_stat.f_max(v_scores));
dbms_output.put_line('min : '|| my_stat.f_min(v_scores));
end;
/
'DB > Oracle' 카테고리의 다른 글
| Oracle DB14_예외처리, 함수, 프로시저 (0) | 2019.10.09 |
|---|---|
| Oracle DB13_VARRAY, 커서 (0) | 2019.10.07 |
| Oracle DB12_레코드,연관배열 (0) | 2019.10.03 |
| Oracle DB11_ 반복문 연습문제2 (0) | 2019.10.01 |
| Oracle DB10_조건문/반복문 연습문제 (0) | 2019.09.30 |
