PL/SQL의 stored function
PL/SQL 언어 중에서 Stored function은 Stored procedure와 개념이 동일하며, 동일한 기능을 가지고 있다. 한가지 차이점은 stored procedure는 로직을 처리만 하고 끝내지만, stored function은 그 처리 결과를 사용자에게 반환하는 기능을 가지고 있는 점이 다르다.
【형식】
| 생성 |
삭제 |
create [or replace] function [함수명]
(argument1 [mode] date_type1,
argument2 [mode] date_type2,
.........
return data_type;
IS [AS]
BEGIN
..........
return(변수);
EXCEPTION
.........
END;
|
DROP function [함수명];
|
|
여기서 사용되는 인수의 의미는 다음과 같다.
| • [함수명] |
데이터베이스 내에 저장될 프로시져 이름 |
| • argument |
운영체제에서 프로시져 내로 어떤 값을 전달할 때의 변수 |
| • mode |
IN, OUT, IN OUT |
| • data_type |
argument 변수의 데이터 타입 |
| • begin...end |
실행하려는 처리 절차 |
|
stored function의 개발절차
1) 운영체제의 편집기로 stored function을 작성하며, 확장자는 *.sql임
2) SQL*Plus에서 작성된 *.sql 스크립트를 실행하여 stored function을 생성함
3) 컴파일러에 의해 pcode가 생성되고 데이터베이스 내에 저장됨
4) SQL*Plus에서 생성된 함수를 호출하여 실행할 때는 EXECUTE 명령을 사용함
응용 프로그램에서 실행할 때는 함수명을 정의만 하면 됨
【예시】
| 편집기로 작성한 스크립트 |
SQL>에서 실행 |
| $ vi chk_sal.sql
create function chk_sal
(v_sal number;)
return number;
IS
BEGIN
v_chk := v_sal * 0.01;
return(v_chk);
end;
|
$ sqlplus scott/tiger
SQL> start chk_sal.sql |
|
SQL> variable v_sal number
SQL> EXECUTE :v_sal := chk_sal(7934); |
|
【예제】
SQL> set serveroutput on
SQL> create function get_salary return number
2 is
3 ret number;
4 begin
5 select sum(sal) into ret from emp;
6 return(ret);
7 end get_salary;
8 /
SQL> select * from user_source; ☜ function의 등록을 확인
SQL> select get_salary from dual;
stored function의 실행방법
1) SQL*Plus에서 실행하는 방법
EXECUTE 명령에 의해 실행할 수 있지만, 자신의 스키마에 생성된 PL/SQL만 실행할 수 있다.
또한 alter function 권한이 반드시 필요하다.
만약 다른 사용자가 생성한 PL/SQL을 실행하려면 alter any function 권한이 있어야 한다.
2) anonymous 프로시져에서 실행하는 방법
begin...end절에 호출하려는 stored function명을 정의하면 된다.
3) 하나의 stored 프로시져에서 호출에 의해 실행하는 방법
anonymous 프로시져처럼 begin...end절에 호출하려는 stored function명을 정의하면 된다.
stored procedure와 stored function의 차이
| 프로시져 |
함수 |
EXECUTE 명령에 의해 호출되고, 실행되며,
때로는 다른 프로시저나 함수 내에서 호출되어 실행 |
EXECUTE 명령과 다른 프로시져, 함수에 의해 호출되거나, 사용자의 SQL 문에 의해 호출할 수 있음 |
| 반환되는 데이터형이 없음 |
RETURN 명령에 의해 반환되는 한개의 데이터형이 존재 |
| OUT 매개변수를 통해 함수처럼 사용하여 여러 개의 처리 결과를 반환 |
RETURN 명령에 의해 하나의 값만 반환 |
|
【예제】
stored function 예제
stored function 예제
return을 사용하여 값을 반환한다. return 문이 없는 함수는 사용할 수 없다.
create와 drop으로 function을 만들고 삭제하며,
function을 실행하기 위해서 execute를 사용하지 않는다.【예제】
SQL> set serveroutput on
SQL> create function get_salary return number
2 is
3 ret number;
4 begin
5 select sum(sal) into ret from emp;
6 return(ret);
7 end get_salary;
8 /
SQL> select * from user_source; ☜ function의 등록을 확인
SQL> select get_salary from dual;
【예제】
$ vi tax.sql
create or replace function tax
(v_value In number)
return number
IS
BEGIN
RETURN (v_value*0.07);
END tax;
/ |
|
$ sqlplus scott/tiger
SQL> start tax.sql
함수가 생성되었습니다.
SQL> variable x number;
SQL> execute :x :=tax(1000);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> print x
X
----------
70
SQL>
SQL> select sal, tax(sal) ☜ DML 문장에서도 실행됨
2 from emp
3 where empno=7934;
SAL TAX(SAL)
---------- ----------
1300 91
SQL> update emp set sal=tax(sal) where empno=7934;
1 행이 갱신되었습니다.
SQL> select * from user_source; ☜ function의 등록을 확인
SQL> drop function tax;