[Oracle] 프로시저(Procedure), 커서(Cursor)

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

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

 

커서(Cursor)란 특정 SQL 문장을 처리한 결과를 담고 있는 영역을 가리키는 일종의 포인터로, 커서를 사용하면 처리된 SQL 문장의 결과 집합에 접근할 수 있다.

 

- 구조

CREATE [OR REPLACE] PROCEDURE 프로시저명

(

      파라미터 [IN | OUT | IN OUT] 데이터 타입

)

[IS | AS]

      변수 선언

BEGIN

      프로시저 작업 수행

EXCEPTION

      예외처리

END 프로시저명;

 

 

1. 사원 정보 리턴 및 부서코드 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;

o_cursor 레퍼런스 변수를 통해 결과 값을 전달할 수 있다.

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

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

 

2. 업데이트 건수 리턴 (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;

커서 외에 선언해둔 VARCHAR2 변수도 리턴이 가능하다.

 

 

- 프로시저 실행 방법

1. 리턴이 없는 프로시저

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

 

2. CURSOR 리턴 프로시저

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