커서(Cursor)

-- 커서(Cursor)

   -- 하나의 레코드가 아닌 여러 레코드로 구성된 작업영역에서 SQL문을 실행하고 그 과정에 생긴 정보를 저장하기 위해서 CURSOR를 사용

  1. 암시적인 커서

     암시적인 커서는 오라클이나 PL/SQL실행 메커니즘에 의해 처리되는 SQL문장이 처리되는 곳에 대한 익명의 address로 오라클 데이터베이스에서 실행되는 모든 SQL문장은 암시적인 커서이며, 암시적인 커서 속성이 사용될 수 있다.

     -- 암시적 커서의 속성

       SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수

       SQL%FOUND : 해당 SQL 영향을 받는 행의 수가 1개 이상일 경우 TRUE

       SQL%NOTFOUND : 해당 SQL 문에 영향을 받는 행의 수가 없을 경우 TRUE

       SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색

  2. 명시적(explicit) 커서

    명시적 커서는 프로그래머에 의해 선언되며 이름이 있는 커서로 여러 row를 다룰 수 있다.

    -- 작업 순서

       CURSOR 선언 → 커서 OPEN → FETCH → 커서 CLOSE

    -- 커서 선언

      CURSOR 커서이름 IS [SELECT 문];

      실행하고자 하는 SELECT문을 작성 한다.

    -- 커서 OPEN

       OPEN 커서이름;

      OPEN은 커서에서 선언된 SELECT문의 실행을 의미 한다.

 

    -- FETCH

        LOOP

            FETCH 커서이름 INTO variable1, variable2 ;

            EXIT WHEN [조건];

        END LOOP;

         OPEN된 SELECT 문에 의해 검색된 하나의 행 정보를 읽어 OUT 변수에 대입한다. 만약 리턴 되는 결과가 여러 개인 경우 LOOP ~ END LOOP와 같은 반복문을 이용하여 마지막 행이 읽혀질 때까지 계속 읽게 된다.

    -- 커서 CLOSE

        CLOSE 커서이름;

        선언된 SELECT문의 선언을 해제 한다.

3. FOR문에서 커서 사용(Cursor FOR Loops)

    FOR문을 사용하면 커서의 OPEN, FETCH, CLOSE가 자동 발생하므로 따로 기술할 필요가 없고 레코드 이름도 자동 선언되므로 따로 선언할 필요가 없다.

    -- 형식

       FOR 레코드이름 IN 커서이름 LOOP

              문장;

              ......

      END LOOP;

4. WHERE CURRENT OF

    FETCH문에 의해 가장 최근에 처리된 행을 참조하기 위해서 "WHERE CURRENT OF 커서이름 "  절로 DELETE나 UPDATE문 작성이 가능하다. 이 절을 사용하기 위해서는 참조하는 커서가 있어야 하며, FOR UPDATE절이 커서 선언 query문장 안에 있어야 한다.

DECLARE

  vname insa.name%TYPE;

  vpay NUMBER;

  CURSOR insa_list IS

    SELECT name, basicpay+sudang pay FROM insa;

BEGIN

  OPEN insa_list;

  LOOP

    FETCH insa_list INTO vname, vpay; --한행을 가져와라

    EXIT WHEN insa_list%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(vname || '   ' || vpay);

  END LOOP;

  CLOSE insa_list;

END;

/

CREATE OR REPLACE PROCEDURE curInsa

IS

  vname insa.name%TYPE;

  vpay NUMBER;

  CURSOR insa_list IS

    SELECT name, basicpay+sudang pay FROM insa;

BEGIN

  OPEN insa_list;

  LOOP

    FETCH insa_list INTO vname, vpay; --한행을 가져와라

    EXIT WHEN insa_list%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(vname || '   ' || vpay);

  END LOOP;

  CLOSE insa_list;

END;

/

EXEC curInsa;

-- SYS_REFCURSOR

   -- REF CURSOR : 9i 이전

   -- SYS_REFCURSOR : 9i 이후

   -- 테이블의 여러 로우를 반복적으로 조회하기 위해 레퍼런스 커서(reference cursor)를 사용한다.

-- 하나의 행밖에 넣지 못하는 커서에 pResult 값을 쭈욱 여러개 넣어줘서 REFCURSOR(여러개 가능)로 다시 불러오는 작업을 한거 같다.

-- 실무에서는 많이 알려지지 않아서 활용하지 않지만 활용하면 매우 유용

CREATE OR REPLACE PROCEDURE select_Insa

(

   pResult OUT SYS_REFCURSOR -- 파라미터가 OUT이라 해당값을 불러온 놈에게 돌려준다 => select_Insa(vResult,pcity);

  ,pcity IN insa.city%TYPE

)

IS

BEGIN

  OPEN pResult FOR SELECT name, city FROM insa WHERE city=pcity;

END;

/

CREATE OR REPLACE PROCEDURE list_Insa

( -- 실행자에 의해서 받아올 인수( 파라미터가 IN이면 해당 프로시져안에서 소화한다, OUT이면 불러온 실행문에 값을 돌려준다)

  pcity  IN insa.city%TYPE

)

IS -- 실행문에서 사용될 변수

  vResult SYS_REFCURSOR;

  vname  insa.name%TYPE;

  vcity insa.city%TYPE;

BEGIN

  select_Insa(vResult,pcity); -- 이 문장이  select_Insa 테이블에 값을 넘겨준다.

  LOOP

    FETCH vResult INTO vname, vcity;

    EXIT WHEN vResult%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(vname || '   ' || vcity);

  END LOOP;

END;

/

EXEC list_insa('부산');