[PL/SQL] 3. CURSOR

2017. 5. 14. 23:37

PL/SQL 커서

오라클 서버에서 사용자의 sql문장을 처리하는 모든 서버 프로세스는 sql문을 실행할 때마다 주어진 sql 문장의 처리(parse, execution) 를 위해 SGA 내부에 Context Area 라는 개별적인 메모리 공간을 사용한다.

Cursor란 Context Area라는 메모리 공간에 있는 데이터를 사람이 접근 할 수 있도록 연결해주는 일종의 연결통로이며 포인터라고 부르기도 한다.

즉 사용자가 요청하는 데이터를 데이터베이스 버퍼 캐쉬에서 PL/SQL이 사용하는 개별적인 메모리공간으로 복사 해 온 후(복사 한 데이터를 Actice Set이라함) 커서를 통해 Context Area 있는 Active Set 중에서 원하는 데이터에 접근한 후 필요한 데이터를 추출해(Fetch) PL/SQL 변수에 담고 후속 작업을 하게 된다는 뜻.

커서 종류에는 두가지와 각각의 속성들이 있다.

묵시적 커서(Implicit Cursor)

오라클이 자동적으로 선언하여 사용하고 자동으로 정리된다(clean up). 사용자 입장에서는 생성 유무를 알수 없고 PL/SQL블록 내에서의 SELECT문, DML문이 실행될 때마다 묵시적 커서가 선언된다. (세션 내 단 한개만이 선언되어 사용되다가 문장이 종료됨과 동시에 정리된다)

SQL%ROWCOUNT : 해당 커서에서 실행한 총 행의 개수를 반환(가장 마지막 행이 몇 번째 행인지 카운트)
SQL%FOUND : 해당 커서 안에 아직 수행해야 할 데이터가 있을 경우 TRUE값을 반환하고 없을 경우 FALSE값 반환
SQL%NOTFOUND : 해당 커서 안에 수행해야 할 데이터가 없을 경우 TRUE값을 반환하고 있을 경우 FALSE의값 반환
SQL%ISOPEN : 현재 묵시적 커서가 메모리에 OPEN 되어 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환

사용예

SCOTT>SELECT sal
2 FROM emp
3 ORDER BY 1;
SAL
-----
800
950
1100
1250
1250
1300
1500
1600
2450
 
 
SCOTT>DECLARE
2 BEGIN
3    delete emp
4    WHERE sal < 1000;
5      DBMS_OUTPUT.PUT_LINE('=====================================');
6      DBMS_OUTPUT.PUT_LINE('1000 LESS THEN : '||SQL%ROWCOUNT||' rows deleted.');
7
8    delete emp
9    WHERE sal between 1000 and 2000 ;
10     DBMS_OUTPUT.PUT_LINE('=====================================');
11     DBMS_OUTPUT.PUT_LINE('1000 - 2000 : '||SQL%ROWCOUNT||' rows deleted.');
12 END;
13 /
============================
1000 LESS THEN : 2 rows deleted.
============================
1000 - 2000 : 6 rows deleted.
PL/SQL procedure successfully completed.

SCOTT> DECLARE
2 BEGIN
3       DBMS_OUTPUT.PUT_LINE(' Information about department name and location');
4       DBMS_OUTPUT.PUT_LINE('-------------------------');
5     FOR dept IN ( SELECT dname , build
6     FROM department
7     WHERE build IS NOT NULL
8     ORDER BY 1)
9   LOOP
10      DBMS_OUTPUT.PUT_LINE(dept.dname||' ---> '|| dept.build);
11  END LOOP;
12 END ;
13 /
 
Information about department name and location
----------------------------------------------
Chemical Engineering ---> Chemical Experiment Bldg
Computer Engineering ---> Information Bldg
Electronic Engineering ---> Electronic Control Bldg
Library and Information science ---> College of Liberal Arts
Mechanical Engineering ---> Machining Experiment Bldg
Multimedia Engineering ---> Multimedia Bldg
Software Engineering ---> Software Bldg
PL/SQL procedure successfully completed.
 
