서브프로그램의 다양한 기능들 - 매개변수

서브프로그램에 대한 설명이므로 저장 서브프로그램과 저장되지 않는 서브프로그램 모두에 해당하는 내용이다. 

매개 변수

함수나 프로시저와 같은 서브프로그램에는 매개변수를 사용할 수 있다. 매개변수가 없다면 서브프로그램이 있으나 마나 할 정도로 기능이 매우 제한적일 수밖에 없을 것이다.

 

매개변수는 서브프로그램에 전달되는 입력 값 또는 출력 값이다. 보통 서브프로그램은 매개변수를 전달받아 이 값에 따라 처리할 대상 데이터와 처리 방식을 결정하고 처리를 수행한다.

 

매개변수의 선언과 사용

서브프로그램 메개변수는 서브프로그램명 뒤에 ( 와 ) 로 둘러싸여 선언되며, 서브프로그램 실행 시에 서브프로그램의 내부로 전달되어 사용된다. 서브프로그램에 매개변수를 사용하려면 먼저 서브프로그램 명세에 매개변수를 선언해야 한다. 명세에 선언된 매개변수는 서브프로그램 내부에서 사용될 수 있다.

CREATE OR REPLACE PROCEDURE raise_salary(a_empno NUMBER, a_amt NUMBER)
-- 사원의 급여를 인상하는 프로시저
IS
BEGIN
	-- 급여를 인상
    IF a_amt IS NOT NULL
    THEN
    	UPDATE emp
           SET sal = sal + a_amt
         WHERE empno = a_empno;
     END IF;
END;

NUMBER 타입의 매개변수 두 개가 선언되었고, BEGIN과 END 사이의 프로시저 정의부에서는 매개변수를 사용하여 데이터를 처리하고 있다.

 

IN/OUT 매개변수

서브프로그램에 전달되는 매개변수는 기본적으로 읽기 전용 모드이며 변경이 불가능하다.

하지만 입출력 모드를 지정하면 변경된 매개변수 값을 되돌려 받는 것도 가능하다. 

CREATE OR REPLACE PROCEDURE get_wage_proc(a_empno NUMBER, a_wage OUT NUMBER)
-- 사원의 급여와 커미션의 합을 매개변수로 반환하는 프로시저
IS
	v_wage NUMBER;
BEGIN
	-- 사번이 a_empno인 사원의 급여와 커미션의 합을 조회한다.
    SELECT sal + NVL(comm, 0) comm
      INTO v_wage
      FROM emp
     WHERE empno = a_empno;
   	-- 매개변수 a_wage를 통해 계산된 급여를 반환
    a_wage := v_wage;
END;
  

매개변수 a_wage에 OUT 모드를 사용하여 변경된 값을 되돌려 받을 수 있다.

DECLARE
	v_amt NUMBER;
BEGIN
	v_amt := get_wage(7788); -- 함수 버전
    get_wage_proc(7788, v_amt);  -- 프로시저 버전
END;

함수 get_wage와 프로시저 get_wage_proc의 호출은 동일하게 사번이 7788인 사원의 급여를 계산하여 그 결과를 변수 v_amt에 할당된다.

 

매개변수가 가질 수 있는 입출력 모드는 다음 세 가지다.

* IN: 매개변수는 호출자로부터 서브프로그램에게 읽기 전용 모드로 전달된다. 모드를 생략하면 기본적으로 IN 모드가 사용된다, 서브프로그램은 IN 모드 매개변수의 값을 읽을 수만 있고 변경할 수는 없다.

-- 컴파일 오류 발생하는 예
DECLARE
	PROCEDURE p(a_arg IN NUMBER) IS
    BEGIN
    	a_arg := a_arg + 1;	-- IN 모드의 매개변수를 변경하면 컴파일 오류
    END;
BEGIN
	p(1);
END;

IN 모드의 매개변수 a_arg의 값을 변경하였는데 실행 시 다음처럼 컴파일 오류가 발생한다.

 

