PL/SQL 커서(CURSOR) (6)

1. PL/SQL 커서란

 

커서란 특정 SQL 문장을 처리한 결과를 담고있는 메모리 영역을 가리키는 일종의 포인터입니다.

대부분의 SQL문 결과 ROW는 여러개인데 커서를 사용하면 이 ROW에 순차적으로 접근이 가능합니다.

커서의 종류에는 묵시적 커서와 명시적 커서가 있습니다.

묵시적 커서는 오라클 내부에서 자동으로 생성되어 SQL문장이 실행될때마다 자동으로 만들어져 실행되는 커서이고,

명시적 커서는 사용자가 직접 정의해서 사용하는 커서를 말합니다

 

 

SQL커서는 크게 묵시적 커서(Implicit Cursor)와 명시적 커서(Explicit Cursor)로 나눌 수 있다.

 

묵시적 커서는 필요한 경우 오라클이 자동적으로 선언하여 사용한 후 자동으로 정리(clean-up) 한다.

즉, 사용자가 생성을 하지 않아도 자동으로 생성 된다는 의미이다.

 

반면, 명시적 커서는 사용자가 정의한 커서를 선언하여 사용하고, 커서의 사용이 끝난 후에는 별도의 정리(Clean-up) 작업을 수행해줘야한다.

 

즉, 묵시적 커서는 자동으로 생성되고 명시적 커서는 사용자가 만들어야 한다는 뜻!

 

 

 

2. 묵시적 커서(Implicit Cursor)

묵시적 커서는 오라클에서 자동적으로 선언해주는 SQL 커서로서, 사용자 입장에서는 생성 유무를 알 수 없다.

기본적으로 PL/SQL 블록 내에서의 SELECT문 , DML(INSERT, UPDATE, DELETE)문이 실행될 때마다 묵시적 커서가 선언된다.

단, 주의해야 할 점은 묵시적 커서의 경우 세션 내에 단 한개만이 선언되어 사용되었다가 문장이 종료됨가 동시에 정리된다는 점이다.

 

그리고 묵시적 커서에 저장되는 데이터는 1행만 가능하다. 즉, 여러 행을 작업해야 할 경우에는 묵시적 커서를 사용할 수 없다는 뜻. 이때에는 명시적 커서를 사용해야 한다.

 

사용자는 커서 내의 실행결과를 커서 속성을 통해 확인할 수 있다.

묵시적 커서 속성은 총 4가지가 있는데, 묵시적 커서라는 뜻의 SQL%을 접두어로 사용하게 된다.

 

* 묵시적 커서 속성

- SQL%ROWCOUNT

해당 커서에서 실행한 총 행의 개수를 반환(가장 마지막 행이 몇 번째인지 카운트)

 

- SQL%FOUND

해당 커서 안에 아직 수행해야 할 데이터가 있을 경우 TRUE 값을 반환하고 없을 경우 FALSE값을 반환하는 속성

 

- SQL%NOTFOUND

위와 반대 데이터가 없을 경우 TRUE, 있을경우 FALSE 반환

 

- SQL%ISOPEN

현재 묵시적 커서가 메모리에 Open되어 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE 값을 가지는 속성

 

 

3. 명시적 커서(Explicit Cursor)

명시적 커서는 사용자가 선언하여 생성 후 사용하는 SQL 커서로, 주로 여러 개의 행을 처리하고자 할 경우 사용

만약 여러 건을 검색하는 SELECT 문장에서 묵시적 커서를 사용할 경우 오라클은 예외사항을 발생 시킴

 

명시적 커서는 묵시적 커서와는 다르게 동시에 여러 개가 선언되어 사용될 수 있다.

또한 묵시적 커서와 마찬가지로 커서 속성 변수로 커서의 내용을 파악하고 보다 쉽게 작업할 수 있게 해줌

 

명시적 커서는 여러개가 선언 될 수 있으므로, 커서 속성 변수는 '커서명%'을 커서 속성변수의 접두어로 붙여서 사용한다.

 

*명시적 커서 속성

- 커서이름%ROWCOUNT

