서브프로그램의 다양한 기능 - 재귀 호출, 서브프로그램 정의의 중첩, 중복 정의

재귀호출(Recursive Call)은 서브프로그램이 자기 자신을 호출하는 것을 말한다.

서브프로그램이 재귀호출을 구현하기 위해서는 최소한 두 개의 실행 경로를 가져야 하는 데 하나는 자신을 재귀 호출하는 경로이고, 다른 하나는 재귀 호출 없이 종료하는 경로이다. 재귀 호출하는 경로만을 가진다면 서브프로그램은 무한 재귀호출되다가 스택 오버플로우 오류를 발생시킬 것이다.

-- 재귀 호출을 사용한 팩토리얼 계산 함수
CREATE OR REPLACE FUNCTION factorial(a_num PLS_INTEGER)
RETURN NUMBER
IS
BEGIN
	IF a_num <= 1 THEN							-- 종료 조건
    	RETURN 1;								-- 종료
    ELSE
    	RETURN a_num * factorial(a_num - 1);	-- 재귀 호출
    END IF;
END;
/

REM SELECT문에서 factorial 함수 호출
SELECT LEVEL 숫자
	, factorial(LEVEL)
  FROM DUAL
 CONNECT BY LEVEL <= 10;
 

서브프로그램 정의의 중첩

서브프로그램의 정의는 중첩될 수 있다. 즉 함수나 프로시저 내부에 다시 함수나 프로시저를 정의하여 사용할 수 있다.

CREATE OR REPLACE PROCEDURE raise_bonus2(a_empno NUMBER, a_amt NUMBER)
-- 테이블 bonus에 사원의 커미션 값을 인상하는 프로시저
IS
	v_emp_name emp.ename%TYPE;
    
   -- 사원의 이름을 얻는 함수 raise_bonus2 내부에 중첩되어 정의됨
   FUNCTION get_ename(a_empno NUMBER) RETURN VARCHAR2
   IS
   	v_ename emp.ename%TYPE;
   BEGIN
   	SELECT ename
      INTO v_ename
      FROM emp
     WHERE empno = a_empno;
    RETURN v_ename;
  EXCEPTION
  	WHEN NO_DATA_FOUND THEN
    	RETURN NULL;
  END;
  
  -- 사원의 보너스를 인상하는 프로시저. raise_bonus2 내부에 중첩되어 정의됨
  PROCEDURE raise_bonus(a_ename VARCHAR2, a_amt NUMBER)
  IS
  BEGIN
  	MERGE INTO bonus
    USING DUAL
       ON (bonus.ename = a_ename)
     WHEN MATCHED THEN		-- 기존 보너스가 있는 경우 인상할 값을 더한다.
     	UPDATE SET comm = comm + a_amt
     WHEN NOT MATCHED THEN	-- 기존 보너스가 없는 경우 새 로우를 추가
     	 INSERT (ename, comm)
         VALUES (a_ename, a_amt);
  END;
BEGIN
	v_emp_name := get_ename(a_empno);
    IF a_amt IS NOT NULL
    THEN
    	raise_bonus(v_emp_name, a_amt);
    END IF;
END;

서브프로그램 raise_bonus2 내부에 get_name과 raise_bonus 두 개의 서브프로그램이 중첩되어 있다.

중복 정의

C++이나 자바와 같은 객체 지향 프로그래밍 언어를 사용해 본 적이 있는 사람은 중복 정의(Overloading)에 대해 알고 있을 것이다. 중복 정의는 동일한 서브프로그램명을 매개변수의 개수, 순서, 데이터 타입을 달리하여 정의하는 것으로 PL/SQL 서브프로그램의 강력한 기능 중 하나이다.

중복 정의의 사용

중복 정의는 오라클 내장 함수와 패키지에서 광범위하게 사용되고 있다. 예로 자주 사용되는 내장 함수 SUBSTR은 매개변수 개수가 다른 다음 두 가지 형식으로 중복 정의되어있다.

SUBSTR(문자열, 시작위치)
SUBSTR(문자열, 시작위치, 서브스트링의 길이)

TO_CHAR 함수도 서로 다른 데이터 타입에 대해 중복 정의되어 있다.