* OUT: 매개변수는 호출자로부터 서브프로그램에게로 변경 전용 모드로서 전달된다. 서브프로그램 내부에서는 이 모드의 매개변수에 값을 할당하며 호출자는 서브프로그램 반환 후에 변경된 값을 읽을 수 있다. 서브프로그램에서 이 모드의 매개변수에 값을 할당하기 전에는 매개변수의 값이 NULL이다. 이 모드의 매개변수를 사용하면 호출자로부터 서브프로그램에게로 값을 전달할 수 없다.

 

* IN OUT: 매개변수는 읽기와 변경 모두가 가능하다. 이 모드의 매개변수를 사용하면 호출자로부터 서브프로그램에게로 값을 전달하고 서브프로그램에서 변경된 값을 호출자가 되돌려 받는 것이 가능하다.

 

서브프로그램의 두 가지 유형인 함수와 프로시저 모두 세 가지 모드의 매개변수 모두를 사용할 수 있다.

하지만 함수의 매개변수로는 IN 모드만이 바람직하고, OUT 모드와 IN OUT 모드는 프로시저에만 사용하는 것이 좋다. IN 모드가 아닌 매개변수를 가지는 함수는 사용상 많은 제약이 따르기 때문이다.

 

매개변수의 기본값

매개변수를 IN 모드로 선언하는 경우에는 기본값을 지정할 수 있다. 기본값을 지정한 매개변수는 서브프로그램 호출 시에 값을 생략할 수 있으며 생략되면 지정된 기본값을 사용하여 호출된다.

CREATE OR REPLACE PROCEDURE raise_salary(a_empno NUMBER, a_amt NUMBER := 100)

매개변수 a_amt에 기본값으로 100을 지정했다. 이 선언에 의하면 프로시저 raise_salary에 대한 다음 두 호출은 완전히 동일하다.

call raise_salary(7788);	-- 두 번째 매개변수를 생략하면 기본값으로 지정된 100이 사용된다.
call raise_salary(7788, 100); -- 두 번째 매개변수를 100으로 명시했다.

매개변수의 기본값 지정을 지원하는 프로그래밍 언어에서 기본값을 지정한 매개변수는 기본값을 지정하지 않은 매개변수보다 뒤에 와야 하는 것이 일반적이다. 오라클 PL/SQL의 매개변수에는 이 제약이 없어서 매개변수 기본값을 다음과 같이 선언할 수도 있다.

REM 첫 번째 매개변수만 기본값을 가지는 프로시저 선언
CREATE OR REPLACE PROCEDURE raise_salary_2(a_amt NUMBER := 100, a_empno NUMBER);

이름에 의한 매개변수 지정을 사용하면 다음과 같이 첫 번째 매개변수를 생략하여 사용할 수 있다.

REM 이름에 의한 매개변수 호출을 사용
call raise_salary_2(a_empno => 7788);	-- 첫 번째 매개변수 a_amt가 생략됨

위의 raise_salary_2 호출에서는 첫 번째 매개변수 a_amt가 생략됐는데 결과적으로 이 호출은 다음과 완전히 동일한 호출이다.

call raise_salary_2(100, 7788); -- 두 매개변수를 모두 명시

매개변수의 값 지정 방법 : 위치에 의한 지정과 이름에 의한 지정

서브프로그램의 호출 시 매개변수의 값을 지정하기 위해 위치에 의한 지정과 이름에 의한 지정 두 가지 방법을 사용할 수 있다.(참고로 커서의 매개변수도 두 방법을 모두 사용할 수 있다.)

* 위치에 의한 지정: 매개변수가 선언된 위치에 따라 매개변수를 순서적으로 나열한다. raise_salary 예제의 경우에는 항상 첫 번째 매개변수 자리에는 사번(a_empno)을 사용하고 두 번째 매개변수 자리에는 금액(a_amt)을 지정한다.

BEGIN raise_salary(7788/*사번*/. 100/*금액*/); END;

