'PLSQL'에 해당되는 글 1건

  1. 2007.12.21 | [본문스크랩] PL/SQL (13) - 커서(cursor)

[본문스크랩] PL/SQL (13) - 커서(cursor)

오라클 | 2007. 12. 21. 15:30
Posted by 시반

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

#   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문 실행 및 처리결과를 저장하는 곳이다.

- 이 때 커서는 현재 처리하고 있는 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를 통해 출력하였다.

 

 

 

 

 
블로그 이미지

시반

시반(詩伴)이란 함께 시를 짓는 벗이란 뜻을 가지고 있습니다. 함께 나눌수 있는 그런 공간이길 바라며...

카테고리

분류 전체보기 (233)
개발 이야기 (73)
WEB2.0 (57)
DB2 (24)
MySQL (6)
오라클 (26)
기타 (44)
취미 (0)
잡담 (2)