프로시저 실행

함수는 반환 값을 받으므로 실행할 때 ‘호출’이라고 명명하지만 프로시저는 ‘호출’ 혹은 ‘실행’한다고 표현하는데, 실제로는 후자를 많이 사용하는 편이다. 프로시저는 반환 값이 없으므로 함수처럼 SELECT 절에는 사용할 수 없고 다음과 같이 실행해야 한다.

    <프로시저 실행1>
    EXEC 혹은 EXECUTE 프로시저명(매개변수1 값, 매개변수2 값, ...);

그럼 my_new_job_proc 프로시저를 실행해 보자.

입력

    EXEC my_new_job_proc ('SM_JOB1', 'Sample JOB1', 1000, 5000);

결과

    익명 블록이 완료되었습니다.

오류가 나지는 않았지만 제대로 입력됐는지 jobs 테이블을 조회해 보자.

입력

    SELECT *
      FROM jobs
     WHERE job_id = 'SM_JOB1';

결과

    JOB_ID   JOB_TITLE    MIN_SALARY MAX_SALARY CREATE_DATE         UPDATE_DATE
    -------- ------------ ---------- ---------- ------------------- ------------------
    SM_JOB1  Sample JOB1  1000       5000       2015/03/12 15:31:49 2015/03/12 15:31:49

성공적으로 입력됐다. 재차 확인하기 위해 다시 한 번 프로시저를 실행해 보자.

입력

    EXEC my_new_job_proc ('SM_JOB1', 'Sample JOB1', 1000, 5000);

결과

    ORA-00001: 무결성 제약 조건(ORA_USER.PK_JOBS)에 위배됩니다.

이번에는 오류가 발생했다. 원인은 jobs 테이블의 job_id는 PRIMARY KEY로 잡혀 있는데도 불구하고 동일한 job_id(SM_JOB1)를 또 입력하려고 시도했기 때문이다. 이렇게 기본적인 데이터 무결성 문제는 오라클에서 자동으로 걸러준다. 오라클에게 오류 처리를 맡길 것이 아니라, 동일한 job_id가 들어오면 신규 INSERT 대신 다른 정보를 갱신하도록 프로시저를 수정해 보자.

입력

    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,
      p_max_sal   IN JOBS.MAX_SALARY%TYPE )
    IS
      vn_cnt NUMBER := 0;
    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, SYSDATE, SYSDATE);
      ELSE -- 있으면 UPDATE
        UPDATE JOBS
          SET job_title   = p_job_title,
              min_salary  = p_min_sal,
              max_salary  = p_max_sal,
              update_date = SYSDATE
             WHERE job_id = p_job_id;
      END IF;
      COMMIT;
    END ;

결과

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

다시 실행해 보자. 이번에는 최소 급여값, 최대 급여값을 수정해서 입력해 보자.

입력

    EXEC my_new_job_proc ('SM_JOB1', 'Sample JOB1', 2000, 6000);

    SELECT *
      FROM jobs
     WHERE job_id = 'SM_JOB1';

결과

    JOB_ID   JOB_TITLE     MIN_SALARY MAX_SALARY CREATE_DATE         UPDATE_DATE
    -------- ------------- ---------- ---------- ------------------- -----------------
    SM_JOB1  Sample JOB1   2000       6000       2015/03/12 15:31:49 2015/03/12 16:27:19

동일한 job_id 값을 입력했더니 INSERT를 하지 않고 매개변수로 전달된 다른 정보를 UPDATE했음을 확인할 수 있다.

프로시저의 매개변수가 많으면 실행할 때 매개변수 값의 개수나 순서를 혼동할 소지가 매우 많다. 이런 경우에는 다음과 같은 형태로 매개변수와 입력 값을 매핑해 실행하면 매우 편리하다.

    <프로시저 실행2>
    • EXEC 혹은 EXECUTE 프로시저명(매개변수1 => 매개변수1 값,
    　　　　　　　　　　  　　　　　매개변수2 => 매개변수2 값, ...);

‘=>’ 기호를 사용해 해당 매개변수명과 값을 연결하는 형태로 실행할 수 있다. my_new_job_proc 프로시저를 이 형태로 실행해 보자.

입력

     EXECUTE my_new_job_proc (p_job_id => 'SM_JOB1', p_job_title => 'Sample JOB1',
                               p_min_sal => 2000, p_max_sal => 7000);

결과

    익명 블록이 완료되었습니다.

입력

    SELECT *
      FROM jobs
     WHERE job_id = 'SM_JOB1';

결과

    JOB_ID   JOB_TITLE     MIN_SALARY MAX_SALARY CREATE_DATE         UPDATE_DATE
    -------- ------------- ---------- ---------- ------------------- ------------------
    SM_JOB1  Sample JOB1   2000       7000       2015/03/12 15:31:49 2015/03/12 16:50:13