* 이름에 의한 지정: 선언된 매개변수의 위치와 무관하게 매개변수의 이름을 사용하여 값을 지정한다. 이름에 의한 지정 시에는 '매개변수이름 => 값' 형식을 시용한다.

-- 이름에 의한 매개변수 지정
REM 다음 두 개의 호출은 매개변수의 위치가 다르지만 실제로는 완전히 동일한 호출이다.
BEGIN raise_salary(a_empno => 7788, a_amt => 100); END;
/
BEGIN raise_salary(a_amt => 100, a_empno => 7788); END;
/

raise_salary에 대한 두 개의 호출은 매개변수의 배열 순서가 다르지만 완전히 동일한 호출이다.

위치에 의한 지정과 이름에 의한 지정은 혼합하여 사용할 수도 있다.

-- 위치에 의한 매개변수 지정과 이름에 의한 매개변수 지정의 혼합 사용
call raise_salary(7788, a_amt => 100);

위치에 의한 지정과 이름에 의한 지정은 함수와 프로시저에 동일하게 적용되는 규칙이다.

-- 함수에서 이름에 의한 매개변수 지정 사용
SELECT ename, empno, sal, comm
	, get_wage(empno) 급여		-- 위치에 의한 매개변수 지정
  FROM emp
 WHERE deptno = 30
   AND get_wage(a_empno => empno) >= 2000	-- 이름에 의한 매개변수 지정
 ORDER BY ename;

OUT 매개변수 변경의 원자성 보장

OUT 또는 IN OUT 매개변수를 가진 서브프로그램의 실행 중에 매개변수의 값을 변경한 상태에서 예외가 발생하였는데 서브프로그램에서 이 예외를 처리하지 않고 서브프로그램이 종료되는 경우, 호출 프로그램에서 바라보는 OUT 매개변수는 어떤 값을 가질까? 아마 두 가지 중 하나로 처리될 것으로 생각된다.

1. 모든 OUT 변수는 변경되지 않은 원래 값을 유지한다.

2. OUT 변수 중에서 변경된 매개변수는 변경된 값을 돌려받고 변경되지 않는 매개변수는 변경되지 않은 원래 값을 유지

 

다른 경우도 가정할 수 있겠지만 위 두 가지가 가장 합리적인 처리일 것이다. 실제로는 문장 레벨 원자성 보장 규칙에 의해 1번과 같이 처리된다.

CREATE OR REPLACE PROCEDURE out_argument_proc(a_num OUT NUMBER)
IS
BEGIN
	a_num := 1;		-- OUT 매개변수 a_num을 1로 변경했다. 호출자 측에서는 값이 어떨게 될까?
    RAISE_APPLICATION_ERROR(-20001, '처리하지 않은 exception 발생');
END;
/

DECLARE
	v_num NUMBER;
BEGIN
	v_num := 0;
    DBMS_OUTPUT.PUT_LINE('프로시저 호출 전 v_num = '||v_num);
    BEGIN
    	out_argument_proc(v_num);	-- 프로시저는 예외를 만나서 예외 처리기로 제어가 이동된다.
    EXCEPTION WHEN OTHERS THEN
    	DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
    DBMS_OUTPUT.PUT_LINE('프로시저 호출 후 v_num = '||v_num);	-- v_num이 변경되었을까?
END;
/

실행 결과는 다음과 같다.

프로시저 호출 전 v_num = 0
ORA-20001: 처리되지 않은 exception 발생
프로시저 호출 후 v_num = 0

PL/SQL 처리가 정상적으로 완료되었습니다.

실행 결과에서 22번 줄은 프로시저 호출 전에 OUT모드의 매개변수로 사용될 변수 v_num의 값을 보여 주고, 24번 줄은 프로시저 실행 중에 처리되지 않은 예외 발생 후 변수 v_num의 값을 보여 주는데 4번 줄에서 1로 변경된 a_num의 값이 반영되지 않았다. 15번 줄에서 프로시저 out_argument_proc가 호출되기 전 시점에서 매개변수로 사용된 v_num의 값은 12번 줄에서 할당된 0이다. 13번 줄의 출력 결과인 22번 줄이 v_num =0임을 보여 주고 있다.