TO_CHAR(CHAR타입값) RETURN VARCHAR2
TO_CHAR(NUMBER타입값) RETURN VARCHAR2
TO_CHAR(DATE타입값) RETURN VARCHAR2

중복 정의를 사용하면 개념적으로는 동일한 행위를 하지만 매개변수의 특성만 조금씩 달라지는 서브프로그램을 PL/SQL을 사용하여 효율적으로 작성할 수 있다. 다음 예제의 패키지 pkg_emp는 사원의 부서명을 출력하는 두 개의 함수 emp_dept_name에 중복 정의를 사용한다. 패키지 명세에는 함수 emp_dept_name이 매개변수의 타입을 달리하여 선언되었다. NUMBER를 매개변수로 하는 함수는 매개변수로 주어지는 사번의 소속부서를 반환하고, VARCHAR2를 매개변수로 주어진 사원명의 소속 부서를 반환한다. 

-- 서브프로그램 중복 정의: 패키지 명세
CREATE OR REPLACE PACKAGE pkg_emp
IS
	FUNCTION emp_dept_name(a_empno NUMBER) RETURN VARCHAR2;	-- 사번을 매개변수로 하는 버전
    FUNCTION emp_dept_name(a_ename VARCHAR2) RETURN VARCHAR2; -- 이름을 매개변수로 하는 버전
END;

-- 서브프로그램 중복 정의: 패키지 본체
CREATE OR REPLACE PACKAGE BODY pkg_emp
IS
	-- 사번을 매개변수로 하는 버전
    FUNCTION emp_dept_name(a_empno NUMBER) RETURN VARCHAR2
    IS
    	v_dname dept.dname%TYPE;
    BEGIN
    	SELECT dname
          INTO v_dname
          FROM emp e, dept d
         WHERE e.deptno = d.deptno
           AND e.empno = a_empno;
        RETURN v_dname;
    END;
    
    -- 이름을 매개변수로 하는 버전
    FUNCTION emp_dept_name(a_ename VARCHAR2) RETURN VARCHAR2
    IS
    	v_dname dept.dname%TYPE;
    BEGIN
    	SELECT dname
          INTO v_dname
          FROM emp e, dept d
         WHERE e.deptno = d.deptno
           AND e.ename = a_ename;
        RETURN v_dname;
    END;
END;

중복 정의된 예제의 함수는 다음과 같이 호출 시 사용되는 매개변수의 데이터 타입에 따라서 서로 다른 버전이 호출된다.

SCOTT> COL 사번매개변수버전 FORMAT A20
SCOTT> COL 이름매개변수버전 FORMAT A20
SCOTT> SELECT pkg_emp.emp_dept_name(7788)	AS 사번매개변수버전
			, pkg_emp.emp_dept_name('SCOTT') AS 이름매개변수버전
         FROM DUAL;
         

중복 정의는 서브프로그램의 두 가지 유형인 함수와 프로시저 모두에 사용가능하다. 여러 가지 유형의 중복 정의가 가능

CREATE OR REPLACE PACKAGE pkg_overloading
IS
	-- 1: 매개변수 데이터 타입이 다른 중복 정의
    PROCEDURE p1(n NUMBER);
    PROCEDURE p1(v VARCHAR2);
    
    -- 2: 매개변수 개수가 다른 중복 정의
    PROCEDURE p2(n NUMBER);
    PROCEDURE p2(n NUMBER, v VARCHAR2);
    
    -- 3: 매개변수 순서가 다른 중복 정의
    PROCEDURE p3(v VARCHAR2, n NUMBER);
    PROCEDURE p3(n NUMBER, v VARCHAR2);
    
    -- 4: 매개변수 이름이 다른 중복 정의
    PROCEDURE p4(v1 VARCHAR2, n1 NUMBER);
    PROCEDURE p4(v2 VARCHAR2, n2 NUMBER);
END;

