Stored Function(저장 함수)

    PL/SQL 저장함수는 RETURN문을 사용하여 하나의 값을 반환하는 서브프로그램으로 고유한 이름을 가지고 데이터베이스에 저장되며 이름을 식별자로 사용하여 반복적으로 재사용될 수 있다. 저장함수는 입력 값으로 매개변수를 가질 수 있으며 일반적으로 서로 다른 입력 값에 따라서 서로 다른 결과값을 반환한다.(But 입력값이 다르다고 결과가 항상 달라야 하는 것은 아니며 입력값이 돌일하다고 항상 결과가 동일해야 하는 것도 아니다.)

    함수의 기본구조

    CREATE [ OR REPLACE ] FUNCTION 함수명 [ ( 매개변수목록 ) ]
RETURN 반환데이터타입
IS	-- IS 대신 AS를 사용해도 동일
	선언부
BEGIN
	실행부
    RETURN 반환값
EXCEPTION
	예외처리부
END;

    CREATE는 실제로는 저장 함수 자체에 포함되는 것은 아니며 저장함수를 생성하는 SQL 명령어다.

    실제로 함수를 정의하는 부분은 CREATE를 제외한 나머지 부분이다.

     

    함수명은 생성할 함수의 식별자이므로 다른 DB 오브젝트와 이름이 중복되지 않아야 한다. 다른 함수명과 중복되지 않아야 하는 것은 물론이고, 테이블이나 뷰나 프로시저와 같은 어떤 데이터베이스 오브젝트의 이름과도 달라야 한다. 매개변수목록은 옵션이므로 존재할 수도 있고 없을 수도 있다. 2번 줄의 RETURN 뒤의 반환데이터타입은 함수의 실행결과로 반환되는 값의 데이터 타입으로 PL/SQL에서 사용 가능한 어떤 데이터 타입이라도 가능하다.

    함수는 반드시 한 개 이상의 RETURN 반환값 문을 가져야 한다. 처리 경로가 여러 개인 경우에는 RETURN문이 여러 개인 경우도 흔하다. 예외처리부는 옵션이며 예외를 처리하지 않는다면 생략할 수 있다.

     

    CREATE OR REPLACE FUNCTION get_wage(a_empno NUMBER)
	RETURN NUMBER
-- 사원의 급여와 커미션의 합을 반환하는 함수
IS	-- 선언부 시작. IS 대신 AS를 사용할 수 있음
	v_wage NUMBER;
BEGIN  -- 실행부 시작
	-- 사번이 a_empno인 사원의 급여와 커미션의 합을 조회한다.
    SELECT sal + NVL(comm, 0) comm
      INTO v_wage
      FROM emp
     WHERE empno = a_empno;
     -- 급여를 반환
     RETURN v_wage;
  EXCEPTION		-- 예외 처리부 시작
  	WHEN NO_DATA_FOUND THEN
    	-- 사원이 존재하지 않을 경우는 -1을 반환
       RETURN -1;
 END;

    함수의 매개변수

    대부분의 함수는 매개변수를 가진다. 매개변수는 프로그램의 작성 시에 특정 값으로 고정되지 않은 값을 나중에 실행 시에 전달받기 위해서 사용한다.

    매개변수의 선언은 기본적으로 다음과 같은 형식을 가진다.

    ( 매개변수명 데이터타입 [, 매개변수명 데이터타입 ... ] )
    CREATE OR REPLACE FUNCTION f(a_empno NUMBER, a_ename VARCHAR2, a_hiredate DATE, a_deptno NUMBER)
RETURN VARCHAR2
AS
BEGIN
	NULL;
END;

    매개변수의 개수에는 특별한 제한이 없다. 매개변수가 없을 수도 있고, 여러 개일 수도 있다.

    매개변수가 없다면 매개변수 목록을 감싸는 괄호까지 포함해서 생략해야 한다.

    -- 매개변수가 없는 함수
SCOTT> CREATE OR REPLACE FUNCTION f RETURN VARCHAR2
	 IS
     BEGIN
       NULL;
     END;
   /

    빈 괄호를 사용하면 컴파일 오류가 발생한다.

    -- 매개변수가 없는 함수 정의에 빈 매개변수 괄호 사용 시 오류 발생
