[Oracle] PL/SQL 기초3 - 커서

 [Oracle] PL/SQL 기초3 - 커서 (CURSOR)

안녕하세요. 갓대희 입니다. 이번 포스팅은 [ PL / SQL 커서 ] 입니다. : ) 

개인적으론 PL / SQL 의 꽃은 "커서"가 아닐까 생각합니다.

커서 (CURSOR)

[정의]

 - SQL 커서는 Oracle 서버에서 할당한 전용 메모리 영역에 대한 포인터이다.

 - 질의의 결과로 얻어진 여러 행이 저장된 메모리상의 위치.

 - 커서는 SELECT 문의 결과 집합을 처리하는데 사용된다.

※ 사전적 정의만 보자면 전혀 이해가 안가는 부분이 많다. 그래도 일단 한번 보고 넘어가자.

사전적 정의 필요없이 맨밑의 예제부터 확인 해보아도 무방할 듯 하다.

[종류]

1) 암시적 커서 (Implicit Cursor)

1.1) 정의

 - 오라클 DB에서 실행되는 모든 SQL문장은 암시적인 커서가 생성되며, 커서 속성을 사용 할 수 있다.

 - 모든 DML과 PL/SQL SELECT문에 대해 선언됨

 

 - 암시적인 커서는 오라클이나 PL/SQL실행 메커니즘에 의해 처리되는 SQL문장이 처리되는 곳에 대한 익명의 주소이다.

 - Oracle 서버에서 SQL문을 처리하기 위해 내부적으로 생성하고 관리한다.

 - 암시적 커서는 SQL 문이 실행되는 순간 자동으로 OPEN과 CLOSE를 실행 한다.

 - SQL 커서 속성을 사용하면 SQL문의 결과를 테스트할 수 있다.

1.2) 암시적 커서 속성

 - SQL%FOUND     : 해당 SQL문에 의해 반환된 총 행수가 1개 이상일 경우TRUE (BOOLEAN)

 - SQL%NOTFOUND : 해당 SQL문에 의해 반환된 총 행수가 없을 경우 TRUE (BOOLEAN)

 - SQL%ISOPEN     : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색( PL/SQL은 실행 후 바로 묵시적 커서를 닫기 때문에 항상 상 false)

 - SQL%ROWCOUNT : 해당 SQL문에 의해 반환된 총 행수, 가장 최근 수행된 SQL문에 의해 영향을 받은 행의 갯수(정수)

[사용 예제]

EX) 

1. SET SERVEROUTPUT ON;
2. DECLARE
3.     BEGIN
4.     DELETE FROM emp WHERE DEPTNO = 10;
5.     DBMS_OUTPUT.PUT_LINE('처리 건수 : ' || TO_CHAR(SQL%ROWCOUNT)|| '건');
6.     END;
7.     
8. -- 결과 
9. 처리 건수 : 21건
10. PL/SQL 처리가 정상적으로 완료되었습니다.

2) 명시적 커서 (Explicit Cursor)

2.1) 정의

 - 프로그래머에 의해 선언되며 이름 있는 커서.

2.2) 명시적 커서 속성

   - %ROWCOUNT : 현재까지 반환된 모든 행의 수를 출력

   - %FOUND : FETCH한 데이터가 행을 반환하면 TRUE

   - %NOTFOUND : FETCH한 데이터가 행을 반환하지 않으면 TRUE (LOOP를 종료할 시점을 찾는다)

   - %ISOPEN : 커서가 OPEN되어있으면 TRUE

2.3) 문법

 - DECLARE를 통해서 명명된 SQL 영역을 생성

 - OPEN을 이용하여 결과 행 집합을 식별

 - FETCH를 통해서 현재 행을 변수에 로드 (이를 현재 행이 없을 때까지 수행할 수 있다)

 - CLOSE를 통해서 결과 행 집합을 해제

---

DECLARE

CURSOR [커서이름] IS [SELECT 구문];

BEGIN

OPEN [커서이름];

FETCH [커서이름] INTO [로컬변수];

CLOSE [커서이름];

END;

---

OPEN(커서열기)

 - OPEN문을 사용하여 커서를 연다.

 - 커서안의 검색이 실행되며 아무런 데이터행을 추출하지 못해도 에러가 발생하지 않는다

