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

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

명시적 커서와 묵시적 커서에 대한 설명 문서 : 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
...

- 매개변수가 있는 커서를 선언하면 매개변수의 값이 바뀔때 마다 질의 조건이 바뀌므로 여러 개의 커서를
선언하는 것과 동일한 효과를 얻을 수 있음

커서

묵시적 커서(Implicit Cursor)

명시적 커서(Explicit Cursor)

BS Blog

2012-09-16

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

댓글 없음:

댓글 쓰기