PL/SQL procedure successfully completed.

명시적 커서(Explicit Cursor)

사용자가 선언하여 생성 후 사용하고 주로 여러 개의 행을 처리하고자 할 경우 사용한다. (변수에 데이터를 담기 이전에 사용되는것이므로 복합변수와 헷갈리면 안된다)
많은 데이터를 변수에 담으려면 복합변수를 써야 하는데 이때 명시적 커서를 사용해야 성능이 더 좋아진다.
묵시적 커서와는 다르게 동시에 여러 개가 선언되어 사용될 수 있다.

커서이름%ROWCOUNT : FETCH 문에 의해 읽혀진 데이터의 총 행 수(가장 마지막에 처리된 행이 몇 번째 인지를 반환)
커서이름%FOUND : FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환
커서이름%NOTFOUND : FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 없을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환
커서이름%ISOPEN : 명시적 커서가 메모리에 확보(선언)되어 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환

명시적 커서 처리단계

1. 명시적 커서 선언(Declaration)

CURSOR 커서명
IS
   커서에 담고 싶은 내용을 가져오는 서브쿼리

해당 커서를 사용 하겠다 라고 PL/SQL에 알려주는 역할만 하며 실제 메모리 할당이 이뤄지는 것은 아님.

2. 명시적 커서 열기(OPEN)

OPEN  커서_이름;

커서 선언 시 기술했던 서브쿼리를 수행해서 데이터를 커서로 가져 온다. 실제 메모리가 할당됨. 이때, 명시적 커서 영역에 자리잡은 데이터의 첫 번째 행에 커서 포인터가 설정되고 이 포인터 위치의 데이터 행을 다음 단계인 fetch에서 읽게됨

3. 명시적 커서로부터 데이터 읽어 변수로 할당하기(FETCH)

FETCH  커서_이름  INTO  변수

명시적 커서의 데이터들(Active Set)로부터 데이터를 한 건씩 읽어 변수로 할당하기 위해 FETCH문을 사용함. 읽게되는 데이터행은 포인터에 의해 지정되고 한 행에 FETCH되면 자동적으로 포인터는 다음 행으로 이동하게 됨.
보통 명시적 커서에는 데이터가 여러 건 들어 있기 대문에 많은 데이터들을 읽어 처리하기 위해 FETCH 문은 반복문과 함께 사용하는 경우가 많다.

4. 명시적 커서 닫기(CLOSE)

CLOSE  커서_이름;

작업이 끝난 메모리 공간을 반환하고 정리한다.

사용예
(emp 테이블에서 deptno 가 10번인 사람들의 empno , ename, sal 의 정보를 명시적 커서로 가져 온 후 변수에 저장하고 화면에 출력)

SCOTT> DECLARE
2     v_empno NUMBER(5) ;
3     v_ename VARCHAR2(30) ;
4     v_sal NUMBER(6) ;
5          CURSOR cur1 IS
6          SELECT empno , ename , sal
7          FROM emp
8          WHERE deptno=10 ;
9 BEGIN
10     OPEN cur1 ;
11 LOOP
12     FETCH cur1 INTO v_empno , v_ename , v_sal ;
13     EXIT WHEN cur1%NOTFOUND ;
14     DBMS_OUTPUT.PUT_LINE(v_empno||' '||v_ename||' '||v_sal) ;
15 END LOOP ;
16     CLOSE cur1 ;
17 END;
18 /
 
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
 
PL/SQL procedure successfully completed.

명시적 커서와 Cursor FOR LOOP 문 활용하기

사용자가 별도의 변수선언, OPEN , FETCH , CLOSE 문을 수행하지 않아도 됨.

FOR record_name IN cursor_name LOOP
   -- 명시적 커서의 OPEN, FETCH가 자동적으로 수행된다.
   statement1;
   statement2;
   ...
END LOOP;  -- 루프문을 빠져 나갈 때 자동적으로 커서가 CLOSE된다.