실무에 사용 되는 오라클 커서 (CURSOR) 사용법

1) 정의

  - 커서를 짧고 간략하게 설명 하면 "SQL문을 처리한 결과 집합을 가리키는 포인터이다."

   이걸 약간 더 길게 설명 하면 SQL 처리 결과 를 오라클 서버 프로세스 내부의 Private SQL Area 라는 

  메모리 영역에 저장되는데 클라이언트 프로세스에서  포인터(커서)라 는 것이 가리키고 있다.

 

아래 두줄은 같은말 ``

  - 질의의 결과로 얻어진 여러 행이 저장된 메모리상의 위치.
  - 커서는 SELECT 문의 결과 집합을 처리하는데 사용된다.

 

 

아래 그림은 위에 설명을 한 장의 이미지로 설명했다.
 

출처:https://bit.ly/2u5Cmvh

 

커서의 정의에 대해서 알아 보았으니 종류에 대해서 알아 보자. 종류로는 암시적, 명시적 커서로 나뉜다.

2) 암시적 커서

  : 오라클 내부에서 PL_SQL(INSERT, UPDATE, DELETE 등등.) 실행시 자동으로 생성 되어 사용 되어진다.
이러한 커서를 아래와 같이 사용 할 수 있다.

함수로 치면 내장 함수라고 생각 하면 쉽게 이해 될듯 하다.

속성
 - SQL%FOUND     : 해당 SQL문에 의해 반환된 총 행수가 1개 이상일 경우TRUE (BOOLEAN)

 - SQL%NOTFOUND : 해당 SQL문에 의해 반환된 총 행수가 없을 경우 TRUE (BOOLEAN)

 - SQL%ISOPEN     : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색( PL/SQL은 실행 후 바로 묵시적 커서를 닫기 때문에 항상 상 false)

 - SQL%ROWCOUNT : 해당 SQL문에 의해 반환된 총 행수, 가장 최근 수행된 SQL문에 의해 영향을 받은 행의 갯수(정수)

3) 명시적 커서 

: 사용자가 직접 정의해서 사용 하는 커서를 말한다. 

함수로 치면 외장 함수 쯤 된다. 개발자가 필요할때 새로 정의해서 사용 하는 커서

 

코드 작성법은 여러 방법이 있지만 아래 커서는 코딩언어 로 치면 지역변수 커서에 해당 한다.

해당 블록에 커서를 생성해서 지정된 코드블럭을 넘어 서게 되면 소멸 한다.

DECLARE 
BEGIN 
    FOR ID_LIST IN  
    ( 
        SELECT 'GOD' AS USER_ID FROM DUAL 
    ) 
    LOOP 
    DBMS_OUTPUT.putline(ID_LIST.USER_ID); 
END LOOP; 
END; 

FOR LOOP가 자동적으로 커서를 OPEN해주며, 행이 없을 때까지 FETCH해주고, CLOSE해준다.
또한 ROWTYPE에 해당하는 변수를 따로 DECLARE할 필요가 없다. 
이는 암시적으로 선언되기 때문이다.

"지역 변수가 아닌 전연 변수를 선언 할때"

 

 - 다른 블록에서도 사용이 가능하고 얼마든지 재사용이 가능하다
 - 한개 이상의 쿼리를 연결 해서 사용 할때
 -변수처럼 커서 변수를 함수나 프로시저의 매개변수로 전달할 수 있다.
 - 커서 속성을 사용 할 수 있다.

 

이 포인터(커서) 종류로는 강한 타입과 약한 타입의 커서 이고 언어코딩으로 설명하자면
int, string 을 명시적으로 정해서 입력해줘야 하는 타입이 "강한 타입" 이고
var 형태의 무기명 함수 처럼 형이 형을 지정 해주지 않아도 되는 타입이 "약한 타입" 이다.

 

강한타입 커서 사용법은 아래와 같고 자세한건 생략 하기로 한다. 
이유는 아래 방식이 더 간단 하므로.

TYPE dep_curtype IS REF CURSOR RETURN department%ROWTYPE;
TYPE dep_curtype IS REF CURSOR;  

3-1) 약한 타입인  SYS_REFCURSOR (빌트인 타입 이라고 불리는) 사용 법을 알아보자

  DECLARE 
      -- 사원명을 받아오기 위한 변수 선언 
      vs_emp_name employees.emp_name%TYPE; 

      -- SYS_REFCURSOR 타입의 커서 변수 선언 
      emp_dep_curvar SYS_REFCURSOR; 
    BEGIN 

      -- 커서 변수를 사용한 커서 정의 및 오픈 
      OPEN emp_dep_curvar FOR SELECT emp_name 
                               FROM employees 
                              WHERE department_id = 90; 

      -- LOOP문 
      LOOP 
        -- 커서 변수를 사용해 결과 집합을 vs_emp_name 변수에 할당 
        FETCH emp_dep_curvar INTO vs_emp_name; 

        -- 더 이상 패치된 참조 로우가 없으면 LOOP 탈출(커서 변수를 이용한 커서 속성 참조) 
        EXIT WHEN emp_dep_curvar%NOTFOUND; 

        -- 사원명을 출력 
        DBMS_OUTPUT.PUT_LINE(vs_emp_name); 

      END LOOP; 
    END; 

간단하고 실무에서 유용 하게 쓰이는 커서 사용 법에 대해 간략히 알아 보았다.

참고 사이트
https://goddaehee.tistory.com/117
https://gdtbgl93.tistory.com/152

[Oracle] PL/SQL 기초3 - 커서

[PL/SQL] 커서 (Cursor)