더북(TheBook)

OUT, IN OUT 매개변수

프로시저와 함수의 가장 큰 차이점은 반환 값의 존재 여부다. 그런데 프로시저에서도 값을 반환하는 방법이 있는데 바로 OUT 매개변수를 통해서 실현할 수 있다. OUT 매개변수란 프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당한다. 그리고 나서 실행이 끝나면 전달한 변수를 참조해 값을 가져올 수 있는 것이다. 프로시저 생성 시 매개변수명과 데이터 타입만 명시하면 디폴트로 IN 매개변수가 되지만 OUT 매개변수는 반드시 OUT 키워드를 명시해야 한다. , my_new_job_proc에서 갱신일자를 받는 OUT 매개변수를 추가해 보자.

입력

    CREATE OR REPLACE PROCEDURE my_new_job_proc
    ( p_job_id    IN JOBS.JOB_ID%TYPE,
      p_job_title IN JOBS.JOB_TITLE%TYPE,
      p_min_sal   IN JOBS.MIN_SALARY%TYPE := 10,
      p_max_sal   IN JOBS.MAX_SALARY%TYPE := 100 )
      p_upd_date  OUT JOBS.UPDATE_DATE%TYPE )
    IS
      vn_cnt NUMBER := 0;
      vn_cur_datec JOBS.UPDATE_DATE%TYPE := SYSDATE;
    BEGIN

      -- 동일한 job_id가 있는지 체크
      SELECT COUNT(*)
        INTO vn_cnt
        FROM JOBS
       WHERE job_id = p_job_id;

      -- 없으면 INSERT
      IF vn_cnt = 0 THEN
        INSERT INTO JOBS ( job_id, job_title, min_salary, max_salary, create_date, update_date)
                  VALUES ( p_job_id, p_job_title, p_min_sal, p_max_sal, vn_cur_date, vn_cur_date);

      ELSE -- 있으면 UPDATE
        UPDATE JOBS
          SET job_title  = p_job_title,
             min_salary  = p_min_sal,
             max_salary  = p_max_sal,
             update_date = vn_cur_date
        WHERE job_id = p_job_id;
      END IF;

     -- OUT 매개변수에 일자 할당
     p_upd_date : = vn_cur_date;

      COMMIT;
    END ;

결과

    PROCEDURE MY_NEW_JOB_PROC이(가) 컴파일되었습니다.

기존에는 jobs 테이블의 create_date, update_date 컬럼 값으로 SYSDATE를 직접 입력했지만 이번에는 vn_cur_date 변수를 선언해 SYSDATE로 초기화한 뒤, 이 변수를 사용했다. 이렇게 처리한 이유는 SYSDATE는 초 단위로 값이 바뀌므로 jobs 테이블에 입력되거나 갱신된 일자 값을 정확히 가져오기 위해서다. 이제 프로시저를 실행하고 OUT 매개변수 값을 참조해야 하는데, 이때 별도의 변수를 선언해서 매개변수로 전달한 뒤 값을 참조해야 한다. 변수가 필요하므로 이 프로시저를 실행하는 익명 블록을 만들어 보자.

입력

    DECLARE
       vd_cur_date JOBS.UPDATE_DATE%TYPE;
    BEGIN
      EXECmy_new_job_proc ('SM_JOB1', 'Sample JOB1', 2000, 6000, vd_cur_date);

      DBMS_OUTPUT.PUT_LINE(vd_cur_date);
    END;

결과

    ORA-06550: 줄 4, 열8:PLS-00103: 심볼 "MY_NEW_JOB_PROC"를 만났습니다 다음 중 하나가 기대될 때:...

오류가 발생했다. 무엇이 잘못된 것일까? 익명 블록에서 프로시저를 실행하면 EXEC나 EXECUTE를 붙이지 않는다. 다른 함수나 프로시저, 패키지에서 실행할 경우도 마찬가지다.

