DBMS/오라클]명시적_커서(Explicit_Cursor)_사용법

출처 : http://www.dbcore.net/~corenet/education/speciallec/sqlplsql/chap15-3.html

명시적 커서와 묵시적 커서에 대한 설명 문서 : 03_SQL_CURSOR.pdf

커서

묵시적 커서(Implicit Cursor)

명시적 커서(Explicit Cursor)

커서(Cursor)  명시적 커서(Explicit Cursor)

정의

● 명시적 커서는 하나 이상의 행을 액세스하는 SELECT 명령문을 처리하기 위해서사용 ● 선언부에서 명시적으로 커서 선언 ● 명시적 커서는 블록의 실행부에서 커서 조작 명령을 통해 처리 ● 명령을 이용하여 질의에 의해 반환된 행들을 한번에 한 행씩 처리 ● 커서 선언은 선언부에서, 커서 OPEN, FETCH, CLOSE 정의는 실행부에서 정의

처리 과정

① 커서 선언(DECLARE CURSOR절) : 커서는 공유풀 내의 전용 SQL 영역에 생성  ② 커서 열기(OPEN절) : 커서를 활성화하여 커서와 연관된 SELECT 명령문 실행  ③ 데이타 추출(FETCH절) : SELECT명령문에 의해 검색된 행 중에서 커서가 가리키는 행을 액세스  ④ 커서 닫기(CLOSE절) : 오픈된 커서를 비활성화

커서 선언

● 커서를 선언하고, 커서와 관련된 SELECT 명령문 정의 ● SELECT 문장에는 INTO절을 사용하지 않음 ● INTO절은 실행부의 FETCH 명령문에서 사용 ● 커서 선언 부분에서 지역 변수나 전역 변수 사용 가능 ● 커서 정의에서 사용되는 지역 변수나 전역 변수는 반드시 커서 선언 전에 정의 필요 ● 변수는 컬럼명과 다르게 선언 ☞ 사용법 DECLARE CURSOR cursor IS select_statement; ☞ 사용예 DECLARE CURSOR Cursor_Emp IS SELECT ename, deptno FROM emp WHERE sal > 2000;

커서 열기 (OPEN)

● 공유풀의 라이브러리 캐쉬에 설정된 커서 영역을 오픈 ● 선언된 커서와 연관된 SELECT 명령문을 실행하여 active set을 구성 ● 커서는 검색된 행으로 구성된 active set 중에서 첫 번째 행을 가르킴 ● 실행과정   - 구문분석(Parsing) : 문장이나 의미, 권한 등이 제대로 되어 있는지 검사   - 바인딩(Binding) : 변수의 값을 할당   - 실행(Excute) : 라이브러리 캐쉬에 할당된 커서 영역을 오픈하고 active set구성 ☞ 사용법 OPEN cursor; ☞ 사용예 OPEN Cursor_Emp;

데이타 추출 (FETCH)

