[DB] Cursor

1. 커서(Cursor)란?

• 커서는 Private SQL의 작업 영역이다.
• 오라클 서버에 의해 실행되는 SQL문은 연관된 각각의 커서를 소유하고 있다.
• 커서의 종류
  - 암시적 커서 : 모든 DML과 PL/SQL SELECT문에 대해 자동 선언된다.
  - 명시적 커서 : 프로그래머에 의해 선언되며, 이름이 있는 커서이다.

 

2. 암시적 커서(Implicit Cursor)

• 암시적 커서는 Oracle이나 PL/SQL 실행 메커니즘에 의해 SQL 문장이 처리되는 곳에 대한 익명의 주소이다. Oracle에서 실행되는 모든 SQL문에는 암시적 커서가 생성되며, 커서 속성을 사용할 수 있다. 암시적 커서는 OPEN과 CLOSE가 자동으로 실행된다.
• 암시적 커서의 속성
  - SQL%ISOPEN : 암시적 커서가 열려 있는지 판단. 항상 FALSE
  - SQL%FOUND : 해당 SQL문에 영향을 받는 행이 존재하면 TRUE
  - SQL%NOTFOUND : 해당 SQL문에 영향을 받는 행이 존재하지 않으면 TRUE
  - SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수
• 암시적 커서 예제

CREATE OR REPLACE PROCEDURE Implicit_Cursor
	(p_empno IN emp.empno%TYPE)

	IS
	v_sal  emp.sal%TYPE;
	v_update_row NUMBER;

	BEGIN
		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;

		-- 수정한 데이터의 카운트를 변수에 저장
		v_update_row := SQL%ROWCOUNT;
		DBMS_OUTPUT.PUT_LINE('급여가 인상된 사원 수 : '|| v_update_row);
        
	EXCEPTION    
		WHEN   NO_DATA_FOUND  THEN  
		DBMS_OUTPUT.PUT_LINE(' 검색한 데이터가 없네요... ');

END;
/
============정상처리 결과============
SQL> set serveroutput on;
SQL> exec implicit_cursor(7369);
검색한 데이터가 존재합니다 : 800
급여가 인상된 사원 수 : 1

PL/SQL 처리가 정상적으로 완료되었습니다.

============예외처리 결과============
SQL> exec implicit_cursor(733223);
검색한 데이터가 없네요...

PL/SQL 처리가 정상적으로 완료되었습니다.

 

 

3. 명시적 커서(Explicit Cursor)

• Declare => Open => Fetch => Close
• 명시적 커서의 속성
  - SQL%ISOPEN : 커서가 OPEN 되어 있으면 TRUE
  - SQL%FOUND : FETCH한 데이터가 행을 반환하면 TRUE
  - SQL%NOTFOUND : FETCH한 데이터가 행을 반환하지 않으면 TRUE
  - SQL%ROWCOUNT : 현재까지 반환된 모든 데이터 행의 수
• 명시적 커서 예제

-- 특정 부서의 평균급여와 사원수를 출력
CREATE OR REPLACE PROCEDURE ExpCursor_Test 
	(v_deptno IN  dept.deptno%TYPE)

	IS
		-- DECLARE
		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 ;

		-- FETCH하기 위한 변수 선언
		v_dname dept.dname%TYPE;
		emp_cnt NUMBER;
		sal_avg NUMBER;

	BEGIN
		-- OPEN
		OPEN dept_avg;

		-- FETCH
		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;
/

============정상처리 결과============
SQL> set serveroutput on;
SQL> execute expcursor_test(30);
부서명 : SALES
사원수 : 6
평균급여 : 1566.667

PL/SQL 처리가 정상적으로 완료되었습니다.

============예외처리 결과============
SQL> execute expcursor_test(40);
부서명 :
사원수 :
평균급여 :

PL/SQL 처리가 정상적으로 완료되었습니다.

예외처리가 정상적으로 됬다면, '에러 발생'이 호출되었어야 하나, 이번 경우에는 제대로 작동하지 않았다.
그 이유에 대해서는 좀 더 공부해본 후 다시 수정하여 올리도록 하겠다.

[참조문서]
https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/06_ora.htm#i45507