프로시저 out_argument_proc가 호출되어 4번 줄에서 이 값이 1로 바뀌었다. 이후 5번 줄에서 예외가 발생했고, 제어는 PL/SQL 블록의 예외 처리기에 의해 17번 줄로 옮겨 진다. 오라클은 OUT 또는 IN OUT 타입의 매개변수를 가지는 서브프로그램을 호출 시 각 매개변수의 복사본을 서브프로그램에 전달하고 서브프로그램이 완료된 후에 변경된 최종 값을 원래 변수에 다시 복사해 주는 방법을 사용한다.

만약 호출된 서브프로그램 내에서 처리되지 않은 예외가 발생하면 오라클은 서브프로그램 내에서 변경된 매개변수의 값을 호출자에게 복사해 주지 않고 그냥 버리며 정상 복귀하는 경우에만 변경된 값을 매개변수로 사용된 변수에 복사한다. 따라서 19번 줄의 출력문에서 v_num의 값이 프로시저 out_argument_proc의 4번 줄에서 변경된 값인 1이 아니라 원래의 값인 0으로 출력되는 것이다. 프로시저에 OUT 매개변수가 여러 개인데 그 중에서 일부만 변경된 상태에서 예외가 발생하더라도 일부 OUT변수만 변경된 값을 돌려받는 일은 발생하지 않는다. 다시 말해 OUT 매개변수들의 값들도 All or Nothing으로 처리되어 원자성이 보장된다.

 

매개변수의 전달 방식: 값에 의한 호출과 참조에 의한 호출

PL/SQL 엔진은 매개변수의 전달에 다음의 두가지 방식을 사용한다. C/C++나 자바 프로그래밍 언어에서는 서브프로그램  내부에서 변경된 값을 매개변수를 통해 되돌려 받고자 할 때 참조에 의한 호출을 사용하고 변경된 값을 되돌려 받는 것을 원하지 않는 경우에는 값에 의한 호출을 사용하는데, PL/SQL에서는 정반대로 사용된다.

 

• 값에 의한 호출 (Call by Value): 서브프로그램의 OUT과 IN OUT 매개변수에 기본적으로 사용되는 호출 방식이다. 저장 서브프로그램 호출 시, IN OUT 모드라면 매개변수로 지정된 변수 또는 리터럴 값의 복사본을, OUT 모드라면 NULL 값을 가진 변수를 생성하고 이 값을 매개변수로 전달한다. OUT 또는 IN OUT 모드의 매개변수는 서브프로그램에 의해 변경될 수 있다. OUT 또는 IN OUT 모드의 변경된 매개변수 값은 서브프로그램이 미처리된 예외 없이 완료된 후에는 매개변수로 지정된 원래 변수에 복사된다. 서브프로그램이 처리되지 않은 예외를 만나서 반환되는 경우에는 OUT 매개변수의 값을 원래 변수에 복사해 주지 않기 때문에 서브프로그램 내부에서 OUT 변수에 가해진 변경은 버려진다. 서브프로그램 호출 전/후의 매개변수 값의 복사에 CPU 시간과 메모리가 소요되므로 효율 저하가 발생한다.
• 참조에 의한 호출(Call by Reference): 서브프로그램의 IN 매개변수에 기본적으로 사용되는 호출 방식.                   저장 서브프로그램 호출시에 매개변수로 지정된 변수에 대한 포인터를 전달한다. IN 모드 매개변수는 값을 변경할 수 없는 상수오 같이 동작하므로 변경으로부터 보호된다. 서브프로그램 호출 전/후에 매개변수 값을 복사하는 작업이 없기 때문에 CPU시간이나 메모리를 추가로 사용하지 않아서 효율적이다.

NOCOPY 매개변수

