행복만땅 개발자

###################################################################################################

#   source site : blog.naver.com/tangamjaelt

#   master : 강용운

#   email : tangamjaelt@korea.com

#   last release : 2007.08.29

###################################################################################################

1. 커서란...

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

- 메모장을 켜보면 깜빡거리는 것. 그게 커서이다.

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

- 커서를 통해, 메모리에 존재하는 SQL문 실행결과를 바로 접근하여 fetch 할 수 있다. 메모리영역이라 함은 private SQL영역으로 SQL문 실행 및 처리결과를 저장하는 곳이다.

- 실행결과를 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 예외가 발생한다.

- 커서 선언에 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를 통해 출력하였다.