FETCH(커서패치)

 - 현재 데이터 행을 OUTPUT변수에 반환한다

 - 커서의 SELECT문의 컬럼의 수와 OUTPUT변수의 수가 동일해야 한다 

 - 커서 컬럼의 변수타입과 OUTPUT변수의 데이터 타입도 역시 동일해야 한다

 - 커서는 한 라인씩 데이터를 FETCH한다

 - 문법 : FETCH cursor_name INTO variable1, variable2;

CLOSE(커서닫기)

 - 사용을 마친 커서는 반드시 닫아주어야 한다

 - 필요시 커서를 다시 열 수 있다

 - 커서를 닫은 상태에서 FETCH는 불가능하다

 - 문법 : CLOSE cursor_name;

[사용 예제]

EX) 커서 사용 예제1

1. SET SERVEROUTPUT ON;
2. DECLARE
3.     CURSOR emp_cur -- 커서정의
4.     IS
5.     SELECT * FROM emp WHERE DEPTNO = 10;
6.     emp_rec emp%ROWTYPE; -- 변수정의
7. BEGIN
8.     OPEN emp_cur;
9.     LOOP -- 반복
10.     FETCH emp_cur INTO emp_rec; -- 하나씩 변수에 넣기
11.     EXIT  WHEN emp_cur%NOTFOUND; -- 더이상 없으면 끝내기
12.         DMBS_OUTPUT.PUT_LINE(emp_rec.empno || ' ' || emp_rec.name); -- 출력
13.     END LOOP;
14.     CLOSE emp_cur;
15. END;

EX) 커서 사용 예제2

1. SET SERVEROUTPUT ON;
2. DECLARE
3.     ID_LIST SYS_REFCURSOR; -- 커서정의
4.     I_ID VARCHAR2(100); -- 변수정의
5. BEGIN
6.     OPEN ID_LIST;
7.     FOR
8.         SELECT USER_ID FROM MY_USER WHERE 조건;
9.     LOOP -- 반복
10.     FETCH ID_LIST INTO I_ID; -- 하나씩 변수에 넣기
11.     EXIT  WHEN ID_LIST%NOTFOUND; -- 더이상 없으면 끝내기
12.         DMBS_OUTPUT.PUT_LINE(I_ID); -- 출력
13.     END LOOP;
14.     CLOSE ID_LIST;
15. END;

EX) 커서 사용 예제3

1. SET SERVEROUTPUT ON;
2. DECLARE
3.     CURSOR ID_LIST IS
4.     SELECT 'GOD' AS USER_ID FROM DUAL;
5. BEGIN
6.     FOR TEST_CURSOR IN ID_LIST
7.     LOOP
8.         DBMS_OUTPUT.putline(TEST_CURSOR.USER_ID);
9.     END LOOP;
10. END;

[명시적 커서 FOR LOOP]

가장 내가 많이 사용하는 방법이다.

서브쿼리를 활용하여 CURSUR FOR LOOP를 사용하면 CURSOR를 선언할 필요도 없어진다.

난 거의 이방법 밖에 안쓰고, 다른방법은 사실 사용하는 법도 잊어 버렸다.

명시적 커서 FOR LOOP를 사용하면 

FOR LOOP가 자동적으로 커서를 OPEN해주며, 행이 없을 때까지 FETCH해주고, CLOSE해준다.

또한 ROWTYPE에 해당하는 변수를 따로 DECLARE할 필요가 없다. 

이는 암시적으로 선언되기 때문이다.

물론, 이 암시적 카운터는 FOR LOOP안에서만 사용할 수 있다.

그리고 이 커서의 데이터 타입(컬럼 데이터 타입의 집합)도 %ROWTYPE 앞으로 이용할 수 있다.

EX) 예제3을 "명시적 커서 FOR LOOP" 방식으로 변경 해보았다.

1. SET SERVEROUTPUT ON;
2. DECLARE
3. BEGIN
4.     FOR ID_LIST IN 
5.     (
6.         SELECT 'GOD' AS USER_ID FROM DUAL
7.     )
8.     LOOP
9.     DBMS_OUTPUT.putline(ID_LIST.USER_ID);
10. END LOOP;
11. END;
12. 
    

※ CURSOR FOR LOOP은 내부적으로 처리되는 데이터의 양, I/O 측면에서 훨씬 효율적이기 때문에, 가급적 이를 사용하는 것이 좋다.

---

이전 글

2018/05/24 - [4. Database/4_3 Oracle 기본] - [Oracle] PL/SQL 기초 (정의, 특징, 사용방법, 변수선언 방법)

2018/06/03 - [4. Database/4_3 Oracle 기본] - [Oracle] PL/SQL 기초2 - 반복문

---