CREATE OR REPLACE PACKAGE BODY pkg_overloading
IS
	-- 1. 매개변수 데이터 타입이 다른 중복 정의
    PROCEDURE p1(n NUMBER)	IS BEGIN NULL; END;
    PROCEDURE p1(v VARCHAR2) IS BEGIN NULL; END;
    
    -- 2. 매개변수 개수가 다른 중복 정의
    PROCEDURE p2(n NUMBER)		IS BEGIN NULL; END;
    PROCEDURE p2(n NUMBER, v VARCHAR2) IS BEGIN NULL; END;
    
    -- 3. 매개변수 순서가 다른 중복 정의
    PROCEDURE p3(v VARCHAR2, n NUMBER) IS BEGIN NULL; END;
    PROCEDURE p3(n NUMBER, v VARCHAR2) IS BEGIN NULL; END;
    
    -- 4. 매개변수 이름만 다르고 다른 것은 동일한 중복 정의
    PROCEDURE p4(v1 VARCHAR2, n1 NUMBER) IS BEGIN NULL; END;
    PROCEDURE p4(v2 VARCHAR2, n2 NUMBER) IS BEGIN NULL; END;
END;

간단히 예제만을 보여주기 위해 각 실행부는 NULL문 하나만을 사용했다.

 

다른 경우는 사용에 별 문제가 없지만 네 번째 프로시저 p4와 같이 매개변수 이름만 다른 중복 정의의 경우는 사용에 특별한 주의가 필요하다, p4의 매개변수가 개수도 동일하고 데이터 타입도 동일하기 때문에 컴파일러가 어느 서브프로그램을 호출해야 하는지 판단하지 못할 수도 있다. 이 오류를 방지하려면 이름에 의한 매개변수 지정 방법을 사용해야 한다. 

BEGIN
	-- 매개변수의 이름만 다른 오버로딩 호출 시 이름에 의한 매개변수 지정을 사용하여 오류 방지
    pkg_overloading.p4(v1 => 'A', n1 => 1);	-- 첫 번째 p4 프로시저
    pkg_overloading.p4(v2 => 'A', n2 => 1); -- 두 번째 p4 프로시저
END;

중복 정의의 제약

• 저장 함수나 저장 프로시저와 같은 독립형 저장 서브프로그램에서는 중복 정의를 사용할 수 없고, 중첩된 서브프로그램이나 패키지나 타입에서만 사용할 수 있다. 독립형 저장 서브프로그램에 중복 정의를 시도하면 실제로는 중복 정의되는 것이 아니라 새로운 정의로 대치되어 버리며 이전의 서브프로그램 정의는 손실된다.
• 입출력 모드만 다른 서브프로그램 중복 정의는 불가능하다. 입출력 모드만 다르게 중복 정의한 경우 호출 시 오류가 발생하며 실행이 불가능하다. 이 유형에 대해서는 어떤 방법을 사용하더라도 컴파일러가 두 버전을 구별하게 만들 수 없다.

-- 입출력 모드만 다른 서브프로그램 중복 정의는 불가능
SCOTT> DECLARE
		PROCEDURE p(a IN  VARCHAR2) IS BEGIN NULL; END;
        PROCEDURE p(a OUT VARCHAR2) IS BEGIN NULL; END;
    BEGIN
    	p('A');
    END;
    /
    
0RA-06550:  줄 6, 열 3:PLS-00307: 해당 호출에 대한 'P' 정의가 너무 많습니다.

• 서브타입만 다르고 기본 데이터 타입이 동일한 매개변수는 중복 정의가 불가능하다.  다음 예제에서 INTEGER와 REAL은 모두 NUMBER의 서브타입이므로 중복 정의가 불가능

SCOTT>	DECLARE
		PROCEDURE p(a INTEGER) IS BEGIN NULL; END;
        PROCEDURE p(a REAL)    IS BEGIN NULL; END;
    BEGIN
    	p(10);
    END;
    /
    

• 반환형만 다른 중복정의는 불가능하다. 따라서 다음 예제와 같이 매개변수가 동일하고 반환형만 다른 함수는 중복정의할 수 없다.

SCOTT> DECLARE
	   FUNCTION f(a VARCHAR2) RETURN NUMBER		IS BEGIN RETURN 0; END;
       FUNCTION f(a VARCHAR2) RETURN VARCHAR2	IS BEGIN RETURN 'A'; END;
    BEGIN
    	DBMS_OUTPUT.PUT_LINE(f('abc'));
    END;
    /