[PL/SQL] 프로시저(PROCEDURE)

수정 이력

2022.08.20   song님 댓글로 아래 내용 제거(특정한 로직을 처리하기만 하고 결과 값을 반환하지 않는 서브 프로그램이다.)

프로시저(PROCEDURE)

특정한 로직을 처리하기만 하고 결과 값을 반환하지 않는 서브 프로그램이다.

테이블에서 데이터를 추출해 조작하고 그 결과를 다른 테이블에 다시 저장하거나 갱신하는 일련의 처리를 할 때 주로 프로시저를 사용한다.

 

생성

CREATE OR REPLACE PROCEDURE 프로시저명
    (매개변수명1 [IN | OUT | IN OUT] 데이터타입 [:= 디폴트값]
    ,매개변수명2 [IN | OUT | IN OUT] 데이터타입 [:= 디폴트값]
    ...
    )
IS[AS]
    변수, 상수 등 선언
BEGIN
    실행부
    
[EXCEPTION
    예외처리부]
END [프로시저명];

매개변수

IN은 입력, OUT은 출력, IN OUT은 입출력을 동시에 한다는 의미이다.

디폴트값은 IN이다.

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,
    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절에는 사용할 수 없다.

EXEC(EXECUTE) 프로시저명(매개변수1값, 매개변수2값, ...);

 

EXEC MY_NEW_JOB_PROC('SM_JOB1', 'SAMPLE JOB1', 1000, 5000);

 

 

SELECT  *
FROM    JOBS
WHERE   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
 
    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;
    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);
    END IF;
    
    COMMIT;
END;

 

 

EXEC MY_NEW_JOB_PROC('SM_JOB1', 'SAMPLE JOB1', 2000, 6000);

 

 

SELECT  *
FROM    JOBS
WHERE   JOB_ID = 'SM_JOB1';

 

 

 

매개변수

입력값 매핑

프로시저의 매개변수가 많으면 실행할 때 매개변수 값의 개수나 순서를 혼동할 여지가 많다.

이런 경우에는 다음과 같은 형태로 매개변수와 입력값을 매핑해 실행하면 편리하다.

 

EXEC 프로시저명 (매개변수1 => 매개변수1 값,
                매개변수2 => 매개변수2 값, ...);

 

EXECUTE MY_NEW_JOB_PROC (P_JOB_ID   =>  'SM_JOB1',
                         P_JOB_TITLE=>  'SAMPLE JOB1',
                         P_MIN_SAL  =>  2000,
                         P_MAX_SAL  =>  7000            );

디폴트값 설정

매개변수에 디폴트 값을 설정하면 프로시저 실행시 해당 매개변수에 값을 넣지 않아도된다.

디폴트값은 IN 매개변수에만 사용할 수 있다.

 

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   )
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;
    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);
    END IF;
    
    COMMIT;
END;
 
EXEC MY_NEW_JOB_PROC('SM_JOB1', 'SAMPLE JOB1');
 
SELECT  *
FROM    JOBS
WHERE   JOB_ID = 'SM_JOB1';

 

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;
    
    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);
    END IF;
    
    P_UPDATE_DATE := VN_CUR_DATE;
    
    COMMIT;
END;
 
DECLARE
    VD_CUR_DATE JOBS.UPDATE_DATE%TYPE;
BEGIN
    MY_NEW_JOB_PROC ('SM_JOB1', 'SAMPLE JOB1', 2000, 6000, VD_CUR_DATE);
    
    DBMS_OUTPUT.PUT_LINE(VD_CUR_DATE);
END;
 

 

IN OUT 매개변수

입력과 동시에 출력용으로 사용할 수 있다.

프로시저 실행시 OUT 매개변수에 전달할 변수에 값을 할당해서 넘겨줄 수 있지만 의미없다.

왜냐하면 OUT 매개변수는 프로시저가 성공적으로 실행을 완료할 때까지 값이 할당되지 않기때문이다.

그러므로 매개변수에 값을 전달해서 사용한 다음 다시 이 매개변수에 값을 받아와 참조하고싶다면 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;

 

키포인트

 IN 매개변수는 참조만 가능하면 값을 할당할 수 없다

 OUT 매개변수에 값을 전달할 수는 있지만 의미는 없다.

 OUT, IN OUT 매개변수에는 디폴트값을 설정할수 없다.

 IN 매개변수에는 변수나 상수, 각 데이터 유형에 따른 값을 전달할 수 있지만 OUT, IN OUT 매개변수를 전달할 때는 반드리 변수 형태로 값을 넘겨줘야한다.

 

 

RETURN문

함수에서는 일정한 연상을 수행하고 결과 값을 반환하는 역할을 했지만, 프로시저에서는 RETURN문을 만나면 이후 로직을 수행하지 않고 프로시저를 빠져나간다.

 

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     )
IS
    VN_CNT  NUMBER := 0;
BEGIN
    IF  P_MIN_SAL < 1000 THEN
        DBMS_OUTPUT.PUT_LINE('최소 급여값은 1000 이상이어야한다.');
        RETURN;
    END IF;
 
    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;
    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);
    END IF;
    
    COMMIT;
END;
 
EXEC MY_NEW_JOB_PROC('SM_JOB1', 'SAMPLE JOB1', 999, 6000);