SCOTT> CREATE OR REPLACE FUNCTION f() RETURN VARCHAR2
	  IS
      BEGIN
        NULL;
      END;
     /

    함수의 선언부

    함수의 선언부에는 타입, 상수, 변수, 커서, 예외, 서브프로그램 전방 선언, 서브프로그램 정의가 올 수 있다.

    물론 필요하지 않은 것들은 생략할 수 있다. 각 종류별 배치 순서에서는 서브프로그램 정의가 가장 나중에 와야 하고, 나머지는 순서에 대한 제약이 없다. 서브프로그램 정의가 나온 후에 서브프로그램이 아닌 다른 유형의 선언이나 정의가 나오면 오류가 발생한다.

    CREATE OR REPLACE FUNCTION f RETURN NUMBER
IS
   /* 함수 선언부 시작 */
   FUNCTION get_emp_sal(a_empno NUMBER) RETURN NUMBER; -- 함수 전방 선언
   
   no_emp_found EXCEPTION;				-- 사용자 지정 예외 선언
   
   TYPE number_arr_type IS TABLE OF NUMBER; -- 타입 선언
   v_empno_arr number_arr_type;			   -- 변수 선언
   
   c_nulm_comm CONSTANT NUMBER := 9;		-- 상수 선언
   
   v_wage	NUMBER;						-- 변수 선언
   
   CURSOR emp_cursor(a_empno NUMBER) IS		-- 커서 선언
   	 SELECT sal + NVL(comm, 0) comm
       FROM emp
      WHERE empno = a_empno;
      
   FUNCTION get_emp_sal(a_empno NUMBER) RETURN NUMBER IS -- 함수 정의
      v_sal NUMBER;
   BEGIN
     OPEN emp_cursor(7788);
     FETCH emp_cursor INTO v_sal;
     CLOSE emp_cursor;
     RETURN v_sal;
   END;
   /* 함수 선언부 끝 */
BEGIN
  NULL;
END;

    함수의 반환값

    함수는 반드시 하나의 값을 반환해야 한다. 값의 반환에는 RETURN문을 사용하는데 RETURN문을 만나면 값을 반환하면서 함수를 즉시 종료하고 호출한 프로그램으로 돌아간다. 반환하는 값은 단일 값이다.

    단 하나의 값만을 반환해야 하는 제한은 있지만 스칼라 값뿐만 아니라 컴포지트 타입도 반환 가능하다.

    하나의 변수 또는 상수로 나타낼 수 있다면 컬렉션이나 레코드나 ADT를 반환할 수도 있다. 저장 함수가 컬렉션이나 레코드를 반환하려면 스키마 레벨에서 객체 타입을 선언해야 한다. 단, %ROWTYPE 앵커는 타입 선언 없이 바로 사용할 수 있다.

    -- 함수에서 컬렉션 반환을 위한 객체 타입 선언
CREATE OR REPLACE TYPE empno_arr_type IS TABLE OF NUMBER;
    -- 컬렉션을 반환하는 함수 정의
CREATE OR REPLACE FUNCTION get_emp_list(a_deptno NUMBER)
	RETURN empno_arr_type
IS
	v_empno_arr empno_arr_type;
BEGIN
   SELECT empno
     BULK COLLECT INTO v_empno_arr
     FROM emp
    WHERE deptno = a_deptno
    ORDER BY empno;
   RETURN v_empno_arr;
END;
    -- 레코드를 반환하는 함수 정의
CREATE OR REPLACE FUNCTION get_emp_rec(a_empno NUMBER)
   RETURN emp%ROWTYPE
IS
	v_emp_rec emp%ROWTYPE;
BEGIN
  SELECT * 
    INTO v_emp_rec
    FROM emp
   WHERE empno = a_empno;
  RETURN v_emp_rec;
END;

    다음은 컬렉션 타입을 반환하는 함수를 사용할 때 나타나는 특이한 형태 하나를 보여주는 프로그램이다.

    첫 번째 괄호인 (10)은 함수 get_emp_list를 호출할 때의 매개변수다. 두 번째 괄호인 (1)은 Nested Table의 인덱스이다.

    -- 컬렉션 반환 함수에 연속적으로 두 개의 괄호 사용
