OraclePL/SQL [커서 관리]

커서는 SELECT 또는 DML 등의 SQL처리를 위한 데이터를 저장하고 있는 오라클이 관리하는 메모리에 대한 일종의 포인터다.

커서는 PGA라 불리는 오라클 메모리 공간에 할당되는데, SQ이 수행되면 PGA 영역에 소트 영역과 세션정보, 커서 상태, 스택 공간 등

SQL 을 수행하기 위해 필요한 공간을 할당받게 된다. 또한 이 영역은 실제 SQL을 수행할 수 있도록 한다. 커서를 실행하기 위한 이런 준비과정을 '커서를 오픈한다' 라고 표현한다.

명시적 커서와 묵시적 커서

커서는 사용 방법에 따라 명시적(Explicit) 방식과 묵시적(Implicit) 방식, 두가지 방식으로 나뉜다.

CURSOR 키워드를 이용하여 커서를 명시적으로 선언하면 명시적 커서라 부르고 , 선언없이 SQL을 직접 사용하면 묵시적 커서가 된다.

DECLARE
-- 명시적(Explicit) 커서 선
    CURSOR EMP_CUR IS
    SELECT EMONO FROM EMP;
    
    --묵시적 (Implicit) 커서 선언
    BEGIN
        SELECT EMPNO
        INTO MY_EMP
        FROM EMP;
        
        END;
        /언

묵시적 커서

묵시적 커서는 DML(INSERT/UPDATE/DELETE) 구문 또는 SELECT 문을 수행할 경우 명시적으로 커서를 선언하지 않아도 커서의

OPEN,FETHCH,CLOSE를 오라클이 자동으로 수행하게 된다.

묵시적 커서 속성

SQL 커서 속성을 활용하여 SQL실행 결과에 대해 확인이 가능하다.

SQL%ROWCOUNT의 경우 커밋(Commit) 또는 롤백(Rollback) 수행 이후에는 0으로 초기화 된다.

커서 속성	설명
SQL%NOTFOUND	SQL실행시 Data가 없을 경우 True를 반환
SQL%FOUND	SQL 실행 시 Datark 1건 이상 존재할 경우 Truc를 반환
SQL%ROWCOUNT	SQL 실행 시 영향받은 Row의 수
SQL%ISOPEN	묵시적 커서 사용 시에는 항상 False를 반환

DECLARE
    I_EMPNO EMP.EMPNO%TYPE;
    
    BEGIN
        --SELECT 시 묵시적 커서 사용
        --SQL 의 OPEN/FETCH/COLSE가 자동으로 수행됨   
        SELECT EMPNO
        INTO I_EMPNO
        FROM EMP
        WHERE ROWNUM = 1;
        
        --DML시 묵시적 커서 사용
        UPDATE EMP
        SET SAL = SAL * 1.1
        WHERE EMPNO = 9000;
        
        --수행건수가 존재하는지 여부확인
        IF SQL%FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Update succeeded');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Update failed');
        END IF;
        
        INSERT INTO EMP
        VALUES(9000,'TEST','CLERK',7934,SYSDATE,1000,0,10);
         DBMS_OUTPUT.PUT_LINE('Update succeeded : ' || I_EMPNO );
        DELETE FROM EMP
        WHERE EMPNO = 9000;
        
        --최종 수행된 SQL문의 결과 건수만 반영
        DBMS_OUTPUT.PUT_LINE('Number of Rows Deleted : ' || SQL%ROWCOUNT);
        
        ROLLBACK;
        --COMMIT이나 RollBACK 수행 후 %ROWCOUNT 초기화
        DBMS_OUTPUT.PUT_LINE('After commit or rollback: ' || SQL%ROWCOUNT);
        END;

위구문을 살펴보자 일단 SELECT문을 먼저 보면 EMP 테이블의 ROWNUM = 1의 EMPNO 를 출력하는거다

ROWNUM = 1 의데이터가 무엇일까? 한번보자

SELECT * FROM EMP;

