[Oracle] 프로시저(Procedure) 생성 및 커서(Cursor) 리턴 방법

오라클의 프로시저(Procedure)는 일련의 작업들을 하나로 묶어서 모듈화한 후 필요할 때 마다 호출하여 사용할 수 있는 기능이다. 함수(Function)와 비슷해 보이지만 프로시저는 리턴 값이 없다. (C언어의 void 함수와 비슷)

 

그러나 프로시저에서 레퍼런스 변수를 사용하면 결과 값을 리턴할 수 있다.

 

 

프로시저 생성 예시

부서코드 UPDATE 프로시저 (단순 작업 수행)

CREATE OR REPLACE PROCEDURE pc_update_deptno
(
    p_job    IN VARCHAR2,
    p_deptno IN VARCHAR2
)
IS

BEGIN

    UPDATE emp
       SET deptno = p_deptno
     WHERE job    = p_job;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);

END pc_update_deptno;

 

직군별 부서코드를 업데이트 후 종료되는 프로시저이다. 리턴 값은 없다.

 

CREATE OR REPLACE로 정의한 이유는 생성과 수정을 동시에 사용할 수 있도록 하기 위함이다. 추후 프로시저를 수정 후 컴파일하면 REPLACE 구문이 실행된다.

 

사원 정보 조회 프로시저 (CURSOR 리턴)

CREATE OR REPLACE PROCEDURE pc_select_emp
(
    p_from_hiredate IN  VARCHAR2,
    p_to_hiredate   IN  VARCHAR2, 
    o_cursor        OUT SYS_REFCURSOR	
)
IS

BEGIN

    OPEN o_cursor FOR
        SELECT empno
             , ename
             , job
             , TO_CHAR(hiredate, 'YYYY-MM-DD') AS hiredate
          FROM emp
         WHERE hiredate BETWEEN TO_DATE(p_from_hiredate, 'YYYY-MM-DD')
                            AND TO_DATE(p_to_hiredate, 'YYYY-MM-DD')
         ORDER BY hiredate;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);

END pc_select_emp;

 

사원 테이블을 조회 후 o_cursor 레퍼런스 변수를 통하여 결과 값을 전달할 수 있다.

 

사원 정보 리턴 및 부서코드 UPDATE 동시에 수행

CREATE OR REPLACE PROCEDURE pc_update_deptno2
(
    p_job    IN  VARCHAR2,
    p_deptno IN  VARCHAR2,
    o_cursor OUT SYS_REFCURSOR	
)
IS

BEGIN

    --부서코드 수정 대상자 조회
    BEGIN
        OPEN o_cursor FOR
            SELECT empno
                 , ename
                 , job
                 , deptno
              FROM emp
             WHERE job = p_job;
              
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);              
    END;
	
    --부서코드 수정
    BEGIN
        UPDATE emp
           SET deptno = p_deptno
         WHERE job    = p_job;

    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);
    END;

END pc_update_deptno2;

 

테이블 조회와 부서코드 수정을 동시에 수행하는 프로시저이다.

 

여러 단위의 작업을 순차적으로 수행이 가능하며, 작업 단위별로 BEGIN~END로 묶어서 정의하면 된다.

예외(Exception) 처리는 BEGIN~END 단위로 가능하다.

 

 

업데이트 건수 리턴 (VARCHAR2 리턴)

CREATE OR REPLACE PROCEDURE pc_update_deptno3
(
    p_job        IN  VARCHAR2,
    p_deptno     IN  VARCHAR2,
    o_update_cnt OUT VARCHAR2
)
IS
    v_update_cnt VARCHAR2(1);
BEGIN

    UPDATE emp
       SET deptno = p_deptno
     WHERE job    = p_job;

    v_update_cnt := TO_CHAR(SQL%ROWCOUNT);
    
    o_update_cnt := v_update_cnt;
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);

END pc_update_deptno3;

 

CURSOR 외에 VARCHAR2 변수도 리턴이 가능하다.

 

프로시저 실행 방법

리턴이 없는 프로시저

EXEC pc_update_deptno('MANAGER', '30');

 

CURSOR 리턴 프로시저

--SQL Developer 툴
VAR o_cursor REFCURSOR
EXEC pc_select_emp('1981-01-01', '1981-12-31', :o_cursor)
PRINT o_cursor

SQL Developer

 

--골든 DB툴
VAR o_cursor REFCURSOR
EXEC pc_select_emp('1981-01-01', '1981-12-31', :o_cursor)

Golden