프로시저 실행

    함수는 반환 값을 받으므로 실행할 때 ‘호출’이라고 명명하지만 프로시저는 ‘호출’ 혹은 ‘실행’한다고 표현하는데, 실제로는 후자를 많이 사용하는 편이다. 프로시저는 반환 값이 없으므로 함수처럼 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
    
    신간 소식 구독하기
    뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.