하면 ROWNUM = 1 번의 EMPNO 는 7369 인걸 확인할수 있다.

그리고 UPDATE문은 9000번의 empno가 있으면 현재 급여값의 1.1 을 곱하는 구문이다 현재 9000번이 없기 때문에 IF 문이 실행되지 않는다

IF SQL%FOUND 는 데이터값이 있다면 DBMS 를 이용하여 출력 할텐데 현재는 데이터가 없기 때문에 안된다 .

그러면 NOTFOUND 로 하면 어떻게 될까?

DECLARE
    I_EMPNO EMP.EMPNO%TYPE;
    
    BEGIN
        --SELECT 시 묵시적 커서 사용
        --SQL 의 OPEN/FETCH/COLSE가 자동으로 수행됨   
        SELECT EMPNO
        INTO I_EMPNO
        FROM EMP
        WHERE ROWNUM = 1;
        
        --DML시 묵시적 커서 사용
        UPDATE EMP
        SET SAL = SAL * 1.1
        WHERE EMPNO = 9000;
        
        --수행건수가 존재하는지 여부확인
        IF SQL%NOTFOUND THEN
            DBMS_OUTPUT.PUT_LINE('Update succeeded');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Update failed');
        END IF;
        
        INSERT INTO EMP
        VALUES(9000,'TEST','CLERK',7934,SYSDATE,1000,0,10);
         DBMS_OUTPUT.PUT_LINE('Update succeeded : ' || I_EMPNO );
        DELETE FROM EMP
        WHERE EMPNO = 9000;
        
        --최종 수행된 SQL문의 결과 건수만 반영
        DBMS_OUTPUT.PUT_LINE('Number of Rows Deleted : ' || SQL%ROWCOUNT);
        
        ROLLBACK;
        --COMMIT이나 RollBACK 수행 후 %ROWCOUNT 초기화
        DBMS_OUTPUT.PUT_LINE('After commit or rollback: ' || SQL%ROWCOUNT);
        END;

IF 구문이 실행된다 그리고 INSERT 구문을 실행하여 9000번인 사원을 등록 한다 여기서 DML문을 수행 시에는 SQL%FOUND 속성을 활용하여 반영된 건수가 있는지 확인이 가능하다. 또한 커서 속성은 바로 직전에 실행된 SQL 결과만 저장되기 때문에 위의 코드를 보면 INSERT가 아닌 DELETE 문에서 삭제된 건수만 SQL%ROWCOUNT 에 저장되며 , 커밋 또는 롤백이 수행되고 나면 0으로 초기화 된다.

명시적 커서

명시적 커서는 프로그램 내에 명시적으로 커서를 선언한 경우를 의미하며 , 사용자가 커서를 직접 Open, Fetch, Close 해야합니다.

아래는 커서의 생성 시 부터 닫을 때 까지 처리 절차를 보여주고 있다.

DECLARE : 사용자가 설정한 이름으로 커서를 선언하여 수행될 쿼리의 구조를 정의합니다.

OPEN : 쿼리를 실행하고 변수를 바인드 시킨다.이때 쿼리에 의해 영향받은 로우들을 Active set 이라 부르며 Fetch될 준비가 된다.

반환할 로우가 없다면 예외가 발생한다.

FETCH: 커서로부터 데이터를 Fetch하여 변수에 저장한다. 더 이상 추출할 로우가 없다면 커러슬 close 해야 한다

CLOSE: Active set 을 해제한다. 커서를 CLOSE 한 이후에는 필요하다면 커서를 재오픈 하여 사용해야 한다.

CREATE OR REPLACE FUNCTION GET_ENAME_FUNC (P_EMPNO IN NUMBER)
RETURN VARCHAR2
IS
    --명시적 커서선언
    CURSOR EMP_CUR IS
    SELECT ENAME FROM EMP
    WHERE EMPNO = P_EMPNO;
    --추출 칼럼과 같은 타입의 변수선언
    I_ENAME EMP.ENAME%TYPE;
    
    BEGIN
    --커서오픈
        OPEN EMP_CUR;
        --데이터 추출 후 INTO 절 안의 변수에 입력
        FETCH EMP_CUR INTO I_ENAME;
        --추출 결과 건수가 0일 경우
        IF EMP_CUR%NOTFOUND
        THEN
        I_ENAME := NULL;
        END IF;
        
    CLOSE EMP_CUR;
    
    RETURN I_ENAME;
