Oracle :: O'REILLY의 oracle pl/sql programmi REF CURSOR

아래는 O'REILLY의 oracle pl/sql programming을 정리한 것입니다.

ref cursor에 대해서 간단히 정리하면..
PL/SQL 테이블이나 프로그래머 정의 레코드에서와 같이 커서 변수를
생성하기 위해서는 두단계를 거쳐야 합니다.

1. 참조되는 커서 타입을 생성
2. 해당 유형기반의 실제 커서변수를 선언..

참조되는 커서형을 생성하는 구문은 아래와 같습니다.

TYPE cursor_type_name is ref cursor [RETURN return_type];

cursor_type_name은 커서형의 이름이고, return type은 커서형에 대한
return 데이터 스펙입니다. return_type은 일바적인 커서 return절에서
사용하는 유효한 데이터구조이며, %ROWTYPE속성을 이용하거나
앞에서 미리 정의된 레코드 type을 이용해서 정의할 수 있습니다.

RETURN절은 REF CURSOR형의 문장에서는 선택적입니다.
아래 두 정의 모두 가능합니다.
TYPE company_curtype is ref cursor return company%ROWTYPE;
TYPE company_curtype is ref cursor;

REF CURSOR문장의 첫번째 형태는 선언할 때에 레코드 형을 커서 변수
유형에 첨부하므로 강한유형?이라고 부릅니다. 이 유형으로 선언된
커서 변수는 SQL문이 지정된 레코드형과 매치되는 FETCH INTO 데이터구조만
사용할 수 있습니다. 강한 REF TYPE의 장점은 개발자가 지정한 커서
변수의 FETCH문과 커서 객체의 질의 리스트가 매치되는 지를 컴파일러가
확인할 수 있다는 것입니다.

RETURN절이 빠진 REF CURSOR문의 두번째 형태는 약한 유형?이라
부릅니다. 이 커서 변수형은 레코드 데이터구조와 관련이 없습니다. return
절 없이 선언된 커서변수는 강한 유형보다 더 유연하게 사용할 수 있습니다.
어떤 질의나 어떤 행 유형(rowtype) 구저에서도 사용될 수 있고, 프로그램이
진행중인 상황에서도 바꿀 수가 있습니다.

그러면.. 커서변수를 어떻게 선언하는지 봅니다.

커서변수를 선언하는 구분은..
cursor_name cursor_type_name;

cursor_name은 커서의 이름이고, cursor_type_name은 앞에서
type문으로 정의된 커서타입의 이름입니다.

만약 커서타입이 패키지로 설정되어 있다면
패키지이름.cursor_type_name이 됩니다.

##################################################
OTN 기술게시판을 보시면.. 잘 나온 자료가 있어서 드립니다.

No. 10933

CURSOR VARIABLE 이란?
=====================

PURPOSE

---

Cursor variable의 개념 및 사용방법에 대해 알아 봅니다.

Explanation

---

<사용환경>

* RDBMS V7.3(PL/SQL V2.3) 이후 사용 가능하다.
* RDBMS V7.2(PL/SQL V2.2)은 제한적으로 사용 가능하다.
- Define은 Server에서, Fetch는 반드시 client(Pro*c, OCI 등)에서
해야 한다.
- REF CURSOR type 정의 시 반드시 return type을 지정해야 한다.

<개념>

static cursor는 multi-row query의 결과를 work area에 담고 있는 것에
비해 cursor variable은 그 work area에 대한 pointer를 가지고 있는
형태이다.

<장점>

1. More Flexibility (PL/SQL V2.3부터 가능)
- query의 row type에 관계없이 cursor variable을 open할 수 있다.

2. Centralizing Data Retrieval
- 검색할 때에는 cursor variable을 parameter로 database의 stored
subprogram에 넘기면 그 subprogram에서 cursor를 open한다.

3. Reduce Network Traffic
- 여러 개의 cursor variable을 open하고 close하는 statement를 한
PL/SQL block에서 처리함으로써 network traffic을 줄일 수 있다.

<REF CURSOR Type의 정의>

TYPE ref_type_name IS REF CURSOR RETURN return_type;

- ref_type_name : 다음에 나올 cursor variable의 선언에 사용될 type
specifier
- return_type : database table의 row나 record type

예 ) DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
TYPE GenericCurTyp IS REF CURSOR; -- return type이 없음(more
flexible)
-- PL/SQL V2.3부터 가능

<Cursor Variable의 선언>

일단 REF CURSOR type을 정의하면 Cursor variable을 선언할 수 있다.

예 ) DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
dept_cv DeptCurTyp; -- cursor variable을 선언

Cursor variable은 persistent state를 유지할 수 없으므로 package내에서
는 선언할 수 없다.

<Cursor Variable의 제어>

Cursor variable을 제어하기 위해서는 3단계 즉 OPEN-FOR, FETCH, CLOSE를
거친다.

OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;

FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable_name[, variable_name]... | record_name};

CLOSE {cursor_variable_name | :host_cursor_variable_name);

Example

---

---

선언 및 OPEN 부분

---

CREATE PACKAGE emp_data AS
TYPE GenericCurTyp IS REF CURSOR;
PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,
choice IN NUMBER);
END emp_data;

CREATE PACKAGE BODY emp_data AS
PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,
choice IN NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
END IF;
END open_cv;
END emp_data;

---

FETCH 부분

---

CREATE PROCEDURE call_cursor(table_no IN NUMBER) IS
generic_cv emp_data.GenericCurTyp;
EmpRec emp%ROWTYPE;
DeptRec dept%ROWTYPE;
BEGIN
emp_data.open_cv(generic_cv, table_no);
LOOP
FETCH generic_cv INTO EmpRec;
EXIT WHEN generic_cv%NOTFOUND;
dbms_output.put_line(EmpRec.ename);
END LOOP;
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN
LOOP
FETCH generic_cv INTO DeptRec;
EXIT WHEN generic_cv%NOTFOUND;
dbms_output.put_line(DeptRec.dname);
END LOOP;
END;