입력

    DECLARE
      vd_cur_date JOBS.UPDATE_DATE%TYPE;
    BEGIN
      my_new_job_proc ('SM_JOB1', 'Sample JOB1', 2000, 6000, vd_cur_date);

      DBMS_OUTPUT.PUT_LINE(vd_cur_date);
    END;

결과

    2015/03/13 11:36:18

성공적으로 프로시저가 실행됐고 OUT 매개변수로 전달한 vd_cur_date 변수에 값이 할당됐음을 알 수 있다.

또 다른 유형으로 IN OUT 매개변수가 있는데, 이렇게 선언하면 입력과 동시에 출력용으로 사용할 수 있다. 여기서 한 가지 짚고 넘어갈 점이 있는데, 프로시저 실행 시 OUT 매개변수에 전달할 변수에 값을 할당해서 넘겨줄 수 있지만 큰 의미는 없는 일이다. OUT 매개변수는 해당 프로시저가 성공적으로 실행을 완료할 때까지 값이 할당되지 않는다. 따라서 매개변수에 값을 전달해서 사용한 다음 다시 이 매개변수에 값을 받아와 참조하고 싶다면 IN OUT 매개변수를 사용해야 한다. 이런 내용을 확인할 수 있는 간단한 프로시저를 만들어 보자.

입력

    CREATE OR REPLACE PROCEDURE my_parameter_test_proc (
                   p_var1        VARCHAR2,
                   p_var2 OUT    VARCHAR2,
                   p_var3 IN OUT VARCHAR2 )
    IS

    BEGIN
     DBMS_OUTPUT.PUT_LINE('p_var1 value = ' || p_var1);
     DBMS_OUTPUT.PUT_LINE('p_var2 value = ' || p_var2);
     DBMS_OUTPUT.PUT_LINE('p_var3 value = ' || p_var3);

     p_var2 := 'B2';
     p_var3 := 'C2';
    END;

결과

    PROCEDURE MY_PARAMETER_TEST_PROC이(가) 컴파일되었습니다.

이제 프로시저를 실행해 보자.

입력

    DECLARE
       v_var1 VARCHAR2(10) := 'A';
       v_var2 VARCHAR2(10) := 'B';
       v_var3 VARCHAR2(10) := 'C';
    BEGIN
      my_parameter_test_proc (v_var1, v_var2, v_var3);

      DBMS_OUTPUT.PUT_LINE('v_var2 value = ' || v_var2);
      DBMS_OUTPUT.PUT_LINE('v_var3 value = ' || v_var3);
    END;

결과

    p_var1 value = A
    p_var2 value =
    p_var3 value = C
    v_var2 value = B2
    v_var3 value = C2

p_var로 시작되는 결과는 my_parameter_test_proc 내부에서 출력한 것이고, v_var로 시작되는 결과는 이 프로시저를 실행한 익명 블록에서 출력한 것이다. OUT 매개변수인 p_var2 자리에 v_var2 변수를 넣어 ‘B’라는 값을 넘겨 줬음에도 불구하고 아무런 값도 없음을 확인할 수 있다. 이에 반해 IN OUT 매개변수인 p_var3에는 ‘C’란 값을 넘겨 줘서 my_parameter_test_proc 내부에서 이 값을 받아 출력했고, 다시 ‘C2’로 값을 할당해서 최종적으로 v_var3 값은 ‘C2’가 된 것이다.

지금까지 IN, OUT, IN OUT 매개변수의 쓰임새에 대해 살펴봤는데, 매개변수에 대해 꼭 알아두어야 할 사항을 정리하면 다음과 같다.

❶ IN 매개변수는 참조만 가능하며 값을 할당할 수 없다.

❷ OUT 매개변수에 값을 전달할 수는 있지만 의미는 없다.

❸ OUT, IN OUT 매개변수에는 디폴트 값을 설정할 수 없다.

❹ IN 매개변수에는 변수나 상수, 각 데이터 유형에 따른 값을 전달할 수 있지만, OUT, IN OUT 매개변수를 전달할 때는 반드시 변수 형태로 값을 넘겨줘야 한다.

신간 소식 구독하기
뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.