BEGIN
 DBMS_OUTPUT_PUT_LINE(get_emp_list(10)(1));
END;

    만약 함수가 매개변수를 가지지 않는 경우라면 더 특이한 형태가 된다. 함수의 매개변수를 위한 첫 번째 괄호는 비어 있다.

    DECLARE
	-- 매개변수를 가지지 않는 함수
    FUNCTION get_emp_list_of_dept_10 RETURN empno_arr_type
    IS
      v_empno_arr empno_arr_type;
    BEGIN
       SELECT empno
         BULK COLLECT INTO v_empno_arr
         FROM emp
        WHERE deptno = 10;	-- 부서 번호 10의 사원 번호 목록
       RETURN v_empno_arr;
    END;
BEGIN
	DBMS_OUTPUT_PUT_LINE(get_emp_list_of_dept_10()(1));
END;

    함수는 RETURN문을 적어도 한 개는 가져야 하며 여러 개를 가질 수도 있다. RETURN문이 여러 개인 경우는 보통 분기문을 사용하여 조건에 따라 다른 값을 반환하는 경우이다. RETURN문이 여러 개라 하더라도 반환되는 값의 데이터 타입은 모두 함수의 반환형과 같거나 호환되는 타입이어야 한다.

    CREATE OR REPLACE FUNCTION max_number(a_1 NUMBER, a_2 NUMBER)
	RETURN NUMBER
IS
BEGIN
	IF a_1 <= a_2 THEN
       RETURN a_2;
    ELSE
       RETURN a_1;
    END IF;
END;

    저장함수 사용의 제약사항

    • SELECT문에서 호출되는 사용자 정의 함수는 매개변수로 IN 모드만 사용할 수 있다. OUT이나 IN OUT 모드의 매개변수를 사용하면 컴파일에는 문제 없지만 SELECT문에 포함하여 실행할 경우 오류가 발생한다. 저장 서브프로그램의 매개변수로 OUT 모드를 사용하는 것은 실행 시에 매개변수의 값이 변경되는 것을 허용한다는 의미인데, 이 제약사항은 SELECT문에서는 함수의 매개변수로 사용된 값이 변경되는 것을 허용하지 않는다는 의미이다.
    • 사용자 정의 함수가 SELECT문에서 호출된 경우에는 DML을 사용할 수 없다. 함수가 DML 내의 서브쿼리에서 호출된 경우라면 DML을 사용하는 것이 가능하다. 하지만 함수를 호출한 DML의 변경 대상 테이블에 대해 사용자 정의 함수가 DML을 수행하는 것은 허용되지 않는다.
    • 사용자 정의 함수 내에서는 트랜잭션 제어를 할 수 없다. 따라서 COMMIT이나 ROLLBACK, SAVEPOINT, LOCK TABLE과 같이 트랜잭션을 제어하는 쿼리는 사용할 수 없다.
    • 사용자 정의 함수 내에서는 DDL을 실행할 수 없다. DDL은 실행 시 내부적으로 COMMIT을 실행하므로 트랜잭션 제어 불가라는 제약사항에도 위배된다.
    • CREATE TABLE문이나 ALTER TABLE문의 CHECK나 DEFAULT절에는 사용자 정의 함수를 사용할 수 없다. 사용자 정의 함수는 변경이 가능한데 CHECK나 DEFAULT절에 사용자 정의 함수를 사용하면 함수를 컴파일할 때마다 CHECK나 DEFAULT의 평가를 재수행해야 하는 문제가 따라오므로 이를 허용하는 것은 합리적이지 않다.

    'DATABASE > SQL, PL-SQL' 카테고리의 다른 글

    Package(패키지) - 1  (0) 2021.03.24
    저장 프로시저  (0) 2021.03.16
    저장 서브프로그램 개요  (0) 2021.03.03
    예외처리 - 2  (0) 2021.02.25
    예외 처리 - 1  (0) 2021.02.23

    관련글

    댓글

    라이브러리 브랜드 그룹 | 코드는 기억보단 기록

    티스토리툴바