ORACLE 프로시저 생성 및 커서 사용 방법

PL/SQL 정의

• 오라클에서 프로시저 작성을 위해 사용하는 문법
• 다른 데이터베이스에서 사용 불가능

 

프로시저 정의

• 자주 사용하는 구문을 하나의 이름으로 사용
• 함수는 반드시 리턴을 해야하지만 프로시저는 리턴 여부가 상관 없음
• 실제 테이블의 이름이나 구조를 노출하지 않고 사용이 가능함
• 실행 속도가 빠름

 

프로시저 생성

CREATE [OR REPLACE] PROCEDURE 프로시저이름 ( 
    매개변수 [IN/OUT] 자료형, ···
)
IS/AS
--  프로시저 안에서 사용할 변수
BEGIN
--  SQL 쿼리

    [EXCEPTION]
--  ERROR 발생 시 수행하는 문장
END 프로시저이름;
/

⑴ OR REPLACE : 프로시저가 존재하는 경우 지우고 새로 만들기

⑵ IN/OUT : IN은 프로시저를 호출할 때 대입해줘야하는 데이터, OUT은 프로시저를 수행하고 그 결과를 넘겨받기 위한 변수 (생략하면 IN)

 

커서 정의

ORACLE 서버에서 할당된 전용 메모리 영역에 대한 포인터로 암시적 커서와 명시적 커서가 존재함

 

1. 암시적 커서 (Implicit Cursor)

• Oracle DB에서 실행되는 모든 SQL문장은 암시적인 커서가 생성되며 커서 속성을 사용할 수 있음
• 모든 DML과 PL/SQL SELECT문에 대해 선언됨
• Oracle Server에서 SQL문을 처리하기 위해 내부적으로 생성하고 관리함
• SQL문이 실행되는 순간 자동으로 OPEN과 CLOSE를 실행함
• Oracle이나 PL/SQL 실행 메커니즘에 의해 처리되는 SQL 문장이 처리되는 곳에 대한 익명의 주소

 

2. 명시적 커서 (Explicit Cursor)

• 프로그래머에 의해 선언되는 이름이 있는 커서

 

커서 생성

명시적 커서

암시적 커서

CREATE OR REPLACE PROCEDURE SP_NAME IS v_DOCUMENT_ID VARCHAR(20); v_APPROVAL_STATE VARCHAR(1); v_BIZ_CODE VARCHAR(2); v_APPROVAL_NO VARCHAR(2); ​ CURSOR CUR_1 IS SELECT DOCUMENT_ID, APPROVAL_STATE, BIZ_CODE, APPROVAL_NO FROM TABLE_NAME WHERE TABLE_COLUMN = '1'; BEGIN OPEN CUR_1; LOOP FETCH CUR_1 INTO v_DOCUMENT_ID, v_APPROVAL_STATE, v_BIZ_CODE, v_APPROVAL_NO ​ EXIT WHEN CUR_1%NOTFOUND; ​ IF (v_APPROVAL_STATE = 'A') THEN UPDATE TABLE_NAME SET TALBE_COLUMN = '2'; END IF; IF (v_APPROVAL_STATE ='S') THEN UPDATE TABLE_NAME SET TABLE_COLUMN = '3'; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('결재상태:' || v_APPROVAL_STATE); END; /

CREATE OR REPLACE PROCEDURE SP_NAME IS v_DOCUMENT_ID VARCHAR(20); v_APPROVAL_STATE VARCHAR(1); v_BIZ_CODE VARCHAR(2); v_APPROVAL_NO VARCHAR(2); ​ BEGIN ​ FOR CUR_1 IN ( SELECT DOCUMENT_ID, APPROVAL_STATE, BIZ_CODE, APPROVAL_NO FROM TABLE_NAME WHERE TABLE_COLUMN = '1' ) ​ LOOP IF (v_APPROVAL_STATE = 'A') THEN UPDATE TABLE_NAME SET TALBE_COLUMN = '2'; END IF; IF (v_APPROVAL_STATE ='S') THEN UPDATE TABLE_NAME SET TABLE_COLUMN = '3'; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('결재상태:' || v_APPROVAL_STATE); END; / ​

 

커서 속성

⑴ [%ISOPEN] : 커서가 OPEN되어 있으면 TRUE

⑵ [%NOTFOUND] : 패치한 데이터가 행을 반환하지 않으면 TRUE

⑶ [%FOUND] : 패치한 데이터가 행을 반환하면 TRUE

⑷ [%ROWCOUNT] : 현재까지 반환된 모든 데이터 행의 수

 

프로시저와 커서 실습해보기

1. 두 개의 테이블을 생성 (TEST_TABLE, STUDENT)

 

2. 프로시저 생성

CREATE OR REPLACE PROCEDURE TEST    -- TEST 이름의 프로시저 생성
IS
    -- 사용할 변수 설정
    v_S_ID VARCHAR2(20);
    v_S_STATE VARCHAR2(1);
    v_S_NAME VARCHAR2(10);
    v_S_SCORE INTEGER;
    v_S_TEST VARCHAR2(2);

-- 커서 생성
CURSOR CUR_1 IS
    SELECT E.S_ID, E.S_STATE, T.S_NAME, T.S_SCORE, T.S_TEST 
    FROM TEST_TABLE E, STUDENT T
    WHERE E.S_ID = T.S_ID;

BEGIN 
    OPEN CUR_1;   -- 커서 OPEN
    LOOP
        FETCH CUR_1 INTO v_S_ID, v_S_STATE, v_S_NAME, v_S_SCORE, v_S_TEST;
        
    EXIT WHEN CUR_1%NOTFOUND;
    
    -- SCORE가 60 이상이면 S_TEST를 T로 수정
    IF(v_S_SCORE >= 60) THEN
    UPDATE STUDENT
    SET S_TEST = 'T'
    WHERE S_ID = v_S_ID;
    
    END IF;

    -- SCORE가 60 미만이면 S_TEST를 F로 수정    
    IF(v_S_SCORE < 60) THEN
    UPDATE STUDENT
    SET S_TEST = 'F'
    WHERE S_ID = v_S_ID;
    
    END IF;
    END LOOP;
    CLOSE CUR_1;  -- 커서 CLOSE
END;

 

3. 프로시저 실행

 

4. 데이터 비교

S_TEST 컬럼의 데이터가 60점 이상은 T, 60점 미만은 F로 변경된 것을 확인할 수 있습니다.