REF CURSOR를 사용하여 레코드셋 반환하기

Using Ref Cursors To Return Recordsets

Since Oracle 7.3 REF CURSORS have been available which allow recordsets to be returned from stored procedures, functions and packages. The example below uses a ref cursor to return a subset of the records in the EMP table.

First, a package definition is needed to hold the ref cursor type:

CREATE OR REPLACE PACKAGE Types AS 
  TYPE cursor_type IS REF CURSOR;
END Types; 
/

Note. In Oracle9i the SYS_REFCURSOR type has been added making this first step unnecessary. If you are using Oracle9i or later simply ignore this first package and replace any references to Types.cursor_type with SYS_REFCURSOR.

Next a procedure is defined to use the ref cursor:

CREATE OR REPLACE
PROCEDURE GetEmpRS (p_deptno    IN  emp.deptno%TYPE,
                    p_recordset OUT Types.cursor_type) AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END GetEmpRS;
/

The resulting cursor can be referenced from PL/SQL as follows:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  v_cursor  Types.cursor_type;
  v_ename   emp.ename%TYPE;
  v_empno   emp.empno%TYPE;
  v_deptno  emp.deptno%TYPE;
BEGIN
  GetEmpRS (p_deptno    => 30,
            p_recordset => v_cursor);
            
  LOOP 
    FETCH v_cursor
    INTO  v_ename, v_empno, v_deptno;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_ename || ' | ' || v_empno || ' | ' || v_deptno);
  END LOOP;
  CLOSE v_cursor;
END;
/

In addition the cursor can be used as an ADO Recordset:

Dim conn, cmd, rs

Set conn = Server.CreateObject("adodb.connection")
conn.Open "DSN=TSH1;UID=scott;PWD=tiger"

Set cmd = Server.CreateObject ("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "GetEmpRS"
cmd.CommandType = 4 'adCmdStoredProc

Dim param1
Set param1 = cmd.CreateParameter ("deptno", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = 30

Set rs = cmd.Execute

Do Until rs.BOF Or rs.EOF
  -- Do something
  rs.MoveNext
Loop

rs.Close
conn.Close
Set rs     = nothing
Set param1 = nothing
Set cmd    = nothing
Set conn   = nothing

The cursor can also be referenced as a Java ResultSet:

import java.sql.*;
import oracle.jdbc.*;

public class TestResultSet  {
  public TestResultSet() {
    try {
      DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
      Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@w2k1", "scott", "tiger");
      CallableStatement stmt = conn.prepareCall("BEGIN GetEmpRS(?, ?); END;");
      stmt.setInt(1, 30); // DEPTNO
      stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
      stmt.execute();
      ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);
      while (rs.next()) {
        System.out.println(rs.getString("ename") + ":" + rs.getString("empno") + ":" + rs.getString("deptno")); 
      }
      rs.close();
      rs = null;
      stmt.close();
      stmt = null;
      conn.close();
      conn = null;
    }
    catch (SQLException e) {
      System.out.println(e.getLocalizedMessage());
    }
  }

  public static void main (String[] args) {
    new TestResultSet();
  }
}