2012-09-16

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



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

명시적 커서(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
...
  - 매개변수가 있는 커서를 선언하면 매개변수의 값이 바뀔때 마다 질의 조건이 바뀌므로 여러 개의 커서를
선언하는 것과 동일한 효과를 얻을 수 있음
 

명시적 커서(Explicit Cursor)

댓글 없음:

댓글 쓰기