프로시저 실행
함수는 반환 값을 받으므로 실행할 때 ‘호출’이라고 명명하지만 프로시저는 ‘호출’ 혹은 ‘실행’한다고 표현하는데, 실제로는 후자를 많이 사용하는 편이다. 프로시저는 반환 값이 없으므로 함수처럼 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