Oracle Cursor2

 커서란 SQL*Plus에서 사용자가 실행한 SQL문의 단위를 의미합니다. 오라클에서 수행한 모든 쿼리문은 커서 단위로 처리합니다. 커서는 암시적 커서와 명시적 커서로 나뉩니다. PL/SQL의 SQL문처럼 하나의 결과를 리턴하는 경우 커서 없이도 SQL문의 실행된 결과가 암시적으로 커서에 저장되므로 이를 암시적 커서라고 합니다.

- SQL문을 수행한 후에 결과로 얻어지는 행이 여러 개일 경우에는 암시적인 커서에 정보를 저장할 수 없기에 에러가 발생합니다. 이런 경우에 반드시 명시적인 커서를 사용해야 합니다.

# 커서의 상태

- 명시적인 커서는 PL/SQL의 레코드(RECORD)와 PL/SQL 의 테이블(TABLE)을 결합한 것으로서 프로그램 언어의 구조체 배열과 유사합니다. 커서를 사용하기 위해서는 다음과 같은 4가지 단계를 거쳐야 합니다.

1. 커서를 선언한다. (CURSOR cur_name)
2. 커서를 오픈한다. (OPEN cur_name)
3. 커서의 조회한 결과를 인출해 저장한다. (FETCH cur_name)
4. 커서를 닫는다. (CLOSE cur_name)

1. 커서를 선언한다.

CURSOR cursor_name
IS statement;

- 커서를 선언할 때에는 실행할 SELECT 문을 IS 다음에 서브쿼리 형태로 기술합니다.

CURSOR C1 IS
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO = 20;

- C1은 커서의 이름이고 이 커서는 사원 테이블에서 부서번호가 20인 행을 조회합니다.

2. 커서를 오픈한다.
- 커서를 오픈 하게 되면 커서를 선언할 때 작성한 SELECT 문을 실행하게 됩니다.

OPEN C1;

3. 커서에 조회한 결과를 인출해 저장한다.
- FETCH 명령어를 수행하면 오픈한 SELECT문에 의해 검색된 한 개의 정보를 읽어오게 됩니다.
읽어온 정보는 변수에 저장합니다.

FETCH C1 INTO VEMPNO, VENAME, VSAL;

- SELECT 문의 결과로 얻어지는 행이 여러개일 경우에는  LOOP-END LOOP와 같은 반복문을 이용해서 마지막 행을 읽을 때가지 반복해서 수행해야 합니다.

LOOP
    FETCH C1 INTO VEMPNO, VENAME, VSAL;
    EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(VEMPNO) || VNAME || TO_CHAR(VSAL));
END LOOP;

- 만족하는 행을 발견하지 못할 때 즉, 마지막 행을 읽었을 때 반복문을 벗어나도록 하기 위해서는 'EXIT WHEN C1%NOTFOUND'와 같이 기술 합니다.

4. 커서를 닫는다.
- CLOSE 명령문으로 더 이상 사용되지 않을 커서를 닫습니다.

CLOSE C1;

- 커서를 닫으면 (CLOSE) 커서에 선언되 SELECT문의 선언이 해제됩니다.

- 20번 부서에 근무하는 사원의 정보를 출력해 봅니다.

SQL> SET SERVEROUTPUT ON -- DBMS_OUTPUT 출력 가능하도록 설정.
SQL> DECLARE
-- 커서를 실행한 결과를 저장할 지역 변수 선언
  2     vempno   NUMBER(4);
  3     vename   VARCHAR2(20);
  4     vsal    NUMBER(7, 2);
  5 
-- 번호가 20인 사원의 정보를 조회하는 SELECT문을 커서로 선언.
  6     CURSOR C1
  7     IS
  8     SELECT EMPNO, ENAME, SAL
  9     FROM EM01
 10     WHERE DEPTNO = 20;
 11 
 12  BEGIN
 13     OPEN C1; -- 커서를 오픈하면 커서를 선언할 때 작성한 SELECT 문이 실행됩.
 14     dbms_output.put_line('번호   이름   급여');
 15 
 16     LOOP
 17             FETCH C1 INTO VEMPNO, VENAME, VSAL; -- SELECT 문을 실행한 결과를 한행씩 가져와서 변수에 저장
 18             EXIT WHEN C1%NOTFOUND; -- 마지막 행까지 읽어서 행이 발견되지 않으면 반복문을 벗어남.
 19             DBMS_OUTPUT.PUT_LINE(TO_CHAR(VEMPNO) || '       ' || VENAME || '         ' || TO_CHAR(VSAL)); -- 검색된 결과를 사용자 화면에 출력.
        END LOOP ;