END;

SELECT GET_ENAME_FUNC(E.EMPNO) FROM EMP E WHERE EMPNO = 7943;

위 방식은 SELECT 절에서 오직 한건의 데이터만 반환할 경우에 사용할 수 있습니다. 여러 건이 반환될 수 있는 경우에는 Loop 구문이 추가되어야 한다.

LOOP를 이용하는 방법은 다음과 같다

OPEN , LOOP , FETCH, CLOSE 를 사용
명시적 커서를 이용한 FOR LOOP를 이용
묵시적 커서를 이용한 FOR LOOP를 이용

OPEN , LOOP , FETCH, CLOSE를 이용할 경우에는 LOOP와 함께 "EXIT WHERE Cursor%NOTFOUN" 를 추가하여 마지막 로우까지 반복 추출될 수 있도록 해야한다.

명시적 커서를 이용한 방법은 커서를 선언한 후 FOR LOOP 안에서 선언한 커서를 사용하는 방법으로 커서 안에 매개변수 값을 전달하여 SQL 이 재활용될 수 있도록 할수 있다.

DECLARE
    --명시적 커서 선언(매개변수 전달)
     CURSOR EMP_CUR(P_DEPTNO NUMBER,
                    P_JOB VARCHAR2)
                    
IS
    SELECT * 
    FROM EMP
    WHERE DEPTNO = P_DEPTNO
    AND JOB = P_JOB;
    
    --커서 EMP_CUR과 같은 레코드 타입 선언
    EMP_REC EMP_CUR%ROWTYPE;
    
    BEGIN
        --방법1 커서 오픈 OPEN,LOOP,FETCH,CLOSE 를 이용한 데이터 추출
        --커서 오픈(매개변수 전달)  
        OPEN EMP_CUR(30,'SALESMEN');
        --커서 내 데이터 반복 추출
            LOOP
            --커서 결과를 EMP_CUR%ROWTYPE변수에 입력
            FETCH EMP_CUR INTO EMP_REC;
            
            --커서 내 추출 데이터가 없을 경우 LOOP빠져나감
            EXIT WHEN EMP_CUR%NOTFOUND;    
          --데이터 처리
            DBMS_OUTPUT.PUT_LINE('Name = ' || EMP_REC.ENAME || ' SALARY = ' || EMP_REC.SAL || ' JOB_ID = ' || EMP_REC.JOB);
          END LOOP;
          
          CLOSE EMP_CUR;
          
          END;

위구문은 OPEN, LOOP , FETCH, CLOSE를 이용하여 커서를 사용한 예이다

커서를 오픈하여 패치한 다음 %NOTFOUND 속성을 활용하여 추출할 건수가 있는지 확인해야 하며 , 작업이 완료된 후에는 반드시 커서를 CLOSE 시켜야 한다. 또한 커서의 결과를 저정하기 위해 %ROWTYPE 함수를 이용하여 EMP_REC 식별자를 커서의 결과와 동일한 레코드 타입으로 저장되도록 했다.

DECLARE 
    CURSOR EMP_CUR(P_DEPTNO NUMBER,
                    P_JOB VARCHAR2)
                    
IS
    SELECT * 
    FROM EMP
    WHERE DEPTNO = P_DEPTNO
    AND JOB = P_JOB;
    
BEGIN
    --방법 2 명시적 커서와 FORLOOP를 이용한 데이터 추출
    --IN절 뒤에 커서와 함께 매개변수 전달
    
    FOR EMP_REC IN EMP_CUR(30,'CLERK') LOOP
    DBMS_OUTPUT.PUT_LINE('Name = ' || EMP_REC.ENAME || ' SALARY = ' || EMP_REC.SAL || ' JOB_ID = ' || EMP_REC.JOB);
    END LOOP;
    
    END;

