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

오라클참조커서, (REF CURSOR)

오라클참조커서, (REF CURSOR)

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

 

오라클참조커서, (REF CURSOR)

오라클참조커서, (REF CURSOR)5.6 참조커서(REF CURSOR) n 일반 커서는 정적이며 커서가 한번 SQL 영역을 가리키면 가리키는 곳을 변경 못한다. 하지만 참조커서(REF CURSOR)는 가능하다. SQL> Declare Type refcur is ref cursor; cursor c is select * from dual; myCur…

ojc.asia

5.6 참조커서(REF CURSOR)

n 일반 커서는 정적이며 커서가 한번 SQL 영역을 가리키면 가리키는 곳을 변경 못한다. 하지만 참조커서(REF CURSOR)는 가능하다.

SQL> Declare Type refcur is ref cursor; cursor c is select * from dual; myCursor refCur; begin //myCur는 ref cursor if (to_char(sysdate,’dd’) = 30) then open myCur for select * from emp; elsif ((to_char(sysdate,’dd’) = 20) then open myCur for select * from dept; end if; open c; //일반커서 end; /

n 일반 커서가 가리키는 데이터는 함수나 프러시저를 호출한 곳으로 보낼 수 없지만 참조커서(REF CURSOR)가 가리키는 데이터는 가능하다.

-- 커서를 리턴할 수 있는 함수를 만든다. SQL> CREATE OR REPLACE FUNCTION emptest(v_deptno in number) RETURN SYS_REFCURSOR AS test_cursor SYS_REFCURSOR; sql_string Varchar2(500); BEGIN sql_string := 'Select empno, ename, sal from Emp where deptno = :deptno' ; Open test_cursor FOR sql_string USING v_deptno; RETURN test_cursor; CLOSE test_Cursor; END; / 함수가 생성되었습니다. -- SQL*Plus 에서 확인 SQL>variable a refcursor SQL>exec :a := emptest(10) SQL>print a EMPNO ENAME SAL ---------- ---------- ---------- 7782 CLARK 2450 7839 KING 5000 7934 MILLER 1300 -- JAVA/JSP등에서 확인 Class.forName(driver_name); //jdbc 드라이버연결 conn = DriverManager.getConnection(url,user,pwd); //Connection인수 입력 String proc_call = "{? = call emptest(?)}"; // 오라클 함수 또는 프러시저를 호출하기 위해 callable statement를 이용 cstmt = conn.prepareCall(proc_call); // key here is to register the output parameter // of type cursor, execute, then cast it as a ResultSet. -- emptest 함수의 output 파라미터 타입을 CURSOR로 한다. -- 첫번째 ?가 출력파라미터, 두번째 파리미터는 입력파라미터 cstmt.registerOutParameter(1, OracleTypes.CURSOR); cstmt.setInt(2, 10); //10번 부서의 데이터만 얻기 위해 cstmt.executeQuery(); rs = (ResultSet)cstmt.getObject(1); while(rs.next()) { out.println(rs.getString("ename") + "<br>"); …… }

​