FETCH문에 의해 읽혀진 데이터의 총 행수를 가지는 속성.

가장 마지막에 처리된 행이 몇 번째인지를 반환해준다.

 

- 커서이름%FOUND

FETCH문이 수행됐을 경우, 읽혀진 행이 있을 경우에는 TRUE , 그렇지 않을 경우엔 FALSE 값을 가진다.

 

- 커서이름%NOTFOUND

위와 반대 , 읽혀진 행이 없을 경우 TRUE, 있을 경우 FALSE

 

- 커서이름%ISOPEN

명시적 커서가 메모리에 확보(선언)되어 있을 경우에는 TRUE, 그렇지 않을 경우 FALSE

 

 

커서(CURSOR)의 흐름도

커서 선언 -> 커서 열기(변수로 할당(Fetch)) -> 커서 사용 -> 커서 닫기

 

 

4. 명시적 커서 처리 단계

 

4.1 명시적 커서 선언 (DECLARE CURSOR)

 

명시적으로 CURSOR를 선언하기 위해 CURSOR 문장을 사용한다.

CURSOR cursor_name
IS
select_statement;

cursor_name :

커서명, 명시적 커서의 이름으로 유일하게 명명되어야 한다.

select_statement :

기본적으로 명시적 커서는 여러 건을 검색하는 SELECT 문을 처리하기 위한 것이므로 처리하고자 하는 데이터를 검색하는 SELECT 문장을 기술한다.(커서에 담고 싶은 내용을 가져오는 서브 쿼리)

 

 

 

4.2 명시적 커서 열기(OPEN)

 

명시적 커서를 OPEN한다는 뜻은 커서 선언 시 기술했던 서브 쿼리를 수행해서 데이터를 커서로 가져온다는 뜻.

메모리에 실제 커서가 사용할 메모리 공간이 할당된다.

 

즉, 커서를 선언할 때는 사용될 메모리 공간의 양을 모르기 때문에 메모리를 할당할 수 없지만 커서가 open이 되면 실제 메모리가 할당이 된다.

이때 명시적 커서 영역에 자리잡은 데이터의 첫번째 행에 커서 포인터가 설정되고, 이 포인터 위치의 데이터행을 다음 단계인 FETCH에서 읽게 되는 것

 

 

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

 

명시적 커서의 데이터들로부터 데이터를 한 건씩 읽어 변수로 할당하기 위해 FETCH문을 사용한다.

이때 읽게 되는 데이터 행은 포인터에 의해서 지정되며, 한 행이 Fetch되면 자동적으로 포인터는 다음 행으로 이동하게 된다.

일반적으로 명시적 커서에는 데이터가 여러 건 들어 있기 때문에 많은 데이터를 처리하기 위해서 FETCH 문은 반복문과 함께 사용하는 경우가 많다.

FETCH문도 FETCH후에 변수에 값을 할당하기 위해 INTO절을 사용한다.

FETCH 커서_이름 INTO 변수들;
* 커서_이름
읽어오고자(fetch) 하는 명시적 커서의 이름으로 반드시 open되어 있어야 FETCH가 가능하다.
*변수들
명시적 커서로부터 읽어온 데이터 행(레코드)을 PL/SQL 블록 내에서 처리하기위해 변수에 저장해서 사용
단순 변수와 복합 변수가 올 수 있으며, 선언부에 선언된 변수만 올 수 있다.
단순 변수를 사용한다면, 커서에서 정의된 SELECT 리스트의 개수만큼 선언하고 SELECT 리스트의 위치대로 FETCH의 INTO절에 차례대로 적어야한다.
복합 변수를 사용한다면, 커서 레코드 변수(커서명%ROWTYPE)을 선언하여 사용한다.

 

 

4.4 명시적 커서 닫기

 

명시적 커서의 정리 작업을 하는 명령, 명시적 커서가 다 사용된 후에는 반드시 '닫기'를 해야한다.

커서 닫기를 하지 않게 되면 메모리 낭비도 많이 되고 동일한 커서를 다른 PL/SQL 블록에서 동일한 이름의 커서를 사용할 경우 에러가 생기게 된다.

CLOSE 커서_이름;