● 현재 커서가 가르키는 행의 값을 변수로 할당하는 과정 ● 커서 선언시 사용한 SELECT 명령문의 컬럼 개수와 데이타타입이 FECTH ∼  INTO절의 변수 개수, 데이타타입과 동일해야 함 ☞ 사용법 FETCH cursor INTO variable_list;   ● variable_list   - 현재 검색된 결과 행을 저장하기 위한 변수 리스트   - variable_list 변수의 개수는 커서 선언부에서 정의한 SELECT문장의 컬럼 개수와 동일   - 커서 안에서 PL/SQL 레코드 타입 설정이 가능하며, variable_list에도 사용 가능 ☞ 사용예 FETCH Cursor_Emp INTO v_ename, v_deptno;  커서 닫기 (CLOSE) ● 현재 오픈된 커서를 닫음 ● 커서 오픈(OPEN) 명령문에 의해 할당된 커서 영역 반환 ● 현재 커서를 닫은 후에 다시 해당 커서를 오픈 가능 ☞ 사용법 CLOSE cursor_name; ☞ 사용예 CLOSE Cursor_Emp;   ▶ 명시적 커서 수행 방법  커서 속성 ● 커서 상태를 검사하기 위해 제공되는 네 가지 속성 ● SQL 명령문에서 커서 속성을 직접 참조할 수 없음 구 분 타입 내 용 %ROWCOUNT Number ·현재까지 FETCH된 행의 갯수 %FOUND Boolean ·FETCH할 행이 존재할 경우 값은 TRUE %NOTFOUND Boolean ·FETCH할 올 행이 없을 경우 값은 TRUE %ISOPEN Boolean ·커서가 오픈되어 있을 경우 값은 TRUE ☞ 사용예 : %ROWCOUNT OPEN Cursor_Emp; LOOP FETCH Cursor_Emp INTO v_emp, v_deptno; EXIT WHEN Cursor_Emp%ROWCOUNT >= 5; END LOOP; CLOSE Cursor_Emp; ☞ 사용예 : %NOTFOUND OPEN Cursor_Emp; LOOP FETCH Cursor_Emp INTO v_emp, v_deptno; EXIT WHEN Cursor_Emp%NOTFOUND; END LOOP; CLOSE Cursor_Emp; ☞ 사용예 : %ISOPEN IF NOT Cursor_Emp%ISOPEN THEN OPEN Cursor_Emp; END IF; ☞ 커서를 사용한 프로시져 생성 실습 ☞ 프로시져 생성 SQL> CREATE OR REPLACE PROCEDURE cur_exam (v_sal IN number) IS v_ename emp.ename%TYPE; v_dname dept.dname%TYPE; CURSOR Cursor_Emp IS SELECT ename, dname FROM   emp e, dept d WHERE  e.deptno = d.deptno AND    e.sal > v_sal; BEGIN OPEN Cursor_Emp; IF NOT Cursor_Emp%ISOPEN THEN OPEN Cursor_Emp; END IF; DBMS_OUTPUT.PUT_LINE('다음은 4월 승진자 명단입니다.'); LOOP FETCH Cursor_Emp INTO v_ename, v_dname; EXIT WHEN Cursor_Emp%ROWCOUNT >= 5 OR Cursor_Emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_dname||' 부서의 '||v_ename||'씨'); END LOOP; CLOSE Cursor_Emp; DBMS_OUTPUT.PUT_LINE('발령일은 4월 1일 입니다.'); DBMS_OUTPUT.PUT_LINE(' *^^* 만우절 행사였습니다.'); END cur_exam; / Procedure created. ☞ DBMS_OUTPUT.PUT_LINE을 사용하기위해 SERVEROUTPUT 환경변수를 ON상태로 설정 SQL> SET SERVEROUTPUT ON ☞ 실행 및 출력결과 SQL> EXEC cur_exam(2500); 다음은 4월 승진자 명단입니다. ACCOUNTING 부서의 KING씨 RESEARCH 부서의 KEBIN씨 RESEARCH 부서의 SCOTT씨 RESEARCH 부서의 LEE씨 발령일은 4월 1일 입니다. *^^* 만우절 행사였습니다. PL/SQL procedure successfully completed.     CURSOR FOR 루프절 ● PL/SQL의 선언부에 이미 명시적 커서가 선언되어 있어야 함 ● FOR 루프가 초기화될 때 내부적으로 커서가 오픈 ● FOR 루프가 반복될 때마다 커서가 가르키는 행을 액세스 ● 루프는 마지막 행을 추출한 후 자동적으로 FOR 루프를 종료 ● OPEN, FETCH, CLOSE의 기능을 하나의 FOR 루프에서 실행 가능 ☞ 사용법 FOR record_name IN cursor_name LOOP statement1; statement2; .................. END LOOP; ● record_name : 내부적으로 선언한 레코드 ● cursor_name : 선언부에서 이미 선언한 커서명 ☞ 사용예 ☞ 프로시져 생성 SQL> CREATE OR REPLACE PROCEDURE cur_for(dnum IN NUMBER) IS CURSOR emp_cur IS SELECT sal, comm FROM   emp WHERE  deptno = dnum; total     NUMBER := 0; high_paid NUMBER := 0; high_comm NUMBER := 0; BEGIN FOR emp_rec IN emp_cur LOOP emp_rec.comm := NVL(emp_rec.comm,0); total := total + emp_rec.sal + emp_rec.comm; IF emp_rec.sal > 2000 THEN high_paid := high_paid + 1; END IF; IF emp_rec.comm > emp_rec.sal THEN high_comm := high_comm + 1; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('1. 전체 : ' ||total); DBMS_OUTPUT.PUT_LINE('2. $2000이상 받는 사원수 : '||high_paid); DBMS_OUTPUT.PUT_LINE('3. 월급보다 커미션이 더 많은 사원수 : '||high_comm); END cur_for; / Procedure created.    ☞ 현재 데이타 확인 SQL> select sal,comm from emp where deptno=30; SAL COMM -------- --------- 1600 300 1250 1400 2850 1500 0 950 ☞ 실행 및 출력결과 SQL> EXEC cur_for(30); 1. 전체 : 9850 2. $2000이상 받는 사원수 : 1 3. 월급보다 커미션이 더 많은 사원수 : 1 PL/SQL procedure successfully completed.   FOR UPDATE 절 ● FOR UPDATE절을 사용한 트랜잭션이 완료되기 전까지 테이블에 액세스하지 못하도록 명시적으로 락을 생성하는 방법 ● update나 delete 명령 수행 전에 대상 행에 대해 락을 생성하고자 할 때 사용 ☞ 사용법 SELECT ... FROM   ... FOR UPDATE [OF column_reference] [NOWAIT];   ● 질의한 결과 행이 다른 세션에 의해 이미 락이 걸려있다면, 즉시 오라클 에러를 반환 ● NOWAIT명령을 사용하지 않으면 다른 세션에 의한 락이 풀릴 때까지 기다림 ☞ 사용예 :update, delete문장에 의해 사용될 deptno가 10인 사원의 행을 질의하면서 해당 행에 대해 락을 생성 DECLARE CURSOR emp_cursor IS SELECT empno, ename, sal FROM   emp WHERE  deptno = 10 FOR UPDATE OF sal NOWAIT;    WHERE CURRENT OF절 ● 커서를 이용하여 가장 최근에 검색된 행을 삭제 또는 수정하는 경우에 사용 ● 커서를 선언할 때 FOR UPDATE절이 반드시 있어야 함 ● WHERE CURRENT OF절에 의해 지정된 커서는 가장 최근에 FETCH한 행을 가르킴 ☞ 사용예 ☞ 프로시져 생성 SQL> CREATE OR REPLACE PROCEDURE cur_where IS CURSOR emp_cur IS SELECT ename, rowid FROM   emp WHERE  deptno = 30 FOR UPDATE;   v_ename   emp.ename%TYPE; v_rowid   ROWID; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO v_ename,v_rowid; EXIT WHEN emp_cur%NOTFOUND; UPDATE emp SET    sal = sal*2 WHERE CURRENT OF emp_cur; -- 보통은 UPDATE emp SET sal = sal*2 WHERE rowid = v_rowid; -- 이나 UPDATE emp SET sal = sal*2 WHERE ename = v_ename; 로 -- 표현함. END LOOP; CLOSE emp_cur; COMMIT; END; / Procedure created.     매개변수가 있는 커서 선언 ● 커서 선언시 매개변수 사용 가능 ● 매개변수의 데이타타입은 스칼라 변수와 동일하고 크기 지정은 불가능 ● 매개변수는 커서와 관련된 SELECT 명령문에서 참조하기 위해 사용 ● 커서가 오픈될 때 커서에 매개변수 값을 전달하여 질의 내에서 사용 ☞ 사용법 CURSOR cursor_name [parameter datatype, ...)] IS select_statement; ☞ 사용예 CURSOR emp_cur(dnum number) IS SELECT sal, comm FROM   emp WHERE  deptno = dnum; BEGIN FOR emp_rec IN emp_cur(30) LOOP ...   - 매개변수가 있는 커서를 선언하면 매개변수의 값이 바뀔때 마다 질의 조건이 바뀌므로 여러 개의 커서를 선언하는 것과 동일한 효과를 얻을 수 있음

커서

묵시적 커서(Implicit Cursor)

명시적 커서(Explicit Cursor)