Oracle PL/SQL[프로시저]

함수는 특정 연산을 수행한뒤 결과 값을 반환하지만 프로시저는 특정한 로직을 처리하기만 하고 결과 값을 반환하지는 않는 서브 프로그램이다. 일반적으로 프로젝트 현장에서는 시스템 설계가 끝난후 업무를 분할하고 이 분할한 업무 단위로 로직을 구현해야 하는데 개별적인 단위 업무는 주로 프로시저를 구현해 처리한다. 즉 테이블에서 데이터를 추출해 입맛에 맞게 조작하고 그결과를 다른 테이블에 다시 저장하거나 갱신하는 일련의 처리를 할때 주로 프로시저를 사용한다.

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

CREATE OR REPLACE PROCEDURE : 함수와 마찬가지로 CREATE OR REPLACE 구문을 사용해 프로시저를 생성한다.

매개변수: IN입력 ,OUT은 출력 , IN OUT은 입력과 출력을 동시에 한다는 의미다. 아무것도 명시하지 않으면 디폴트로 IN 매개변수임을 듯한다. OUT 매개변수는 프로시저 내에서 로직 처리 후, 해당 매개변수에 값을 할당해 프러시저 호출 부분에서 이값을  참조할수 있다. 

그리고 IN 매개변수에는 디폴트 값 설정이 가능하다.

 

 

CREATE OR REPLACE PROCEDURE MY_NEW_JOB 
    (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,ADD_DATE,UPDATE_DATE)
  VALUES (P_JOB_ID,P_JOB_TITLE,P_MIN_SAL,P_MAX_SAL,SYSDATE,SYSDATE);
  COMMIT;
END MY_NEW_JOB;

 

p_JOB_ID,  부터 P_MAX_SAL 까지 총 4개의 매개변수를 전달받아 이 값들을 JOBS 테이블에 입력하고 있다. INSER문을 사용하므로 COMMIT  문을 사용해서 최종적으로 DB에 변경사항을 반영하고 있다.

 

프로시저 실행

함수는 반환 값을 받으므로 실행할 때 '호출' 이라고 명명하지만 프로시저는 '호출' 혹은 '실행' 한다고 표현하는데, 실제로는 후자를 많이 사용하는 편이다. 프로시저는 반환 값이 없으므로 함수처럼  SELECT 절에는 사용할 수 없고 다음과 같이 실행해야 한다.

 

EXEC 혹은 EXECUTE 프로시저명 (매개변수1 값, 매개변수 2 값, ..);
EXEC MY_NEW_JOB('TES_JOBS','SAMPLE_JOBS',1000,5000);     
                    

 

오우~

 

매개변수 디폴트 값 설정

 

프로시저를 실행할 때는 반드시 매개변수의 개수에 맞춰 값을 전달해 실행해야 한다. 만약 매개변수 값을 누락하면 다음과 같이 오류가 발생한다.

 

EXECUTE MY_NEW_JOB('TE_JOBS','SAMPLE_JOBS2');

호출시 인수의 갯수나 유형이 잘못되었습니다. 라고 오류가 확인된다.

 

프로시저느의 매개변수에 디폴트 값을 설정하면 실행할 때 해당 매개변수를 전달하지 않더라도 오류가 나지 않고 디폴트로 설정한 값이 해당 매개변수에 적용된다. MY_NEW_JOB 프로시저의 최소, 최대 급여의 디폴트 값을 각각 10과 100으로 값을 설정해보자.

 

CREATE OR REPLACE PROCEDURE MY_NEW_JOB 
    (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
BEGIN
  INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY,ADD_DATE,UPDATE_DATE)
  VALUES (P_JOB_ID,P_JOB_TITLE,P_MIN_SAL,P_MAX_SAL,SYSDATE,SYSDATE);
  COMMIT;
END MY_NEW_JOB;

 

%TYPE 뒤에 10 과 ,100 을 대입해주면 된다. 프로시저를 실행해보자

최소 최대 급여 매개변수를 전달하지 않았어도 디폴트 값을 설정하면 이 값이 매개변수에 할당되어 처리되었음을 알수 있다.

한가지 주의 할점은 디폴트 값은 IN 매개변수에만 사용할수 있다.

 

OUT,IN OUT 매개변수

 

프로시저와 함수의 가장 큰 차이점은 반환 값의 존재여부다. 그런데 프로시저에서도 값을 반환 하는 방법이 있는데 바로 OUT  매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당한다. 그리고 나서 실행이 끝나면 전달한 변수를 참조해 값을 가져올 수 있는 것이다. 프로시저 생성시 매개변수명과 데이터 타입만 명시하면 디폴트 IN 매개변수가 되지만 OUT 매개변수는 반드시 OUT 키워드를 명시해야한다. 

 

