- 반환값이 없는 프로그램, 특정 연산을 수행하기만 함. - 테이블의 갱신이나 다른 테이블에 데이터를 백업하거나 하는 경우 등 이용한다.
저장 프로시저 (Stored Procedure) - 자주 사용되는 쿼리문을 모듈화시켜서 필요할 때마다 호출하여 사용하는 것
SYNTAX CREATE OR REPLACE PROCEDURE 프로시저명 [ (P1 [ IN | OUT | INOUT ] 데이터타입 := 값, ...) ] IS / AS 변수, 상수 등 선언 BEGIN 실행문 ; ... [ EXCEPTION 예외처리문 ; ] --RETURN문 미존재시 PROCEDURE END [프로시저명] ; /
키포인트 - IN매개변수는 참조만 가능하면 값을 할당 할 수 없다. 초기값 설정 가능 - OUT매개변수에 값을 전달할 수는 있지만 의미는 없다. 초기값 가질 수 없다. - IN OUT 매개변수는 값 참조 및 전달이 가능하다. - OUT, IN OUT 매개변수에는 디폴트값을 설정할 수 없다.
- IN 매개변수에는 변수나 상수, 각 데이터 유형에 따른 값을 전달 할 수 있지만 OUT, IN OUT 매개변수를 전달할 때는 반드시 변수 형태로 값을 넘겨줘야한다. *** OUT키워드가 들어간 매개변수는 실제값을 호출 못 함. 기본값 설정 안 됨.
IN OUT 매개변수 입력과 동시에 출력용으로 사용할 수 있다. 프로시저 실행시 OUT매개변수에 전달할 변수에 값을 할당해서 넘겨줄 수 있지만 의미없다. 왜냐하면 OUT매개변수는 프로시저가 성공적으로 실행을 완료할 때까지 값이 할당되지 않기 때문이다. 그러므로 매개변수에 값을 전달해서 사용한 다음 다시 이 매개변수에 값을 받아와 참조하고 싶다면 IN OUT 매개변수를 이용해야 한다.
--한 줄명령은 ;처리를 해도 줄바꿈없이 주석이 기술되면 오류가 발생한다.
DROP TYPE ED_TYPE FORCE;
15
CREATE PROCEDURE test IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello Function~');
END;
/
EXEC test;
-- 프로시저 호출
CREATE OR REPLACE PROCEDURE res ( sname IN VARCHAR2 )
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(sname || '님 합격!!');
END;
/
EXEC res('지화자');
--익명블록에서 호출
BEGIN
RES('와우');
END;
/
555
--[PROCEDURE]
CREATE OR REPLACE PROCEDURE MY_NEW_JOB_PROC (
P_JOB_ID IN JOBS.JOB_ID%TYPE, --VARCHAR2(10 BYTE)
P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE, --VARCHAR2(80 BYTE)
P_MIN_SAL IN JOBS.MIN_SALARY%TYPE, --NUMBER(8,2)
P_MAX_SAL IN JOBS.MAX_SALARY%TYPE) --NUMBER(8,2)
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; --INSERT문
END;
/
--프로시저는 반환값이 없으므로 함수처럼 SELECT절에는 사용할 수 없다.
--호출문 형식: EXEC(EXECUTE) 프로시저명(매개변수1값, 매개변수2값, ...);
EXEC MY_NEW_JOB_PROC('SM_JOB1', 'SAMPLE JOB1', 1000, 5000);
--데이터가 있으면 UPDATE, 없으면 INSERT하기
DECLARE
V_JOB_ID JOBS.JOB_ID%TYPE;
V_JOB_TITLE JOBS.JOB_TITLE%TYPE;
V_MIN_SAL JOBS.MIN_SALARY%TYPE;
V_MAX_SAL JOBS.MAX_SALARY%TYPE;
BEGIN
SELECT D.DEPTNO, D.DNAME, 1500, 5000
INTO V_JOB_ID, V_JOB_TITLE, V_MIN_SAL, V_MAX_SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND E.ENAME = 'SMITH';
MY_NEW_JOB_PROC(V_JOB_ID, V_JOB_TITLE, V_MIN_SAL, V_MAX_SAL);
END;
--[PROCEDURE]
--데이터가 있으면 UPDATE, 없으면 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
SELECT COUNT(*)
INTO VN_CNT
FROM JOBS
WHERE JOB_ID = P_JOB_ID;
IF VN_CNT > 0 THEN
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;
DBMS_OUTPUT.PUT_LINE('UPDATE문 실행완료');
ELSE
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);
DBMS_OUTPUT.PUT_LINE('INSERT문 실행완료');
END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE('갱신이 완료되었습니다.');
END;
/
--매개 변수 개수가 많아서 인자값이 어떤 변수에 바인딩되는지 잘 모르겠을 때,
--프로시저의 매개변수명과 동일하게 해주어야 하며, 순서는 바뀌어도 상관없다.
EXECUTE MY_NEW_JOB_PROC(P_JOB_TITLE=> 'SAMPLE JOB1', P_JOB_ID =>'SM_JOB1',
P_MAX_SAL => 9000, P_MIN_SAL =>500);
--[PROCEDURE]
--IN OUT 명시없으면 디폴트는 IN
--매개변수에 초기값이 대입되어 있을 경우, 호출문에 값이 없으면 초기값으로 대입됨.
CREATE OR REPLACE PROCEDURE MY_NEW_JOB_PROC (
P_JOB_ID JOBS.JOB_ID%TYPE,
P_JOB_TITLE JOBS.JOB_TITLE%TYPE,
P_MIN_SAL JOBS.MIN_SALARY%TYPE := 10,
P_MAX_SAL JOBS.MAX_SALARY%TYPE := 100)
IS
VN_CNT NUMBER :=0;
BEGIN
SELECT COUNT(*)
INTO VN_CNT
FROM JOBS
WHERE JOB_ID = P_JOB_ID;
IF VN_CNT > 0 THEN
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;
DBMS_OUTPUT.PUT_LINE('UPDATE문 실행완료');
ELSE
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);
DBMS_OUTPUT.PUT_LINE('INSERT문 실행완료');
END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE('갱신이 완료되었습니다.');
END;
/
EXEC MY_NEW_JOB_PROC ('SM_JOB1', 'SAMPLE JOB1', 2000);
--매개변수에 초기값이 대입되어 있을 경우, 호출문에 값이 없으면 초기값으로 대입됨.
--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_UPDATE_DATE OUT JOBS.UPDATE_DATE%TYPE )
IS
VN_CNT NUMBER := 0;
VN_CUR_DATE JOBS.UPDATE_DATE%TYPE := SYSDATE;
BEGIN
SELECT COUNT(*)
INTO VN_CNT
FROM JOBS
WHERE JOB_ID = P_JOB_ID ;
DBMS_OUTPUT.PUT_LINE('UPDATE문 실행완료');
IF VN_CNT > 0 THEN
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;
ELSE
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);
DBMS_OUTPUT.PUT_LINE('UPDATE문 실행완료');
END IF;
P_UPDATE_DATE := VN_CUR_DATE;
DBMS_OUTPUT.PUT_LINE('갱신이 완료되었습니다. :'||P_UPDATE_DATE);
COMMIT;
END;
/
--변수를 선언하야 하기때문에 EXEC이 아닌 익명블록으로 진행해야 한다.
DECLARE
VD_CUR_DATE JOBS.UPDATE_DATE%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE(VD_CUR_DATE);
MY_NEW_JOB_PROC ('SM_JOB1','SAMPLE JOB1', 2000,6000,VD_CUR_DATE);
DBMS_OUTPUT.PUT_LINE(VD_CUR_DATE);
END;
/
ㅁㅁ
CREATE OR REPLACE PROCEDURE MY_PARAMETER_TEST_PROC
( P_VAR1 VARCHAR2,
P_VAR2 OUT VARCHAR2,
P_VAR3 IN OUT VARCHAR2
)
IS --PROCEDURE(RETURN값x), FUNCTION(RETURN값 존재)에서는 변수, 상수선언시 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);
DBMS_OUTPUT.PUT_LINE('');
P_VAR2 := 'B2';
P_VAR3 := 'C2';
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);
DBMS_OUTPUT.PUT_LINE('');
END;
/
