[ORACLE] FUNCTION

FUNCTION 생성

실행 환경에 반드시 하나의 값을 Return하기 위해 PL/SQL 함수를 사용한다. 함수 선언에서 Datatype이 있는 RETURN 절을 추가하고 PL/SQL 블록에 적어도 한 개의 이상의 RETURN 문을 포함한다. PARAMETER에서 사용하는 IN,OUT,IN OUT는 PROCEDURE에서 사용한 것과 동일하게 사용 가능하나 대부분 IN을 사용한다.

 

1.4.1 Syntax

CREATE  [OR  REPLACE]  FUNCTION  function_name         [(argument1  [mode1]  datatype [{:= | DEFAULT} expression]         [,argument2  [mode2]  datatype [{:= | DEFAULT} expression], . . .])] RETURN  data_type {IS | AS} BEGIN         pl/sql_block; END;

OR  REPLACE  function_name이 존재할 경우 FUNCTION의 내용을 지우고 다시 생성

function_name Function의 이름은 표준 Oracle 명명법에 따른 함수이름

argument     매개변수의 이름

mode         3가지가 있다

             IN : 입력 매개변수로 상용

             OUT : 출력 매개변수로 사용

             IN OUT : 입력, 출력 매개변수로 상용

data_type    반환되는 값의 datatype

pl/sql_block FUNCTION를 구성하는 코드를 구성하는 PL/SQL의 블록

 

1.4.2  RETURN 문

1)   PL/SQL 블록에는 RETURN문이 있어야 한다.

2)   함수는 RETURN 절에 지정된 것과 동일한 datatype으로 RETURN 값을 설정해야 한다.

3)   다중 RETURN 문은 사용할 수 있지만 한 번의 호출로는 한 개의 RETURN 문만 실행된다.

4)   일반적으로 다중 RETURN 문은 IF 문에서 사용한다.

 

1.4.3  FUNCTION 실행

PL/SQL을 지원하는 어떤 툴이나 언어에서도 함수를 실행할 수 있고 PL/SQL내부에서 식의 일부로서 함수를 실행할 수 있다. SQL*Plus에서 FUNCTION 호출은 Stored Function를 참조하는 PL/SQL 문을 실행하기 위해 EXECUTE 명령을 사용할 수 있다. EXECUTE는 명령 다음에 입력되는 Stored Function를 실행한다.

 

가) Syntax

output_variable := function_name[(argument1[,argument2, . . . . .])]

 

나) SQL*Plus에서 함수 실행

SQL> EXECUTE :g_deptno := ename_deptno('ALLEN')   PL/SQL procedure successfully completed.

 

문제4) EMP 테이블에서 이름으로 부서 번호를 검색하는 함수를 작성하여라.

CREATE OR REPLACE FUNCTION ename_deptno(         v_ename IN      emp.ename%TYPE) RETURN NUMBER IS         v_deptno       emp.deptno%TYPE; BEGIN         SELECT deptno                INTO v_deptno                FROM emp                WHERE ename = UPPER(v_ename);         DBMS_OUTPUT.PUT_LINE('부서번호 : ' || TO_CHAR(v_deptno));         RETURN v_deptno; EXCEPTION         WHEN NO_DATA_FOUND THEN                DBMS_OUTPUT.PUT_LINE('입력한 MANAGER는 없습니다.');         WHEN TOO_MANY_ROWS THEN                DBMS_OUTPUT.PUT_LINE('자료가 2건 이상입니다.');         WHEN OTHERS THEN                DBMS_OUTPUT.PUT_LINE('기타 에러입니다.'); END; /

SQL> SET SERVEROUTPUT ON SQL> VAR g_deptno NUMBER SQL> EXECUTE :g_deptno := ename_deptno('SCOTT') 부서번호 : 10   PL/SQL procedure successfully completed.   SQL> PRINT g_deptno    G_DEPTNO ---------        10

 

문제5) EMP 테이블에서 이름을 입력 받아 부서번호,부서명,급여를 검색하는 FUNCTION을 작성하여라. 단 부서번호를 RETURN에 사용하여라.

CREATE OR REPLACE FUNCTION emp_disp(         v_ename IN      emp.ename%TYPE,         v_dname OUT     dept.dname%TYPE,         v_sal   OUT     emp.sal%TYPE) RETURN NUMBER IS         v_deptno       emp.deptno%TYPE;         v_dname_temp   dept.dname%TYPE;         v_sal_temp     emp.sal%TYPE; BEGIN         SELECT sal,deptno                INTO v_sal_temp,v_deptno                FROM emp                WHERE ename = UPPER(v_ename);         SELECT dname                INTO v_dname_temp                FROM dept                WHERE deptno = v_deptno;         v_dname := v_dname_temp;         v_sal := v_sal_temp;         DBMS_OUTPUT.PUT_LINE('성    명 : ' || v_ename);         DBMS_OUTPUT.PUT_LINE('부서번호 : ' || TO_CHAR(v_deptno));         DBMS_OUTPUT.PUT_LINE('부 서 명 : ' || v_dname_temp);         DBMS_OUTPUT.PUT_LINE('급    여 : ' || TO_CHAR(v_sal_temp,'$999,999'));         RETURN v_deptno; EXCEPTION         WHEN NO_DATA_FOUND THEN                DBMS_OUTPUT.PUT_LINE('입력한 MANAGER는 없습니다.');         WHEN TOO_MANY_ROWS THEN                DBMS_OUTPUT.PUT_LINE('자료가 2건 이상입니다.');         WHEN OTHERS THEN                DBMS_OUTPUT.PUT_LINE('기타 에러입니다.'); END; /

SQL> SET SERVEROUTPUT ON SQL> VAR g_deptno NUMBER SQL> VAR g_dname VARCHAR2(20) SQL> VAR g_sal NUMBER SQL> EXECUTE :g_deptno := emp_disp('scott',:g_dname,:g_sal) 성    명 : scott 부서번호 : 10 부 서 명 : ACCOUNTING 급    여 :    $3,000 PL/SQL procedure successfully completed. SQL> PRINT g_deptno  G_DEPTNO ---------        10 SQL> PRINT g_dname G_DNAME -------------------------------- ACCOUNTING SQL> PRINT g_sal     G_SAL ---------      3000

 

1.5 함수와 프로시저 비교

프로시저	함수
PL/SQL 문으로서 실행	식의 일부로서 사용
RETURN Datatype이 없음	RETURN Datatype이 필수
값을 Return할 수 있음	값을 Return하는 것이 필수

 

♣ 참고

1)   프로시저는 parameter리스트를 가질 수 있지만 값 반환이 필수적 이지는 않다.

2)   함수는 다음과 같은 두 가지 점에서 프로시저와 다르다.

①   식(expression)의 일부로서 함수를 사용한다.

②   함수는 값을 return하는 것이 필수적이다.