CREATE OR REPLACE PROCEDURE MY_NEW_JOB 
    (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_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
  INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY,ADD_DATE,UPDATE_DATE)
  VALUES (P_JOB_ID,P_JOB_TITLE,P_MIN_SAL,P_MAX_SAL,VN_CUR_DATE,VN_CUR_DATE);
  
    ELSE
    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;
            
            P_UPD_DATE := VN_CUR_DATE;
            
            COMMIT;
END MY_NEW_JOB;

 

기존 테이블에는 JOBS 테이블의  ADD_DATE,UPDATE_DATE 컬럼 값으로 SYSDATE 직접 입력했지만 이번에는 VN_CUR_DATE 변수를 선언해 SYSDATE로 초기화한 뒤, 이 변수를 사용했다. 이렇게 처리한 이유는 SYSDATE는 초단위로 값이 바뀌므로 JOBS 테이블에 입력되거나 갱신된 일자 값을 정확히 가져오기 위해서이다. 이제 프로시저를 실행하고  OUT 매개변수 값을 참조해야 하는데, 이때 별도의 변수를 선언해서 매개변수로 전달한 뒤 값을 참조해야 한다. 변수가 필요하므로 이 프로시저를 실행하는 익명블록을 만들어보자.

DECLARE
    VN_CUR_DATE JOBS.UPDATE_DATE%TYPE;
    
    BEGIN
        MY_NEW_JOB('TEST_JOB1','SAMPLE_JOB3',2000,6000, VN_CUR_DATE);
        DBMS_OUTPUT.PUT_LINE(VN_CUR_DATE);
        END;
        /

 

OUT 매개변수로 전달한 VN_CUR_DATE 변수에 값이 할당됐음을 알수 있다.

 

IN OUT 매개변수가 있는데, 이렇게 선언하면 입력과 동시에 출력 용으로  사용할수 있다. 여기서 한가지 짚고 넘어갈 점이 있는데, 프로시저 실행시 OUT 매개변수에 전달할 변수에 값을 할당해서 넘겨줄 수 있지만, 큰의미는 없다.  OUT 매개변수는 해당 프로시저가 성공적으로 실행을 완료할 때 까지 값이 할당되지 않는다. 따라서 매개변수에 값을 전달해서 사용한 다음 다시 이 매개변수에 값을 받아와 참조하고 싶다면 IN OUT 매개변수에 값을 전달해서 사용한 다음 다시 이 매개변수에 값을 받아와 참조하고 싶다면 IN OUT 매개변수를 사용해야 한다.

 

CREATE OR REPLACE PROCEDURE PARAMETER_TEST (
    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 PARAMETER_TEST;

프로시저를 실행해보자 

DECLARE
    V_VAR1 VARCHAR2(10) := 'A';
    V_VAR2 VARCHAR2(10) := 'B';
    V_VAR3 VARCHAR2(10) := 'C';
    
    BEGIN
        PARAMETER_TEST (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_VAR 로 시작되는 결과는 PARAMETER_TEST 내부에서 출력한것이고ㅓ V_VAR 로 시작되는 결과는 프로시저를 실행한 익명 블록에서 출력한것이다. OUT 매개변수인 P_VAR2 자리에 V_VAR2 변수를 넣어 'B' 라는 값을 넘겨 줬음에도 불구하고 아무런 값도 없음을 확인할수 있다. 이에반해  IN OUT 매개변수인 P_VAR3 에는 'C' 란 값을 넘겨 줘서 PARAMETER_TEST 내부에서 이 값을 받아 출력했고 다시 'C2' 로 값을 할당해서 최종적으로 V_VAR3 값은 'C2' 가된다. 

 

좀더 이해하기 쉽게 여러가지로 테스트를 진행 해봤다. 

 

CREATE OR REPLACE PROCEDURE PARAMETER_TEST (
    P_VAR1 VARCHAR2,
    P_VAR2 OUT VARCHAR2,
    P_VAR3 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 PARAMETER_TEST;

 

 

 

CREATE OR REPLACE PROCEDURE PARAMETER_TEST (
    P_VAR1 VARCHAR2,
    P_VAR2 VARCHAR2,
    P_VAR3 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 PARAMETER_TEST;

 

 

 

 

CREATE OR REPLACE PROCEDURE PARAMETER_TEST (
    P_VAR1 IN VARCHAR2,
    P_VAR2 IN VARCHAR2,
    P_VAR3 IN 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 PARAMETER_TEST;
////////////////////////////////////////

CREATE OR REPLACE PROCEDURE PARAMETER_TEST (
    P_VAR1 IN VARCHAR2,
    P_VAR2 IN 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 PARAMETER_TEST;

 

 

 

CREATE OR REPLACE PROCEDURE PARAMETER_TEST (
    P_VAR1 IN VARCHAR2,
    P_VAR2 IN 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 PARAMETER_TEST;

 

 

 

 

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

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

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

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