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