프로시저
함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만 프로시저는 특정한 로직을 처리하기만하고 결과 값을 반환하지 않는 서브프로그램이다.
-
일반적으로 프로젝트 현장에서 시스템 설계가 끝난 후 업무를 분할하고 이 분할한 업무 단위로 로직을 구현해야 하는데, 개별적인 단위 업무를 프로시저로 처리한다. (약간 프로세스와 스레드의 느낌이 있는 것 같다.)
-
각각의 데이터의 조작을 맞치고 테이블에 값을 다시 저장하거나 갱신함
프로시저 생성
create or replace procedure 프로시저 이름
(매개변수명1[in |out | in out] 데이터타입[:= default value],
매개변수명2[in |out | in out] 데이터타입[:= default value],
...)
is[as]
변수, 상수 등 선언
[exception
예외처리부]
end [프로시저명];-
in, out, in out은 데이터를 입력하는지, 출력하는지이다.
-
작은 작업 단위를 프로시저로 처리하여 효율적이게 전체 pl/sql을 다루는 것 같은데, 굳이 사용하는 이유에 대해 고민해보자!
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 ;프로시저 실행
-
함수는 반환 값을 받으므로 실행하면 '호출'이라고 부른다. 하지만 프로시저는 '호출한다'와 '실행한다' 두 가지의 의미를 모두 가질 수 있다
-
프로시저는 반환 값이 없으므로 함수처럼 select 절에는 사용할 수 없다.
--프로시저 실행1--
exec/execute 프로시저명(매개변수1 값, 매개변수2 값)
--프로시저 실행2--
exec 프로시저명(매개변수1 => 매개변수1 값,
매개변수2 => 매개변수2 값, ...);-
테이블을 실제로 만들어서 사용할 수 없어 프로시저 실습을 해보지 못함, 아니면 임의의 테이블을 만들어서 사용할 수는 있다
-
프로시저 실행2를 보면 =>를 이용하여 매개변수명과 값을 연결하는 형태로 실행할 수 있다.
프로시저 매개변수 디폴트 값 설정
- 프로시저를 실행할 때는 매개변수의 개수에 맞춰 값을 전달해 실행해야 함.
예를 들어
create or replace procedure insert_new_member_proc(
member_id in members.member_id%type,
member_name in members.member_name%type,
member_age in members.member_age%type := 19) --디폴트값--
is
...
...위 처럼 설계한다면
exec insert_new_member(100, 'lee');를 수행하더라도 오류를 발생시키지 않는다.
Out, In Out 매개변수
out
- 프로시저와 함수는 반환값의 존재여부이다
- 프로시저는 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 ; DECLARE
vd_cur_date JOBS.UPDATE_DATE%TYPE;
BEGIN
EXEC my_new_job_proc ('SM_JOB1', 'Sample JOB1', 2000, 6000, vd_cur_date);
DBMS_OUTPUT.PUT_LINE(vd_cur_date);
END;
- 오류를 유발한다 왜냐하면 익명 블록에서는 프로시저를 호출할때
exec/execute를 붙이지 않기 때문
my_new_job_proc ('SM_JOB1', 'Sample JOB1', 2000, 6000, vd_cur_date);In out
CREATE OR REPLACE PROCEDURE my_parameter_test_proc (
p_var1 VARCHAR2,
p_var2 OUT VARCHAR2,
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 := 'B2';
p_var3 := 'C2';
END;
DECLARE
v_var1 VARCHAR2(10) := 'A';
v_var2 VARCHAR2(10) := 'B';
v_var3 VARCHAR2(10) := 'C';
BEGIN
my_parameter_test_proc (v_var1, v_var2, v_var3);
DBMS_OUTPUT.PUT_LINE('v_var2 value = ' || v_var2);
DBMS_OUTPUT.PUT_LINE('v_var3 value = ' || v_var3);
END;
--실행 결과--
p_var1 value = A
p_var2 value =
p_var3 value = C
v_var2 value = B2
v_var3 value = C2
-
다음과 같은 실행결과를 갖는 이유는 디폴트로 in, out을 적지 않으면 in으로 사용되기 때문에 p_var1은 정상 출력 가능, var2는 값을 출력하지 않음
-
하지만 프로시저 내에서 값을 넣어준 뒤 익명 블록에서 실행하면 정상 동작 하는 것을 알 수 있음
-
익명 블록, 블록 > 프로시저, 함수
