오라클PLSQL, 저장 함수(Stored Function)
15.2. 저장 함수(Stored Function)
자신을 호출한 곳으로 반드시 하나의 값을 리턴해 줘야되는 PL/SQL Stored Program 입니다. 함수 자체가 오라클 서버에 저장 되므로 Stored Function or User Function or User-Defined Function 이라고 합니다.
자신의 스키마 계정에 함수를 작성하려면 CREATE PROCEDURE 시스템 권한이 있어야 하며 다른 사용자 계정에서 함수를 만들려면 CREATE ANY PROCEDURE 시스템 권한이 있어야 합니다.
함수를 정의할 때 Header에 리턴되는 데이터 타입을 기술하고 Header의 끝에는 IS[AS]가
와야하며 IS[AS]와 BEGIN사이에 BEGIN ~ END에서 사용할 변수를 선언 합니다.
<기본형식>
CREATE [OR REPLACE] FUNCTION function_name [parameters] RETURN return_type IS[or AS] declaration_section BEGIN execution_section RETURN return_variable; EXCEPTION exception_section RETURN exception_return_variable; END [function_name]; /
|
저장 함수를 만들고 호출하는 실습을 해보겠습니다.
15.2.1. 저장 함수 작성 실습 : CREATE OR REPLACE FUNCTION
EMP 테이블에서 사번을 인자로 입력받아 해당 사원의 이름을 리턴하는 저장 함수를 F_GET_ENAME 이라는 이름으로 작성하세요. |
-- 이미 F_GET_ENAME 함수가 있다면 대체 합니다. 리턴 타입은 VARCHAR2 입니다. -- 함수를 호출할 때는 숫자형으로 사번을 던져줘야 합니다. CREATE OR REPLACE FUNCTION F_GET_ENAME(P_EMPNO IN NUMBER) RETURN VARCHAR2 IS /* IS ~ BEGIN사이는 이름없는 블럭을 만들때의 DECLARE ~ BEGIN과 동일하게 변수를 선언하는 선언부 입니다. V_EANE 변수는 EMP 테이블의 ENAME과 타입이 같습니다. */ V_ENAME EMP.ENAME%TYPE; BEGIN /* BEGIN ~ END 사이의 SELECT절은 반드시 한건만 SELECT 되야 하며 INTO절이 반드시 필요합니다. WHERE절의 사원이 없다면 EXCEPTION절의 WHEN NO_DATA_FOUND THEN 절로 이동합니다. */ SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = P_EMPNO;
-- V_ENAME 변수값을 리턴 합니다. RETURN V_ENAME; -- 예외처리 절 입니다. EXCEPTION /* NO_DATA_FOUND는 PL/SQL에 사전에 정의되어 있는 예외 입니다. ORA-01403 오류 코드이며 SELECT시 데이터를 찾을 수 없을때 발생하는 예외 입니다. WHEN 절에서 일일이 예외를 처리하기가 번거러우면 모든 예외를 다 처리할 수 있는 WHEN OTHERS THEN만 사용해도 무방 합니다. */ WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('해당 사원은 없습니다.'); WHEN OTHERS THEN -- 기타 오류인 경우 SQL 에러코드와 에러 메시지를 출력 합니다. DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM); END F_GET_ENAME; |
<실행결과>
Function F_GET_ENME이(가) 컴파일되었습니다.
SQL Developer에서 실행할 경우 END F_GET_ENME; 다음에 슬래시(‘/’)를 하지 않아도 실행 되지만 SQL*Plus에서 실행을 하는 경우에는 반드시 ‘/’를 뒷부분에 표시 해야만 실행이 됩니다. 아래 그림을 참조하세요.
작성한 함수를 SELECT절에서 호출하는 실습 입니다.
F_GET_ENME 함수를 SELECT 쿼리에서 호출 하세요. |
SELECT ❶F_GET_ENAME(7369) FROM DUAL; |
<실행결과>
| F_GET_ENAME(7369) |
1 | SMITH |
❶ 오라클에서 제공하는 수많은 함수를 SELECT 쿼리에서 사용하듯이 우리가 작성한 함수도 같은 형태로 사용 가능 합니다.
SELECT F_GET_ENAME(7777) FROM DUAL; |
<실행결과>
ORA-06503: PL/SQL: 함수가 값을 리턴하지 못했습니다
해당 사원은 없습니다.
작성한 함수를 EXECUTE 명령으로 호출하는 실습 입니다. 아래 실습의 실행은 SQL*Deveoper 또는 SQL*PLUS 모두 가능 합니다.
F_GET_ENME 함수를 EXECUTE 명령으로 호출 하세요. |
-- 변수를 선언 합니다. 이렇게 선언한 변수를 참조할 때는 앞에 ‘:’을 표시해야 합니다. VARIABLE ENAME VARCHAR2(50); -- F_GET_ENAME 함수를 호출하면서 7369 사번을 인자로 전달하고 -- EXEC[UTE] 명령으로 함수를 호출하고 그 결과를 ENAME 변수로 받습니다. -- ENAME 변수는 반드시 앞에 콜론(‘:’)을 기술해야 합니다. EXEC :ENAME := F_GET_ENAME(7369); -- ENAME 변수를 출력합니다. PRINT ENAME; |
<실행결과>
ENAME
--------------------------------------------------------------------------------
SMITH
