레이블이 커서인 게시물을 표시합니다. 모든 게시물 표시
레이블이 커서인 게시물을 표시합니다. 모든 게시물 표시

2012-10-07

DBMS/오라클]오라클_커서_사용법


원본 출처 :: http://netme.kr/bbs/board.php?bo_table=sub_2011&wr_id=23


1.  커서의  정의

커서란  SQL  Plus에서  사용자가  실행한  SQL문의  단위를  의미합니다.
오라클렝서  수행한  모든  쿼리문은  커서  단위로  처리합니다.

PL/SQL의  SQL문처럼  하나의  결과를  리턴하는  경우  커서  없이도  SQL문의  실행결과가 
암시적으로  커서에  저장되므로  이를  암시적  커서라고  합니다.

SQL문을  수행한  후에  결과로  얻어지는  행이  여러  개일  경우에는  암시적인  커서에  정보를 
저장할  수  없기에  에러가  발생합니다.  이럴  경우에는  반드시  명시적인  커서를  사용해야  합니다.

명시적인  커서는  PL/SQL의  레코드(RECORD)와  PL/SQL의  테이블(TABLE)을  결합한  것으로서
프로그램  언어의  구조체  배열과  유사합니다.



[커서의  사용]

1)  커서를  선언한다.
CURSOR  cur_name

2)  커서를  오픈한다.
OPEN  cur_name

3)  커서에  조회한  결과를  인출해  지정한다.
FECTCH  cur_name  ...

4)  커서를  닫는다
CLOSE  cur_name






2.  20번  부서에  근무하는  사원의  정보를  출력하는  예제


ed  cur01


SET  SERVEROUTPUT  ON
declare
   vempno   NUMBER(4);
   vename   VARCHAR2(20);
   vsal   NUMBER(7,  2);

   CURSOR   C1
   IS
   select  empno,  ename,  sal
   from  emp
   where  deptno=20;

begin
   OPEN  C1;

   dbms_output.put_line('empno   ename   sal');

   LOOP
      FETCH  C1  INTO  vempno,  vename,  vsal;
      EXIT  WHEN  C1%NOTFOUND;

      dbms_output.put_line(to_char(vempno)||'   '||vename||'   '||to_char(vsal));
   END  LOOP;


end;
/



저장하고  실행합니다.

@cur01
empno   ename   sal
7369   SMITH   800
...
...
...
...
...

PL/SQL  procedure  successfully  completed.





3.  OPEN-FETCH-CLOSE가  없이  커서  처리

ed  cur02


SET  SERVEROUTPUT  ON
declare
   vemp   emp%ROWTYPE;

   CURSOR   C1
   IS
   select  empno,  ename,  sal
   from  emp
   where  deptno=20;

begin
   dbms_output.put_line('empno   ename   sal');

   FOR   vemp   IN   C1   LOOP
      EXIT  WHEN  C1%NOTFOUND;

      dbms_output.put_line(to_char(vemp.empno)||'   '||vemp.ename||'   '||to_char(vemp.sal));
   END  LOOP;

end;
/


정장하고  실행합니다.

@cur02
empno   ename   sal
7369   SMITH   800
...
...
...
...
...

PL/SQL  procedure  successfully  completed.



4.  커서의  상태

%NOTFOUND   커서  영역의  자료가  모두  FETCH  됬는가를  알려줌
%FOUND      커서  영역에  FETCH가  되지  않은  자료가  있는가를  알려줌
%ISOPEN      커서가  OPEN된  상태인가를  알려줌
%ROWCOUNT   FETCH된  RECORD가  몇  개  있는지  알려줌

cur02  예제를  수정합니다.

ed  cur02

SET  SERVEROUTPUT  ON
declare
   vemp   emp%ROWTYPE;

   CURSOR   C1
   IS
   select  empno,  ename,  sal
   from  emp
   where  deptno=20;

begin
   dbms_output.put_line('empno   ename   sal   record  count');

   FOR   vemp   IN   C1   LOOP
      EXIT  WHEN  C1%NOTFOUND;

      dbms_output.put_line(to_char(vemp.empno)||'   '||vemp.ename||'   '||to_char(vemp.sal)||'   '||C1%ROWCOUNT);
   END  LOOP;

end;
/


저장하고  실행합니다.

@cur02

결과는  record  count  추가되었습니다.




5.  커서를  활용한  실용  예제
1)  급여  총합을  구하는  예제
ed  cur03

SET  SERVEROUTPUT  ON
declare
   tot   NUMBER  :=  0;

   CURSOR   emp_cursor
   IS
   select  ename,  sal
   from  emp;

begin
   dbms_output.put_line('name   sal');
   dbms_output.put_line('------------------------------------------');

   FOR  cur_var  IN  emp_cursor  LOOP
      tot  :=  tot  +  cur_var.sal;
      dbms_output.put_line(cur_var.ename);
      dbms_output.put_line('-   '||cur_var.sal);
   END  LOOP;
  
   dbms_output.put_line('------------------------------------------');
   dbms_output.put_line('-   '||tot);
end;
/


저장하고  실행합니다

@cur03




2)  사원별  급여  현황을  그래포로  표현
ed  cur04

SET  SERVEROUTPUT  ON
declare
   CURSOR   emp_cursor
   IS
   select  ename,  sal
   from  emp
   order  by  sal  desc;

   star   varchar2(100);
   cnt   number  :=  0;

begin
   dbms_output.put_line('   sal  of  emp');
   dbms_output.put_line('------------------------------------------');

   FOR  cur_var  IN  emp_cursor  LOOP
      star  :=  NULL;
      cnt  :=  round(cur_var.sal/100,  0);

      for  i  in  1..  cnt  loop
         star  :=  star||'*';
      end  loop;

   dbms_output.put_line(cur_var.ename);
   dbms_output.put_line('-   '||star||'('||cur_var.sal||')');
   END  LOOP;
end;
/


저장하고  실행합니다

@cur04

2012-10-06

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





명시적 커서와 묵시적 커서에 대한 설명 문서 : 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 명령문에서 커서 속성을 직접 참조할 수 없음
구 분타입내 용
%ROWCOUNTNumber·현재까지 FETCH된 행의 갯수
%FOUNDBoolean·FETCH할 행이 존재할 경우 값은 TRUE
%NOTFOUNDBoolean·FETCH할 올 행이 없을 경우 값은 TRUE
%ISOPENBoolean·커서가 오픈되어 있을 경우 값은 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;
SALCOMM
-----------------
1600300
12501400
2850
15000
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)

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)