(3)PL/SQL PROCEDURE
PL/SQL의 PROCEDURE 사용법
시작하기 전에
사용 교재 : 오라클SQL과 PL/SQL을 다루는 기술 (https://thebook.io/006696/)
예제 코드 : https://github.com/gilbutITbook/006696
SERVEROUTPUT 설정
PL/SQL 결과물을 보기 위해 아래의 실행문을 실행
SET SERVEROUTPUT ON;
PL/SQL PROCEDURE
사용자 정의 함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만,
프로시저는 특정한 로직을 처리하기만 하고 값을 반환하지는 않는 서브 프로그램이다.
PROCEDURE 기본 구조
CREATE OR REPLACE PROCEDURE 프로시저 이름(
매개변수명1 [IN | OUT | IN OUT] 테이터타입 [:= 디폴트 값],
:
:
매개변수명n [IN | OUT | IN OUT] 테이터타입 [:= 디폴트 값]
)
IS[AS]
변수, 상수 등 선언
BEGIN
실행부
[EXCEPTION]
예외처리
END [프로시저 이름];
- []는 생략 가능
PROCEDURE 예제
예제1
- jobs 테이블에 신규 JOB을 넣는 프로시저
-- my_new_job_proc 프로시저 신규생성
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
BEGIN
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);
COMMIT;
END;
/
-- 프로시저 실행
EXEC my_new_job_proc('SM_JOB1', 'Sample JOB1', 1000, 5000);
-- 프로시저 결과
select * from jobs;
예제2
- 예제 1을 수정
- 데이터가 있을 경우 INSERT / 없을 경우 UPDATE
-- my_new_job_proc 프로시저 수정
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;
-- cnt가0 이다 -> 데이터가 없다 -> 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);
-- cnt가 0이 아님 -> 데이터가 있다 -> UPDATE
ELSE
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;
/
-- 프로시저 실행 (UPDATE)
EXEC my_new_job_proc('SM_JOB1', 'Sample JOB1', 1500, 5000);
-- 프로시저 결과
select * from jobs;
- 기존의 SM_JOB1의 MIN_SALARY가 1500으로 UPDATE
-- 프로시저 실행 (INSERT)
EXEC my_new_job_proc('SM_JOB2', 'Sample JOB2', 2000, 6000);
-- 프로시저 결과
select * from jobs;
- SM_JOB2는 기존에 있는 데이터가 아니므로 INSERT
예제3
- 예제2를 수정
- 프로시저를 실행할 때 디폴트 값을 주어, 매개변수를 입력하지 않아도 실행되게 변경
- **<주의> IN 매개변수에만 사용 가능**
-- my_new_job_proc 프로시저 수정
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 := 1000
)
-- 아랫부분은 동일
-- 프로시저 실행
EXEC my_new_job_proc('SM_JOB3', 'Sample JOB3');
-- 프로시저 결과
select * from jobs;
- 매개변수 없이 프로시저를 실행했지만, 세팅해놓은 디폴트 값 10, 1000이 자동으로 입력
PROCEDURE - IN , OUT, IN OUT
프로시저는 값을 반환하지 않는 서브 프로그램이지만, 값을 반환하는 방법이 존재 - OUT 매개변수
IN, OUT, IN OUT 예제
- 프로시저 생성
-- my_parameter_test_proc 신규 생성
CREATE OR REPLACE PROCEDURE my_parameter_test_proc (
-- IN : 값을 받기만 함
p_var1 VARCHAR2,
-- OUT : 값을 반환
p_var2 OUT VARCHAR2,
-- IN OUT : 값을 받고 반환함
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 := 'A2';
p_var2 := 'B2';
p_var3 := 'C2';
END;
/
- 익명블록으로 테스트
DECLARE
v_var1 VARCHAR2(10) := 'A';
v_var2 VARCHAR2(10) := 'B';
v_var3 VARCHAR2(10) := 'C';
BEGIN
-- (1)프로시저 호출
my_parameter_test_proc (v_var1, v_var2, v_var3);
-- (2)프로시저 호출 후 값 비교
DBMS_OUTPUT.PUT_LINE('v_var1 value = ' || v_var1);
DBMS_OUTPUT.PUT_LINE('v_var2 value = ' || v_var2);
DBMS_OUTPUT.PUT_LINE('v_var3 value = ' || v_var3);
END;
/
p_var1은 IN이므로 A가 저장 및 실행
p_var2는 OUT이므로 B가 저장 되지 않고 실행 후 B2를 반환
p_var3은 IN OUT이므로 C를 저장하고 실행 후 C2를 반환
-------------------------------------------
v_var1은 반환되어진 A가 아니라, 원래 선언해두었던 A를 표시
v_var2는 반환된 B2 표시
v_var3은 반환된 C2 표시
PROCEDURE RETURN
사용자 정의 함수에서의 RETURN은 값을 반환하는 역할이지만
프로시저에서의 RETURN은 수행을 종료하는 역할을 한다
PROCEDURE RETURN 예제
- PROCEDURE 예제 수정
CREATE OR REPLACE PROCEDURE my_new_job_proc(
...
)
...
BEGIN
-- 1000보다 작으면 메시지 출력 후 RETURN
IF p_min_sal <1000 THEN
DBMS_OUTPUT.PUT_LINE('최소 급여값은 1000 이상이어야 함');
RETURN;
END IF;
SELECT COUNT(*)
...
...
...
-- 프로시저 실행 : 1000보다 작은 값
EXEC my_new_job_proc('SM_JOB4', 'Sample JOB4', 999, 50000);
-- 프로시저 결과
select * from jobs;
- 프로시저는 완료되지만 ‘SM_JOB4’는 저장되지 않는다.
Leave a comment