DBMS_SQL 패키지를 이용한 REF CURSOR

DBMS_SQL 패키지를 이용한 REF CURSOR

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=579

 

DBMS_SQL 패키지를 이용한 REF CURSOR

DBMS_SQL 패키지를 이용한 REF CURSOR5.7 DBMS_SQL 패키지를 이용한 REF CURSORn ORACLE 11g이후 DBMS_SQL과 Native Dynamic SQL은 2개의 새로운 API를 통해 DBMS_SQL의 CURSOR를 REF CURSOR로 변경할 수 있다.DBMS_SQL.TO_REFCURSORn DBMS_SQL을 이용하여 만든…

ojc.asia

5.7 DBMS_SQL 패키지를 이용한 REF CURSOR

n ORACLE 11g이후 DBMS_SQL과 Native Dynamic SQL은 2개의 새로운 API를 통해 DBMS_SQL의 CURSOR를 REF CURSOR로 변경할 수 있다.

DBMS_SQL.TO_REFCURSOR

n DBMS_SQL을 이용하여 만든 커서를 참조커서(REF CURSOR)로 변환한다.

-- 아래 예제는 EMP 테이블에서 입력한 부서코드의 사원을 출력하는 예제이다. SQL> SET SERVEROUTPUT ON SQL> ACCEPT p_deptno PROMPT 'Enter the Deptno:' SQL> DECLARE -- 테이블타입은 데이터를 배열처럼 다룰 수 있는 타입인데 -- 배열첨자를 위해 4바이트 정수형 PLS_INTEGER를 사용했다. TYPE emp_record IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER; v_emps emp_record; v_sql CLOB; v_cursor PLS_INTEGER; v_rc SYS_REFCURSOR; --REF CURSOR형 v_execute PLS_INTEGER; BEGIN v_cursor := DBMS_SQL.OPEN_CURSOR; v_sql := 'SELECT * FROM emp WHERE deptno = :deptno'; -- Dynamic SQL문 파싱 DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE); -- 바인드변수 바인딩 DBMS_SQL.BIND_VARIABLE(v_cursor, 'DEPTNO', '&p_deptno'); -- SQL문 실행 v_execute := DBMS_SQL.EXECUTE(v_cursor); -- REF CURSOR로 변환 v_rc := DBMS_SQL.TO_REFCURSOR(v_cursor); -- REF CURSOR에서 데이터 추출 LOOP FETCH v_rc BULK COLLECT INTO v_emps LIMIT 100; FOR i IN 1 .. v_emps.COUNT LOOP DBMS_OUTPUT.PUT_LINE(v_emps(i).ename || v_emps(i).sal); END LOOP; EXIT WHEN v_rc%NOTFOUND; END LOOP; CLOSE v_rc; END; / 'Enter the Deptno:: 20 구 19: DBMS_SQL.BIND_VARIABLE(v_cursor, 'DEPTNO', '&deptno'); 신 19: DBMS_SQL.BIND_VARIABLE(v_cursor, 'DEPTNO', '20'); SMITH800 …… FORD3000 -- 이번에는 REF CURSOR를 리턴해 보자. -- 아래처럼 함수의 리턴타입을 SYS_REFCURSOR로 하고 v_rc 변수의 타입도 SYS_REFCURSOR로 주면 된다. SQL> CREATE OR REPLACE FUNCTION emplist (p_deptno emp.deptno%TYPE) RETURN SYS_REFCURSOR IS v_sql CLOB; v_cursor PLS_INTEGER; v_rc SYS_REFCURSOR; --REF CURSOR형 v_execute PLS_INTEGER; BEGIN v_cursor := DBMS_SQL.OPEN_CURSOR; v_sql :='SELECT * FROM emp WHERE deptno = :deptno'; -- Dynamic SQL문 파싱 DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE); -- 바인드변수 바인딩 DBMS_SQL.BIND_VARIABLE(v_cursor, 'DEPTNO', p_deptno); -- SQL문 실행 v_execute := DBMS_SQL.EXECUTE(v_cursor); -- REF CURSOR로 변환 v_rc := DBMS_SQL.TO_REFCURSOR(v_cursor); return v_rc; END; / 함수가 생성되었습니다. SQL> set autoprint on SQL> variable refcur REFCURSOR SQL> BEGIN :refcur := emplist(10); END; / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------------------------------------------------------------------------------------------------- 7782 CLARK MANAGER 7839 81/06/09 3404.4 10 7839 KING PRESIDENT 81/11/17 6946.8 10 7934 MILLER CLERK 7782 82/01/23 2257.5 10