[PL/SQL] 커서 (CURSOR)

Cursor란?

• Private SQL 작업 영역 (SQL문 실행 -> 처리 -> 저장 하는 작업 영역)
• Oracle Server에 의해 실행되는 모든 SQL 문은 연관된 각각의 커서를 소유함.
• 개념을 Java에서의 ResultSet으로 생각하자!

Implicit Cursor (암시적 커서)

• 모든 DML과 PL/SQL SELECT문에 대해 선언됨
• Implicit cursor의 attribute
  - SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수
  - SQL%FOUND : 해당 SQL 영향을 받는 행의 수가 한 개 이상일 경우 TRUE
  - SQL%NOTFOUND : 해당 SQL 문에 영향을 받는 행의 수가 없을 경우 TRUE
  - SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색

CREATE OR REPLACE PROCEDURE Implicit_Cursor
(p_empno IN emp.empno%TYPE)
IS
  v_sal emp.sal%TYPE;
  v_update_row NUMBER;
BEGIN
  DBMS_OUTPUT.ENABLE;
  
  SELECT sal
  INTO v_sal
  FROM emp
  WHERE empno = p_empno;
  
  -- 검색한 데이터가 있을 경우
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재합니다 : ' || v_sal);
  END IF;
  
  UPDATE emp
  SET sal = sal*1.1
  WHERE empno = p_empno;

  -- 변경된 row의 개수
  v_update_row := SQL%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE('급여가 인상된 사원 수 : ' || v_update_row);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('검색한 데이터가 없습니다.');
END;

 

Explicit Cursor (명시적 커서)

• 프로그래머에 의해 선언되며 이름이 있는 커서
• 4단계 명령에 의해서 사용됨 (DECLARE -> OPEN -> FETCH -> CLOSE)
  1. Declare Cursor
     - 커서에 이름 부여하고 커서 내에서 수행할 select 문 정의
  
  2. Open 단계
     - 참조되는 변수 연결하고 Select문 실행
     - Action Set(Select로 검색된 Row들)은 Fetch단계에서 사용
     - Cursor는 Action Set을 생성한 다음, 첫번째 Row에 Pointer를 위치시켜 가리키게 됨.
     - Cursor를 Open할 때 검색된 Row가 없어도 에러 발생X
     - Fetch 한 후에 Cursor의 Attribute를 살펴서 알아볼 수 있음.
  
  3. Fetch 단계
     - Cursor로부터 Pointer가 존재하는 Record의 값을 변수에 전달해줌.
     - 그리고, pointer는 active set의 다음 record로 이동
  
  4. Close 단계
     - Record의 Active Set을 닫아주고, 다시 새로운 Active Set을 만들어 Open할 수 있게 해줌.
• Explicit Cusor의 attribute
  - %ISOPEN : cursor가 open되어 있으면 true
  - %NOTFOUND : fetch문에 의해 검색도니 데이터가 없으면 true
  - %FOUND : fetch문에 의해 검색된 데이터가 있으면 true
  - %ROWCOUNT : fetch한 데이터의 총 개수
• 예제 소스

CREATE OR REPLACE PROCEDURE Show_Emp3
(p_empno IN emp.empno%TYPE)
IS
  -- 1. Declare cursor
  CURSOR emp_cursor IS
    SELECT ename, job, sal
    FROM emp
    WHERE empno LIKE p_empno||'%';
    
  -- cursor fetch하기 위한 변수 선언    
  v_ename emp.ename%TYPE;
  v_job   emp.job%TYPE;
  v_sal   emp.sal%TYPE;
BEGIN
  DBMS_OUTPUT.ENABLE;
  -- 2. Open cursor
  OPEN emp_cursor;
  DBMS_OUTPUT.PUT_LINE('이름    업무    급여');
  DBMS_OUTPUT.PUT_LINE('------------------');
  
  -- 3. Fetch cursor
  LOOP -- do while과 동일
    FETCH emp_cursor INTO v_ename, v_job, v_sal;
    EXIT WHEN emp_cursor%NOTFOUND;  -- *반드시 exit 조건문 써줘야함*
    DBMS_OUTPUT.PUT_LINE(v_ename || '   ' || v_job || '   ' || v_sal);
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE(emp_cursor%ROWCOUNT || '개의 행 선택');
  
  -- 4. Close cursor
  CLOSE emp_cursor;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM || '에러 발생');
END;

 

• 항상 4단계를 거치기에는 너무 번거롭다....
  => For statement를 사용하면 Open + Fetch + Close 한번에 해줌!
       : 실무에서는 이 방법 사용. 하지만 위의 개념은 반드시 알아야함.

CREATE OR REPLACE PROCEDURE ForCursor_Test
IS
  CURSOR dept_sum IS
        SELECT b.dname, COUNT(a.empno) cnt, SUM(a.sal) salary
        FROM   emp a, dept b
        WHERE  a.deptno = b.deptno
        GROUP BY b.dname;

BEGIN
  DBMS_OUTPUT.ENABLE;
  
  -- Cursor를 for문에 실행
  FOR emp_list IN dept_sum LOOP
        DBMS_OUTPUT.PUT_LINE('부서명 : ' || emp_list.dname);
        DBMS_OUTPUT.PUT_LINE('사원수 : ' || emp_list.cnt);
        DBMS_OUTPUT.PUT_LINE('급여합계 : ' || emp_list.salary);
  END LOOP;
  
EXCEPTION
  WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM || '에러 발생 ');
END;