명시적 커서와 함께 FOR LOOP 를 이용한 예이다 선언된 커서를 FOR LOOP의 IN절에 사용하여 , 오라클이 커서를 자동으로 OPEN,FETCH,한 후 모든 결과를 출력하고 CLOSE 시킨다. 또한 커서의 결과를 저정하기 위해 별도의 타입을 생성하지 않아도

$ROWTYPE 을 묵시적으로 이요하여 EMP_REC 레코드 변수를 생성한다.

DECLARE 
  BEGIN
      --방법 3 묵시적 커서와 FORLOOP를 이용한 데이터 추출
      FOR EMP_REC IN 
      (SELECT ENAME ,JOB, SAL FROM EMP WHERE DEPTNO = 10)
      LOOP
      DBMS_OUTPUT.PUT_LINE('Name = ' || EMP_REC.ENAME || ' SALARY = ' || EMP_REC.SAL || ' JOB_ID = ' || EMP_REC.JOB);
      END LOOP;

  END;

별도의 커서 선언 없이 FOR LOOP 안에 SQL 을 직접 입력하는 방법이다

명시적 커서를 사용할 겨우 커서 속성을 활용하여 커서 결과에 대해 확인이 가능하다. 제공되는 커서 속성은 아래와 같다.

커서 속성	설명
CURSOR%NOTFOUND	커서 내 더 이상 데이터가 없을 경우 True를 반환
CURSOR%FOUND	데이터가 존재할 경우 True를 반환
CURSOR%ROWCOUNT	커서 실행 직후의 Rowcount로 Open 전에는 0을 반환
CURSOR%ISOPEN	커서 오픈 여부를 확인하여 Open 되어 있으며 True를 반환

DECLARE
    --dept 테이블 관련 커서선언
    --커서 안에 p_deptno 변수를 이용해 매개변수를 전달함
    
    CURSOR EMP_CUR(P_DEPTNO NUMBER)
    IS SELECT * 
        FROM DEPT 
        WHERE DEPTNO > P_DEPTNO;
    --커서에서 반환되는 레코드 타입 변수 선언
    DEPT_REC EMP_CUR%ROWTYPE;
    
    BEGIN
        --테스트(커서 ROWTYPE 변수를 이용한 OPEN,LOOP,FETCH,CLOSE 를 활용한 데이터 추출)
        DBMS_OUTPUT.PUT_LINE('<TEST #1 - OPEN CURSOR>');
        
        --매개변수와 함께 커서 오픈
        OPEN EMP_CUR(0);
        
        LOOP
            --EMP_CUR 를 FETCH 하여 DEPT_REC 커서 변수에 입력
            FETCH EMP_CUR INTO DEPT_REC;
            --Cursor내 data 가 존재하는지 확인후 없으면 빠저나감
            EXIT WHEN EMP_CUR%NOTFOUND;
            
            --직전 실행된 수행 건수
            DBMS_OUTPUT.PUT_LINE(EMP_CUR%ROWCOUNT || 'throw, deptno -> ' || DEPT_REC.DEPTNO);
        END LOOP;
       --커서 결과 건수 확인
       DBMS_OUTPUT.PUT_LINE('Total rows: ' || EMP_CUR%ROWCOUNT);
       
       --커서CLOSE
       CLOSE EMP_CUR;
       
       END;

EMP_CUR%NOTFOUND는 커서 내 Fetch되는 데이터가 없을 경우 True가 되면서 EXIT가 수행되며, %ROWCOUNT는 LOOP가 돌면서 건수가 누적되는 것을 확인할 수 있습니다.

