커서 - 묵시적 커서(Implicit Cusor) : 필요할 경우 오라클이 자동적으로 선언하여 사용한 후 자동적으로 정리
- 명시적 커서(Explicit Cursor) : 사용자가 정의한 커서른 선언하여 사용하고, 커서의 사용이 끝난 후에는 별도의 정리 작업을 수행해줘야 함
묵시적 커서
- 세션 내에 단 한 개만이 선언되어 사용되었다가 문장이 종료됨과 동시에 정리, 1개의 데이터만 저장됨
묵시적 커서 속성
- SQL%ROWCOUNT
해당 커서에서 실행한 총 행의 개수(가장 마지막 행이 몇 번째 행인지 카운트)를 반환
- SQL%FOUND
해당 커서 안에 아직 수행해야 할 데이터가 있을 경우 TRUE 값을 반환하고 없을경우 FALSE 값을 반환
- SQL%NOTFOUND
해당 커서 안에 아직 수행해야 할 데이터가 없을 경우 TRUE 값을 반환하고 있을경우 FALSE 값을 반환
- SQL%ISOPEN
현재 묵시적 커서가 메모리에 OPEN 되어 있을 경우에는 TRUE 값을, 그렇지 않을 경우 FALSE 값을 가지는 속성
사용 예)
SCOTT>DECLARE
BEGIN
DELETE emp
WHERE sal < 1000 ;
DBMS_OUTPUT.PUT_LINE('1000 이하 : '||sql%rowcount||'건 삭제되었습니다') ;
DELETE emp
WHERE sal BETWEEN 1000 AND 2000 ;
DBMS_OUTPUT.PUT_LINE('1000~2000 사이 : '||sql%rowcount||'건 삭제 되었습니다') ;
END ;
/
-> 1000 이하 : 2 건 삭제 되었습니다
1000 ~2000 사이 : 6 건 삭제 되었습니다
명시적 커서
사용자가 선언하여 생성 후 사용하는 SQL 커서로, 여러 개의 행을 처리하고자 할 경우 사용
명시적 커서 속성
- 커서이름%ROWCOUNT
FETCH 문에 의해 읽혀진 데이터의 총 행 수를 가지는 속성. 가장 마지막에 처리된 행이 몇 번째 인지 반환
- 커서이름%FOUND
FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 있을 경우에는 TRUE 값을, 그렇지 않을 경우 FALSE 값을 가짐
- 커서이름%NOTFOUND
FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 없을 경우에는 TRUE 값을, 그렇지 않을 경우 FALSE 값을 가짐
- 커서이름%ISOPEN
명시적 커서가 메모리에 확보(선언) 되어 있을 경우에는 TRUE 값을, 그렇지 않을 경우에는 FALSE 값을 가짐
묵시적 커서와 FOR LOOP 문 활용하기
SCOTT>DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE('학과명과 학과의 위치 안내 입니다') ;
DBMS_OUTPUT.PUT_LINE('---------------------------------') ;
FOR dept_cur IN (SELECT dname,build
FROM department
WHERE build IS NOT NULL
ORDER BY 1)
LOOP
DBMS_OUTPUT.PUT_LINE(dept_cur.dname||'-->'||dept_cur.build||'에 있습니다') ;
END LOOP ;
END ;
/
-> 학과명과 학과의 위치 안내 입니다
-------------------------------------
기계공학과 --> 기계실험관 에 있습니다
멀티미디어공학과 --> 멀티미디어관 에 있습니다
문헌정보학과 --> 인문관 에 있습니다
....
명시적 커서 처리 단계
1) 명시적 커서 선언 (Declaration)
PL/SQL의 선언부(DECLARE)에 다른 변수와 마찬가지로 선언되어야 하며, 형식은 아래와 같다
- 문법 :
CURSOR 커서명 IS 커서에 담고 싶은 내용을 가져오는 서브쿼리
- 커서명 : 명시적 커서의 이름
- 서브쿼리 : 처리하고자 하는 데이터를 검색하는 SELECT 문장을 이부분에 기술
2) 명시적 커서 오픈(OPEN)
선언 시 기술했던 서브쿼리를 수행해서 데이터를 커서로 가져 온다는 뜻.
- 문법 :
OPEN 커서 이름 ;
- 커서이름 : 열고자 하는 명시적 커서의 이름
3) 명시적 커서로부터 데이터 읽어서 변수로 할당하기(FETCH)
명시적 커서의 데이터들로부터 데이터를 한 건씩 읽어 변수로 할당하기 위해 FETCH문을 사용
- 문법 :
FETCH 커서 이름 INTO 변수
- 커서이름 : 읽어(FETCH) 오고자 하는 명시적 커서의 이름으로 반드시 OPEN 되어 있어야 FETCH 가능
- 변수들 : 명시적 커서로부터 읽어온(FETCH) 데이터 행(레코드) 을 PL/SQL 블록 내에서 처리하기 위해서
변수에 저장해서 사용.
4) 명시적 커서 닫기(CLOSE)
명시적 커서의 정리 작업을 하는 명령으로, 명시적 커서가 다 사용된 후에는 반드시 '닫기'를 해야 함.
커서를 닫는다 함은 작업이 끝난 메모리 공간을 반환하고 정리한다는 뜻
- 문법 :
CLOSE 커서 이름 ;
- 커서이름 : 닫고자 하는 명시적 커서의 이름으로 Close 된 명시적 커서는 더 이상 사용하지 않는다
사용 예)
SCOTT>DECLARE
vempno NUMBER(4) ;
vename VARCHAR2(20) ;
vsal NUMBER(7) ;
(커서 선언) CURSOR c1 IS
SELECT empno, ename, sal
FROM emp
WHERE deptno=20 ;
BEGIN
(커서 오픈) OPEN c1 ;
DBMS_OUTPUT.PUT_LINE('번호 이름 급여') ;
LOOP
(데이터 추출) FETCH c1 INTO vempno, vename, vsal ;
EXIT WHEN c1%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(vempno||' '||vename||' '||vsal) ;
END LOOP ;
(커서 닫기) CLOSE c1 ;
END ;
/
명시적 커서와 Cursor FOR LOOP 문 활용
문법 :
FOR record_name IN cursor_name LOOP
-- 명시적 커서의 OPEN, FETCH 가 자동적으로 수행됨.
statement1 ;
statement2 ;
....
END LOOP ; -- 루프문을 빠져 나갈 때 자동적으로 커서가 CLOSE 됨.
- record_name : 커서로부터 FETCH 된 하나의 레코드를 저장하기 위한 커서 레코드 변수, 자동으로 만들어짐
- cursor_name : 처리하고자 하는 명시적 커서명으로, 선언부에서 정의되어야 함.
명시적 CURSOR 와 CURSOR FOR LOOP 문 사용 예 :
emp 테이블에서 empno, ename 값을 가져와서 커서에 저장 후 출력하세요.
SCOTT>DECLARE
CURSOR emp_cur IS -- 커서 선언
SELECT empno, ename
FROM emp ;
BEGIN
FOR emp_rec IN emp_cur -- 커서의 데이터를 저장할 emp_rec 변수 선언
LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.empno||' '||emp_rec.ename) ;
END LOOP ;
END ;
/
파라미터 Explicit Cursor
- 명시적 커서를 선언 한 후 OPEN 할 때 값을 바꾸어서 수행해야 할 경우, 커서를 OPEN 할 때 필요한 값만 파라미터로
전달해서 반복 수행 할 수 있는 방법
문법 :
CURSOR cursor_name
[(parameter_name dataTYPE ,...)]
IS select-statement ;
- parameter_name : 파라미터 변수명으로 변수선언과 동일하며 여러 개의 파라미터를 지정할 수 있다
파라미터 Explicit Cursor 사용 예 :
SCOTT>DECLARE
CURSOR prof_cur (v_deptno IN NUMBER)
IS SELECT *
FROM professor
WHERE deptno=v_deptno ;
v_prof professor%ROWTYPE ;
BEGIN
DBMS_OUTPUT.PUT_LINE('============================') ;
DBMS_OUTPUT.PUT_LINE('101번 학과 교수님 번호와 이름 출력') ;
DBMS_OUTPUT.PUT_LINE('----------------------------') ;
OPEN prof_cur(101) ;
LOOP
FETCH prof_cur INTO v_prof ;
EXIT WHEN prof_cur%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(v_prof.profno||' 번 교수님 이름은 '||v_prof.name||' 입니다') ;
END LOOP ;
CLOSE prof_cur ;
DBMS_OUTPUT.PUT_LINE('============================') ;
DBMS_OUTPUT.PUT_LINE('102번 학과 교수님 번호와 이름 출력') ;
DBMS_OUTPUT.PUT_LINE('----------------------------') ;
OPEN prof_cur(102) ;
LOOP
FETCH prof_cur INTO v_prof ;
EXIT WHEN prof_cur%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(v_prof.profno||' 번 교수님 이름은 '||v_prof.name||' 입니다') ;
END LOOP ;
CLOSE prof_cur ;
END ;
/
Cursor 와 Select....FOR UPDATE 문장
명시적 커서를 선언해 데이터를 조회한 후 변경을 하려는 중에 다른 세션에서 현 세션이 작업하는 내용을 변경 할 수 있다.
예를 들어 조회 할 시점에 A라는 데이터였지만 조회나 변경하는 중에 다른 세션이 B 라는 데이터로 바꿀수 있다는 뜻
그래서 커서를 선언 할 때 커서에 있는 행들에 대해 잠금을 수행.
1) 기본 문법
이 실습을 위해 두 개의 세션에서 작업 실행
-세션 1- -세션 2-
SCOTT>SELECT empno,ename,sal SCOTT>UPDATE emp
FROM emp SET sal = 1000
WHERE empno = 7900 WHERE empno = 7900 ;
FOR update ; -> 대기 상태로 반응 없습니다.
EMPNO ENAME SAL
------ ------- ----
7900 JAMES 950
위와 같이 세션 1 에서 select 구문에 for update를 함께 쓰면 해당 데이터에 lock이 설정되어 다른 세션에서 dml 작업을 할 경우 대기상태로 들어감.
이 상태에서 세션 1 에서 commit 이나 rollback 명령을 수행하면 세션 2 에서 작업이 진행됨
2) Cursor 에서 SELECT... FOR update 사용하기
문법 :
SELECT ...
FROM ...
FOR UPDATE [OF column_reference][NOWAIT|WAIT n] ;
위 옵션중 NOWAIT 옵션은 다른 세션에서 행을 잠글 경우 즉시 오라클 에러를 발생 시켜주는 옵션.
다른 세션에서 해당 행에 LOCK 을 설정했을 경우 NOWAIT 옵션을 사용하면 즉시 에러를 발생시킴
그러나 만약 NOWAIT 옵션을 사용하지 않을 경우 LOCK 이 확보 될 때 까지 무한 대기상태
WAIT n = n자리에 초를 설정할 수 있으며 해당 초가 지난 후에도 LOCK을 확보할 수 없으며 에러발생
Cursor 에서 FOR UPDATE 구문 사용 예 :
DECLARE
CURSOR emp_cur IS
SELECT empno, ename, sal
FROM emp
WHERE deptno = 20
FOR UPDATE -- 커서 선언시에 FOR UPDATE 로 행을 잠금
NO WAIT ;
....
BEGIN
....
OPEN emp_cur ;
....
UPDATE emp
SET sal = sal * 2
WHERE CURRENT OF emp_cur -- 커서 선언 시에 잠긴 행 갱신
....
CLOSE emp_cur ;
END ;
'오라클 > PL/SQL' 카테고리의 다른 글
| PL/SQL sub program (0) | 2014.10.16 |
|---|---|
| PL/SQL 예외처리 (0) | 2014.10.16 |
| PL/SQL 제어문 (0) | 2014.10.13 |
| PL/SQL 변수 (0) | 2014.10.13 |
| PL/SQL 시작하기 (0) | 2014.10.13 |