PL/SQL (13) - 커서 (Cursor)

​

1. 커서란...

- 일반적으로 커서는 모니터에 해당 위치를 알려주고 그곳에 입력을 대기중이라고 깜빡거리는 것을 나타낸다.

- 같은 맥락으로 PL/SQL에서 커서는 메모리상에 SQL문이 실행되는 위치를 가리킨다.

- 커서를 통해, 메모리에 존재하는 SQL문 실행결과를 바로 접근하여 fetch 할 수 있다.

- 이 때 커서는 현재 처리하고 있는 row를 가리키게 된다.

- 실행결과를 1개 row씩 처리하다가 마지막까지 처리가 끝나면 커서를 닫는다.

- 많은 row를 처리하기 위해 명시적 커서를 선언하고 제어한다.

 

 

2. 종류

(1) 암시적커서 (Implicit Cursor) : 모든 DML, PL/SQL Select 문에 대해 선언된다.

- 모든 SQL문에는 관련된 개별 커서가 존재한다.

- SQL문을 실행하면 PL/SQL은 암시적 커서를 작성하여 자동관리 한다.

 

(2) 명시적커서 (Explicit Cursor) : 프로그래머가 선언하고 이름을 지정한다.

- query 결과를 첫번째 행부터 차례대로 처리할 수 있다.

- 현재 처리중인 행을 추적한다.

- 프로그래머가 PL/SQL 블록에 명시적 커서를 수동으로 제어할 수 있다.

- 여러 행 질의에 의해 반환되는 행집합을 활성 집합이라하고 활성집합의 크기는 검색조건을 만족하는 행(row)수와 같다.

 

< 명시적 커서 >

 

(1) 커서를 연다.

(2) 행(row)을 인출(fetch)한다.

(3) 커서를 닫는다.

 

- OPEN 문은 질의를 실행하여 결과 집합을 식별한 후 커서를 첫번째 행 앞에 위치시킨다.

- FETCH 문은 현재 행(row)을 검색하고 지정한 조건(empty?)이 만족할 때까지 커서를 다음 행(row)로 이동시킨다.

- CLOSE 문으로 마지막 행(row)까지 처리되었으면 커서를 닫는다.

 

 

3. 커서 속성

- %ROWCOUNT : 가장 최근에 인출한 행의 개수

- %FOUND : 가장 최근에 인출한 행이 있으면 TRUE

- %NOTFOUND : 가장 최근에 인출한 행이 없으면 TRUE

- %ISOPEN : 커서가 열려있으면 TRUE.

 

- 커서가 열려있어야 FETCH가 가능하므로 이 속성을 사용해서 커서의 OPEN 상태를 확인한다.

- CLOSE 된 상태에서 FETCH를 하면 INVALID_CURSOR 예외가 발생한다.

IF NOT cursor_name%ISOPEN THEN     OPEN curosr_name; END IF;

 

4. 커서 선언

CURSOR cursor_name IS     SELECT문;

- 커서 선언에 INTO 절을 포함시키지 않는다. INTO절은 FETCH문에 포함된다.

- 질의에 ORDER BY를 사용하여 특정 순서로 행을 처리할 수 있다.

- CURSOR 질의에 있는 변수를 참조할 수 있으나 변수는 CURSOR문 앞에 선언해야 한다.

 

cursor_test.sql

  DECLARE
         v_mp mobile_phone.product_name%TYPE;
     CURSOR cursor_mp IS
         SELECT product_name FROM mobile_phone;
 
  BEGIN
     OPEN cursor_mp;
          LOOP
              FETCH cursor_mp INTO v_mp;
              EXIT WHEN cursor_mp%NOTFOUND;
                  DBMS_OUTPUT.PUT_LINE(v_mp);
          END LOOP;
          DBMS_OUTPUT.PUT_LINE('총 row수 : ' || cursor_mp%ROWCOUNT);
     CLOSE cursor_mp;
  END;
   /

 

- 명시적으로 OPEN, FETCH, CLOSE를 수행하였다.

 

SQL> SET SERVEROUTPUT ON;

SQL> @cursor_test

 

 

5. 커서 FOR LOOP 사용 (권장)

- 커서 FOR 루프를 사용하면 암시적 커서가 자동으로 실행된다.

- 레코드가 암시적으로 선언된다.

- FOR 루프가 한번 반복될 때마다 행이 인출된다.

- 마지막 행이 처리되면 루프가 종료되고 커서가 자동으로 닫힌다.

- 암시적 커서 실행이 이루어지므로 OPEN, FETCH, CLOSE를 선언하지 않는다. (선언하면 이미 커서가 열렸다는 에러 발생)

 

cursor_test2.sql

  DECLARE
     CURSOR cursor_mp IS
         SELECT brand, product_name FROM mobile_phone;
     record_mp cursor_mp%ROWTYPE
 
  BEGIN
      FOR record_mp IN cursor_mp LOOP
          IF record_mp.brand = 'LG' THEN
              DBMS_OUTPUT.PUT_LINE(record_mp.brand || ' => ' || record_mp.product_name);
          END IF;
    END LOOP;
 END;
 /

 

- 이 예제에서는 브랜드가 LG인 상품을 출력하는 프로그램이다.

- 여기 예제에서는 %ROWTYPE을 사용하여 커서에 대한 record 타입 변수 record_mp 를 선언하였다.

- OPEN, FETCH, CLOSE 없이 바로 FOR문을 돌리면 된다.

 

 

 

6. 파라미터 사용 커서

- 커서에 파라미터를 정의하고 OPEN 시 파라미터를 전달할 수 있다.

- 실행할 때마다 이전에 사용했던 파라미터의 활성 집합을 닫고, 매번 새 파라미터를 이용해 커서를 OPEN한다.

- OPEN(parameter1, parameter2, ...) 형식으로 사용한다.

 

cursor_test3.sql

 DECLARE
        v_product mobile_phone.product_name%TYPE;
        v_brand mobile_phone.brand%TYPE;
 
     CURSOR cursor_mp(p_brand VARCHAR2) IS
         SELECT brand, product_name FROM mobile_phone
                WHERE brand = p_brand;
 BEGIN
        OPEN cursor_mp('LG');
        LOOP
                FETCH cursor_mp INTO v_brand, v_product;
                EXIT WHEN cursor_mp %NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_brand || ' => ' ||  v_product);
        END LOOP;

	DBMS_OUTPUT.PUT_LINE(v_brand || ' 총 ' || cursor_mp%ROWCOUNT || ' 건');
        CLOSE cursor_mp;
 
        OPEN cursor_mp('SAMSUNG');
        LOOP
                FETCH cursor_mp INTO v_brand, v_product;
                EXIT WHEN cursor_mp %NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_brand || ' => ' ||  v_product);
        END LOOP;

	DBMS_OUTPUT.PUT_LINE(v_brand || ' 총 ' || cursor_mp%ROWCOUNT || ' 건');
        CLOSE cursor_mp;
 END;
 /

 

 

- OPEN cursor 를 통해 파라미터를 전달한다.

- LG, SAMSUNG 인 것들을 각각 cursor를 통해 출력하였다.