대량 데이터를 OUT 또는 IN OUT 모드의 매개변수로 전달할 때의 비효율을 회피할 수 있는 방법으로 NOCOPY 매개변수가 제공된다. NOCOPY는 기본적으로 값에 의한 호출을 사용하는 OUT 또는 IN OUT 모드의 매개변수를 참조에 의한 호출로 전환한다. NOCOPY로 호출되는 매개변수는 참조에 의한 호출 방식이 사용되지만 서브프로그램 내에서 변경이 가능하다. 이 방법을 사용하면 대규모의 값을 매개변수로 전달할 경우 메모리 복사를 회피하여 성능 향상이라는 장점을 얻을 수 있다. 하지만 그 대가로 저장 서브프로그램에서 매개변수의 원자성 보장이라는 장점을 희생해야 한다.

DECLARE
	v_my_exception EXCEPTION;
    
    v1 NUMBER := 0;
    v2 NUMBER := 0;
    
    -- 매개변수의 원자성 보장
    PROCEDURE p_normal(a_1 OUT NUMBER, a_2 OUT NUMBER)
    IS
    BEGIN
    	-- 예외 발생 이전에 매개변수 값을 변경하지만 메인 블록으로 전달되지 않는다.
        a_1 := 10;
        RAISE v_my_exception;	-- 예외 발생
        a_2 := 10;
    END;
    
    -- NOCOPY 매개변수, 메모리 복사로 인한 부하는 사라지나, 매개변수의 원자성은 미보장
    PROCEDURE p_nocopy(a_1 OUT NOCOPY NUMBER, a_2 OUT NOCOPY NUMBER)
    IS
    BEGIN
    	-- NOCOPY를 사용했기 때문에 예외가 발생하면 변경이 메인 블록으로 전달된다.
        a_1 := 10;
        RAISE v_my_exception; -- 예외 발생
        a_2 := 10;
    END;
BEGIN
	DBMS_OUTPUT.PUT_LINE('p_normal(NOCOPY 미사용) 호출 전 : v1 = '|| v1 || ', v2 = '|| v2);
    BEGIN
    	p_normal(v1,v2);	-- 매개변수 원자성 보장 버전 호출
    EXCEPTION WHEN v_my_exception THEN NULL;
    END;
    
    DBMS_OUTPUT.PUT_LINE('p_normal(NOCOPY 미사용) 호출 후 : v1 = '|| v1 || ', v2 = ' || v2);
    BEGIN
    	p_nocopy(v1,v2); -- NOCOPY로 인한 매개변수 원자성 미보장 버전 호출
    EXCEPTION WHEN v_my_exception THEN NULL;
    END;
    DBMS_OUTPUT.PUT_LINE('p_nocopy(NOCOPY 사용) 호출 후 : v1 = '|| v1 || ', v2 = '|| v2);
END;
/

p_normal(NOCOPY 미사용) 호출 전 : v1 = 0, v2 = 0
p_normal(NOCOPY 미사용) 호출 후 : v1 = 0, v2 = 0
p_normal(NOCOPY 사용)   호출 후 : v1 = 10, v2 =

위 예제의 프로시저 p_normal은 원자성을 보장하는 버전이고, p_nocopy는 NOCOPY를 사용하여 성능은 높으나 원자성을 보장하지 않는 버전이다. 둘 다 첫 번째 매개변수 a_1을 변경한 후에 바로 예외를 발생시켜 메인 프로그램으로 복귀했다. p_normal이 예외로 인해 복귀한 후에도 두 변수 모두가 호출 전과 동일한 값을 유지하고 있다. 그러나 p_nocopy가 예외로 인해 복귀한 후에는 v1은 서브 프로시저에 의해 변경된 값인 10을 가지고 있다. 서브프로그램에 의해 변경되지 않는 매개변수 v2도 값이 NULL로 변경되었음을 알 수 있는데, 이는 OUT 타입의 매개변수의 기본값이 NULL이기 때문이다. 결과적으로 서브프로그램 p_nocopy에서 예외가 발생할 때에는 모든 OUT 타입의 매개변수는 값이 변경된다. IN OUT 타입의 경우에는 서브프로그램에서 값을 변경하지 않는 매개변수의 값은 보존된다.