DECLARE
    --dept 테이블 관련 커서선언
    --커서 안에 p_deptno 변수를 이용해 매개변수를 전달함
    
    CURSOR EMP_CUR(P_DEPTNO NUMBER)
    IS SELECT * 
        FROM DEPT 
        WHERE DEPTNO > P_DEPTNO;
   
    BEGIN
        --테스트(FOR LOOP와 명시적 커서를 이용한 데이터 추출)
        DBMS_OUTPUT.PUT_LINE('<TEST #2 - OPEN CURSOR>');
        
        --FOR LOOP 사용 시 자동으로 커서를 OPEN - FETCH - CLOSE 시킴
        FOR DEPT_REC IN EMP_CUR(20)
        LOOP
            DBMS_OUTPUT.PUT_LINE(EMP_CUR%ROWCOUNT || ' THROW,DEPTNO -> ' || DEPT_REC.DEPTNO);
        END LOOP;
        
        --커서가 자동으로 닫히므로 아래 주석 제거 시 에러가 발생함
        -- DBMS_OUTPUT.PUT_LINE('Total rows : ' || EMP_CUR%ROWCOUNT);
       
       END;

FOR LOOP 문은 커서를 자동으로 OPEN-FETCH-CLOSE 하기 때문에 NOTFOUND 속성이 없다 루프가 종료 되면서 커서가 자동으로 CLOSE 되기 때문에 LOOP 박에서 %ROWCOUNT 속성을 사용할 경우 에러가 발생한다.

DECLARE
    --조회 대상 데이터에 대해 락을 경유하며 커서 오픈
   CURSOR EMP_CUR IS SELECT * FROM EMP FOR UPDATE;
   
   --락이 걸린 데이터에 대해 커서 오픈 요청 시 바로 에러 출력
   --CURSOR EMP_CUR IS SELECT * FROM EMP UPDATE NOWAIT;
   
   --락이 걸린 데이터에 대해 커거 오픈 요청시 3초 후에도 락 대기 시 에러 출력
   --CURSOR EMP_CUR SELELCT * FROM EMP UPDATE WAIT 3;
 
   BEGIN
   --CURRENT OF를 이용하여 커서에 읽어들인 로우의 rowid를 활용하여 성능 개선
   --기본 키 인덱스를 이용하는 것보다도 성능이 우월함
    FOR REC IN EMP_CUR 
    LOOP
        UPDATE EMP
        SET SAL = SAL +0 
        WHERE CURRENT OF EMP_CUR;
        
        END LOOP;
    COMMIT;
END;

CURRENT OF CURSOR 를 이용한 방법을 통해서 성능 개선이 가능하다

FOR UPDATE 의 경우 락이 걸린 데이터에 대해 다시 락을 요청하면 기본적으로 락이 풀릴 대 까지 기다리게 되는데, NOWAIT/WAIT 문을 추가하여 바로 에러를 발생시키거나 일정 시간동안 대기 한후 락을 재점유 하도록 할수 있다.

DECLARE
 --커서 추출시 ROWID 함께 추출
 CURSOR EMP_CUR 
 IS SELECT * FROM EMP FOR UPDATE;
            MY_ROWID ROWID;
 
 BEGIN
    --ROWID를 이용하여 변경하려는 로우 확인
        FOR EMP_REC IN EMP_CUR
        LOOP MY_ROWID : = EMP_REC.ROWID;
        
        UPDATE EMP
        SET SAL = SAL + 0
        WHERE ROWID = REC.ROWID;
        
        END LOOP;
        
        COMMIT;
        
        END;
/

'DB' 카테고리의 다른 글

Oracle PL/SQL REF 커서 (0)	2021.01.06
Oracle PL/SQL 자율 트랜잭션 (0)	2021.01.05
Oracle PL/SQL [제어 구문] (0)	2021.01.02
Oracle PL/SQL 프로시저[패키지] (0)	2021.01.02
Oracle SQL(서브쿼리) (0)	2020.12.31

안졸리나졸리나코딩

OraclePL/SQL [커서 관리]

묵시적 커서 속성

명시적 커서

'DB' 카테고리의 다른 글

티스토리툴바

OraclePL/SQL [커서 관리]

묵시적 커서 속성

명시적 커서

'DB' 카테고리의 다른 글

'DB' Related Articles

티스토리툴바