-- LOOP 구문 : 커서 실행 결과 구해진 행이 여러 개이면 반복문을 사용하여 처리.
 20   21        CLOSE C1;
 22  END ;
 23  /
번호   이름   급여
7566    JONES    2975
7902    FORD     3000
7368    SMITH    800
7788    SCOTT    3000
7876    TEST3    1100

PL/SQL procedure successfully completed.

SQL>

# OPEN-FETCH-CLOSE가 없이 커서 처리

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  -- 커서를 수행한 결과 값을 저장할 레코드 형태의 변수.
  3     vemp     emp%ROWTYPE;
  4 
  5  -- 커서 선언
  6     CURSOR C1
  7     IS
  8     SELECT EMPNO, ENAME, SAL
  9     FROM EM01
 10     WHERE DEPTNO = 20;
 11 
 12  BEGIN
 13     dbms_output.put_line('번호   이름   급여');
 14 
 15  /* FOR 레코드명 IN 커서명 LOOP */
 16  -- OPEN-FETCH-CLOSE문을 하나의 FOR문으로 대체할 수 있음.
 17 
 18     FOR vemp IN C1 LOOP
 19             EXIT WHEN C1%NOTFOUND;
 20             DBMS_OUTPUT.PUT_LINE(TO_CHAR(vemp.empno) || '   ' || vemp.ename || '    ' || TO_CHAR(vemp.sal) || '------------->'||C1%ROWCOUNT);
 21     END LOOP;
 22  END ;
 23  /
번호   이름   급여
7566    JONES   2975------------->1
7902    FORD    3000------------->2
7368    SMITH   800------------->3
7788    SCOTT   3000------------->4
7876    TEST3   1100------------->5

PL/SQL procedure successfully completed.

# 커서를 활용한 실용 예제

SQL> DECLARE
  2     tot      NUMBER := 0;
  3     CURSOR emp_cursor
  4     IS
  5     SELECT ENAME, SAL
  6     FROM EM01;
  7  BEGIN
  8     dbms_output.put_line('이름                          급여');
  9     dbms_output.put_line('-------------------------');
 10 
 11     FOR cur_var IN emp_cursor LOOP
 12             tot := tot + cur_var.sal;
 13             dbms_output.put_line(cur_var.ename);
 14             dbms_output.put_line('_                ' || cur_var.sal);
 15     END LOOP;
 16     dbms_output.put_line('-------------------------');
 17     dbms_output.put_line('_                            ' || tot);
 18  END;
 19  /
이름                          급여
-------------------------
MARTIN
_                1250
JONES
_                2975
TURNER
_                1500
FORD
_                3000
SMITH
_                800
KING
_                5000
WARD
_                1250
BLAKE
_                2850
CLARK
_                2450
MILLER
_                1300
ALLEN
_                1600
SCOTT
_                3000
JAMES
_                1957
TEST
_                800
TEST3
_                1100
-------------------------
_                            30832

PL/SQL procedure successfully completed.

- 사원별 급여 현황을 그래프로 표현

SQL> DECLARE
  2     CURSOR emp_cursor
  3     IS
  4     SELECT ENAME, SAL
  5     FROM EMP
  6     ORDER BY SAL DESC;
  7 
  8     star    VARCHAR2(100);
  9     cnt     NUMBER := 0;
 10  BEGIN
 11     dbms_output.put_line('                       사원별 급여 현황');
 12     dbms_output.put_line('----------------------------------------');
 13 
 14     FOR cur_var IN emp_cursor LOOP
 15             star := NULL;
 16             cnt := round(cur_var.sal/100, 0);
 17 
 18             FOR i IN 1.. cnt LOOP
 19                     star := star || '*';
 20             END LOOP;
 21 
 22     dbms_output.put_line(cur_var.ename);
 23     dbms_output.put_line('_               ' || star || '    ' || ' (' || cur_var.sal || ')');
 24     END LOOP;
 25  END;
 26  /
사원별 급여 현황
----------------------------------------
KING
_               **************************************************       (5000)
FORD
_               ******************************   (3000)
SCOTT
_               ******************************   (3000)
JONES
_               ******************************   (2975)
BLAKE
_               *****************************    (2850)
CLARK
_               *************************        (2450)
JAMES
_               ********************     (1957)
ALLEN
_               ****************         (1600)
TURNER
_               ***************  (1500)
MILLER
_               *************    (1300)
MARTIN
_               *************    (1250)
WARD
_               *************    (1250)
ADAMS
_               ***********      (1100)
SMITH
_               ********         (800)
SMITH
_               ********         (800)

PL/SQL procedure successfully completed.