[Oracle/기본]21. 명시적 커서 작성

 

● 커서

 

오라클 서버는 SQL 문장을 실행하고 처리한 정보를 저장하기 위해 Private SQL 영역이라 불리는 작업 영역을 사용한다.

Private SQL 영역에 이름을 붙이고 그것의 저장된 정보를 액세스하기 위해 PL/SQL커서를 사용할 수 있다.

 

- 암시적 커서 : 모든 DML과 PL/SQL SELECT 문장에 대해 선언된다.

- 명시적 커서 : 프로그래머에 의해 선언되고 이름 붙여진다.

 

커서는 PL/SQL에 내장된 일종의 포인터이며, 데이터베이스 쿼리와 레코드들의 세트를 얻는 작업, 그리고 결과셋의 행들을 하나씩 얻는 작업에 이용될 수 있다. 즉, 커서는 데이터에 대한 프로그래밍적인 접근을 가능하게 해준다.

---

[명시적 커서]

명시적 커서는 커서를 관리하는데 필요한 PL/SQL코드를 PL/SQL 프로그래머가 직접 작성하는 커서이다.

DECLARE

vempno number(4);

vename varchar2(20);

vsal number(7, 2);

CURSOR c1 is

SELECT empno, ename, sal -- 반드시 SELECT 사용 (INTO 절이 없는 SELECT 사용한다.)

  FROM  emp

 WHERE deptno = 20;

BEGIN 

OPEN c1;

dbms_output.put_line('empno ename slal');

LOOP

FETCH c1 INTO vempno, vename, vsal;

EXIT WHEN c1%NOTFOUND;

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

END LOOP;

CLOSE c1;

END;

 

- 질의에 의해 식별된 행은 active set이라 불리고, 인출(fetch)가능하다.

   리턴된 행의 집합은 result set 이라 한다.

- FETCH 문장의 역할

   ☞ 1. active set에서 다음 행으로 포인터를 움직인다.

   ☞ 2. 현재 행에 대한 정보를 출력 PL/SQL 변수로 읽어들인다.

   ☞ 3. 포인터가 active set의 끝에 위치하게 되면 커서는 FOR LOOP를 빠져나간다.

---

1) 커서를 선언한다 

cursor c1 is

2) 커서를 오픈한다. (result set을 생성하고 첫 번째 행 앞에 커서(포인터)를 위치시킨다.)

open c1;

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

fetch c1

4)커서를 닫는다.

close c1;

---

[암시적 커서]

암시적 커서는 커서를 처리하는 코드를 직접 작성할 필요가 없다. 암시적 커서를 이용할 때 역시 레코드를 이용한 작업이 가능하지만 커서의 라이프 사이클을 관리하는 코드를 직접 작성하지 않아도 된다.

declare

begin

for my_dept_rec in (select department_id, department_name

from departments order by 1)

loop

dbms_output.put('Department : ' || my_dept_rec.department_id);

dbms_output.put_line(' is named '|| my_dept_rec.department_name);

end loop;

end;

---

%FOUND : 가장 최근의 인출(fetch)이 행을 리턴하면 TRUE를 리턴한다.

%NOTFOUND : 가장 최근의 인출(fetch)이 행을 리턴하지 않으면 TRUE를 리턴한다.

%ROWCOUNT : 지금까지 리턴된 행의 총 수를 리턴한다.

%ISOPEN : 커서가 열렸으면, 아직 닫히지 않은 상태이면 TRUE를 리턴한다.

 

[ 출처 및 참고 ]  http://psh85a.tistory.com/entry/Oracle-%EB%AA%85%EC%8B%9C%EC%A0%81-%EC%BB%A4%EC%84%9C%EC%99%80-%EC%95%94%EC%8B%9C%EC%A0%81-%EC%BB%A4%EC%84%9C

 

● 커서 FOR 루프

 

DECLARE

    CURSOR c1 IS

         SELECT empno, ename

            FROM emp;

BEGIN

        FOR emp_record IN c1 LOOP

                      -- implicit open and implicit fetch occur

             IF emp_record.empno = 7839 THEN

             ...

        END LOOP; --implicit close occurs

END;

더 이상 남겨진 것이 없을 때 까지 종업원을 하나씩 읽어 들입니다.

 

 

- 커서 FOR 루프문은 OPEN, FETCH, CLOSE가 자동이다.

 

● 서브쿼리를 사용한 커서 FOR 루프

 

BEGIN

    FOR emp_record IN (SELECT empno, ename

                                       FROM emp) LOOP

                  -- implicit open and implicit fetch occur

        IF emp_record.empno = 7839 THEN

            ...

    END LOOP; --implicit close occurs

 END;

 

커서를 선언할 필요가 없다.