PL/SQL - 06(Cursor)

PL/SQL에서 반드시 알아야 할 부분인 커서에 대해 공부해보겠습니다~

모든 SQL문은 연관된 각각의 커서를 소유하고 있으며 커서에는 2가지 종류의 커서가 존재합니다.

1. 암시적 커서 - 모든 DML 및 PL/SQL SELECT문에 대해 선언됩니다.

2. 명시적 커서 - 프로그래머에 의해 선언되며 이름이 있는 커서입니다.

먼저 암시적인 커서에 대해서 알아보겠습니다.

암시적 커서

암시적인 커서는 PL/SQL 실행 메커니즘에 의해 처리되는 SQL 문장이 처리되는 곳에 대한 익명의 어드레스입니다

오라클 DB에서 실행되는 모든 SQL문은 암시적인 커서로 모든 암시적인 커서 속성이 사용 가능합니다.

암시적 커서 속성

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

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

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

SQL% ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색( 암시적 커서는 SQL문이 실행되는 순간 자동으로 열림과 닫힘 실행)

암시적 커서 예제

예제로 알아보는 것이 가장 이해하기 쉬울 것 같습니다.

BEGIN
  SELECT a FROM b WHERE
  --검색된 데이터 존재
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('검색된 데이터 존재');
  END IF;
  UPDATE 문
  -- 수정한 데이터 카운트 변수에 저장
  v_update_row := SQL%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE('수정된 데이터의 수(ex ~한 사람의 수);
END;

select문의 로우가 하나라도 존재한다면 SQL% FOUND가 TRUE를 반환합니다.

update문의 영향을 받는 행의 수를 변수에 저장하여 수정된 데이터의 수를 출력하는 예제입니다.

명시적 커서(EXplicit Cursor)

CURSOR cursor_name IS SELECT statement

커서 열기(OPEN)

커서 열기는 OPEN문을 사용합니다.

커서안의 검색이 실행되며 아무런 데이터 행을 추출하지 않아도 에러가 발생하지 않습니다.

OPEN cursor_name;

커서 패치(FETCH)

FETCH는 현재 데이터 행을 OUTPUT 변수에 반환합니다.

커서의 SELECT문의 칼럼의 수와 OUTPUT 변수의 수와 타입이 동일해야 합니다.

또한, 한 라인씩 데이터를 패치합니다.

FETCH cursor_name INTO var1, var2;

커서 닫기(CLOSE)

사용을 마친 커서는 반드시 닫아야 합니다!

커서를 닫은 상태에서는 fetch 불가능!

CLOSE cursor_name;

Explicit Cursor 예제
특정 부서의 평균급여와 사원수를 출력..
SQL>CREATE OR REPLACE PROCEDURE ExpCursor_Test
 	(v_deptno dept.deptno%TYPE)
 	IS
   CURSOR dept_avg IS
   SELECT b.dname, COUNT(a.empno) cnt, ROUND(AVG(a.sal),3) salary
   FROM emp a, dept b
   WHERE a.deptno = b.deptno
   AND b.deptno = v_deptno
   GROUP BY b.dname ;

   -- 커서를 패치하기 위한 변수 선언
   v_dname dept.dname%TYPE;
   emp_cnt NUMBER;
   sal_avg NUMBER;
   BEGIN
   -- 커서의 오픈
   OPEN dept_avg;
   -- 커서의 패치
   FETCH dept_avg INTO v_dname, emp_cnt, sal_avg;
     DBMS_OUTPUT.PUT_LINE('부서명 : ' || v_dname);
     DBMS_OUTPUT.PUT_LINE('사원수 : ' || emp_cnt);
     DBMS_OUTPUT.PUT_LINE('평균급여 : ' || sal_avg);
   -- 커서의 CLOSE
   CLOSE dept_avg;
   EXCEPTION
     WHEN OTHERS THEN
     	DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
 END;
 / 

FOR문 커서 사용(Cursor FOR Loops)

FOR문을 사용하면 커서의 OPEN , FETCH, CLOSE가 자동 발생하여 따로 선언하지 않아도 됩니다.

파라미터 커서(Cursors with Parameters)

커서가 열리고 질의가 실행되면 매개변수 값을 커서에 전달!

다른 active set을 원할 경우 명시적 커서를 따로 선언해야 합니다.

CURSOR cursor_name [(paramter name datatype, .. )] IS SELECT statement

select 문에 where 조건에 파라미터를 사용하고 싶을 때 사용합니다.

SQL> CREATE OR REPLACE PROCEDURE ParamCursor_Test
   (param_deptno emp.deptno%TYPE)
   IS
   v_ename emp.ename%TYPE;
   -- Parameter가 있는 커서의 선언
   CURSOR emp_list(v_deptno emp.deptno%TYPE) IS
   SELECT ename FROM emp WHERE deptno = v_deptno;
   BEGIN
     DBMS_OUTPUT.ENABLE;
     DBMS_OUTPUT.PUT_LINE(' ****** 입력한 부서에 해당하는 사람들 ****** ');
     -- Parameter변수의 값을 전달(OPEN될 때 값을 전달한다)
     FOR emplst IN emp_list(param_deptno) LOOP
      DBMS_OUTPUT.PUT_LINE('이름 : ' || emplst.ename);
   END LOOP;
   EXCEPTION
     WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
   END;
 / 

WHERE CURRENT OF

rowid를 이용하지 않고 현재 참조하는 행을 수정, 삭제할 수 있게 해주는 기능입니다.

fetch문에 의해 가장 최근에 처리된 행을 참조하기 위해 "WHERE CURRENT OF cursor_name"으로

delete 또는 update 문을 사용할 수 있습니다.

단, 참조하는 커서가 있어야 하며, for update절이 커서 선언 query문장 안에 있어야 하고 그렇지 않을 경우 error가 발생합니다.

지금까지 커서에 대해 알아보았습니다. 저는 정리하면서 커서라는 개념이 많이 어렵다고 생각이 되는 동시에 엄청나게 효율적인 도구라고 생각했습니다.

이전에 안드로이드에서 SQLLite를 활용 할 때, CURSOR라는 것을 처음 활용했는데 그 당시에는 단지 행이 언제 위치해 있는지를 가르쳐주는 정보라고 생각했고 어떻게 활용할지는 전혀 생각하지 못했는데 이런 여러 활용 방안이 있다는 걸 꺠달았네요.

 

* 이 포스팅은 아래 출처의 내용을 개인적인 공부를 위해 정리한 내용입니다.

출처 : http://www.gurubee.net/oracle/plsql

Oracle PL/SQL 강좌