PL/SQL - 커서(CORSOR)

https://goddaehee.tistory.com/117

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

CORSOR (커서)

- SQL커서는 Oracle서버에서 할당한 정용 메모리 영역에 대한 포인터이다.

- 질의의 결과로 얻어진 여러 행이 저장된 메모리상에 위치

- 커서는 select 문의 결과 집합을 처리하는데 사용

 

CORSOR 의 종류

1. 암시적 커서 (Implicit Cursor) / 묵시적 커서

1.1 정의

- 오라클 DB에서 실행되는 모든 SQL문장은 암시적인 커서가 생성되며, 커서 속성을 사용할 수 있다.

- 모든 DML(Data Manipulation Language, 조작어, ex.select/insert/update/delete)과 PL/SQL SELECT문에 대해 선언됨.

- 암시적 커서는 오라클이나 PL/SQL실행 메커니즘에 의해 처리되는 SQL문장이 처리되는 곳에 대한 익명의 주소이다.

- Orcale서버에서 SQL문을 처리하기 위해 내부적으로 생성하고 관리한다.

- 암시적 커서는 SQL문이 실행되는 순간 자동적으로 OPEN과 CLOSE를 실행한다.

- SQL커서 속성을 사용하면 SQL문의 결과를 테스트할 수 있다.

1.2 속성

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

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

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

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

 

2. 명시적 커서 (Explicit Cursor)

2.1 정의

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

2.2 명시적 커서 속성

- %ROWCOUNT : 현재까지 반환된 모든 행의 수를 출력

- %FOUND : FETCH한 데이터가 행을 반환하면 TRUE

- %NOTFOUND : FETCH한 데이터가 행을 반환하지 않으면 TRUE(LOOP를 종료할 시점을 찾는다.)

- %ISOPEN : 커서가 OPEN되어 있으면 TRUE

2.3 문법

- DECLARE를 통해서 명명된 SQL영역을 생성

- OPEN을 이용해 결과 행 집합을 식별

  OPEN(커서열기)

  · OPEN문을 사용해서 커서를 연다

  · 커서 안의 검색이 실행되며 아무런 데이터행을 추출하지 못해도 에러가 발생하지 않는다.

- FETCH를 통해서 현재 행을 변수에 로드(이를 현재 행이 없을 때까지 수행할 수 있다.)

  FETCH(커서패치)

  · 현재 데이터 행을 OUTPUT변수에 반환한다.

  · 커서의 SELECT문의 컬럼의 수와 OUTPUT변수의 수가 동일해야 한다.

  · 커서 칼럼의 변수타입과 OUTPUT변수의 데이터 타입도 역시 동일해야 한다.

  · 커서는 한 라인식 데이터를 FETCH한다.

- CLOSE를 통해서 결과 행 집합을 해제

  CLOSE(커서닫기) => '닫는다'는 것은 메모리상에 존재하는 커서의 쿼리결과를 소멸시키는 것을 의미한다.

  · 사용을 마친 커서는 반드시 닫아주어야 한다.

  · 필요시 커서를 다시 열 수 있다.

  · 커서를 닫은 상태에서 FETCH는 불가능하다.

   

---

명시적 커서 FOR LOOP

https://goddaehee.tistory.com/117

상단 블로그 글쓴이에 의하면.. 명시적 커서 FOR LOOP를 가장 많이 사용하고 내부적으로 처리되는 데이터의 양, I/O측면에서 훨씬 효율적이라고 한다..

- FOR LOOP가 자동적을 커서를 OPEN해주며, 행이 없을 때까지 FETCH해주고, CLOSE해준다.

- ROWTYPE에 해당하는 변수를 따로 DECLARE할 필요가 없다. (암시적으로 선언됨. 암시적 카운터는 FOR LOOP안에서만 쓸 수 있다.)

 

커서와 FOR문

https://logical-code.tistory.com/54

 

기존의 FOR문 형태

FOR 인덱스 IN 초기값..최종값
LOOP
	처리문;
END LOOP;

 

커서를 이용한 FOR문

FOR 레코드 IN 커서명(매개변수1, 매개변수2..)
LOOP
	처리문;
END LOOP;

=> 이때, 레코드는 테이블 로우 전체를 담아둘 수 있는 오라클 데이터 타입 중 하나이다.

 

-- < 명시적 커서 예제 > 
DECLARE
 -- 사원명을 받아오기 위한 변수 선언
 VS_EMP_NAME EMPLOYEED.EMP_NAME%TYPE;
 
 -- 커서 선언, 매개변수로 부서코드를 받기
 CURSOR CUR_EMP_DEP(DP_DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID)
 
 IS
 	SELECT EMP_NAME
    FROM EMPLOYEES
    WHERE DEPRARTMENT_ID = CP_DEPERTMENT_ID;
 BEGIN
 	-- 커서 오픈
    OPEN CUT_EMP_DEP(90);
    
    -- 반복문을 통한 커서 패치 작업
    LOOP
    	-- 커서 결과로 나온 로우를 패치함
        FETCH CUR_DEP INTO VS_EMP_NAME;
        -- 패치된 참조 로우가 더 없으면 LOOP탈출
        EXIT WHEN CUR_EMP_DEP%NOTFOUND;
        --사원명을 출력
        DBMS_OUTPUT.PUT_LINE(VS_EMP_NAME);
    END LOOP;
    
    CLOSE CUR_EMP_DEP;
END;
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
-- < 명시적 커서 FOR문 예제 > 

DECLARE
    -- 커서 선언, 매개변수로 부서코드를 받기
    CURSOR CUR_EMP_DEP(CP_DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%TYPE)
    IS
    	SELECT EMP_NAME
        FROM EMPLOYEES
        WHERE DEPARTMENT_ID = CP_DEPARTMENT_ID;
    BEGIN
       -- FOR문을 통한 커서 패치 작업
       FOR EMP_REC IN CUR_EMP_DEP(90)
       LOOP
         -- 사원명을 출력
         DBMS_OUTPUT.PUT_LINE(EMP_REC.EMP_NAME);
       END LOOP;
    END;
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
-- < 명시적 커서 선언부분은 없애고 FOR문에 직접 정의내용 넣기 예제 > 
DECLARE

BEGIN
	FOR EMP_REC IN (SELECT EMP_NAME
    				FORM EMPLOYEES
                    WHERE DEPARTMENT_ID = 90
    				)
      LOOP
      	DBMS_OUTPUT.PUT_LINE(EMP_REC.EMP_NAME);
       END LOOP;
  END;

마지막 예제를 보면 확실히 많이 줄었고 

FOR문에 시작값 ..종료값이 아니라

1,2,3 이렇게 바로 값을 넣을 수 있다.