参考游标(refcursor)的使用

参考游标(refcursor)的使用

我们知道在sql server中可以直接在存储过程中使用select * from 来产生记录集，但在oracle的存储过程里却不可以。怎么办呢？可以用参考游标来实现。

 

参考游标（ref cursor）从Oracle 7.3开始引入，作用是允许在存储过程，函数，包中返回记录集。

 

在Oracle 9i之前，参考游标以如下方式定义：

 

首先定义一个参考游标：

 

TYPE ref_type_name IS REF CURSOR
      [RETURN {cursor_name%ROWTYPE
             |ref_cursor_name%ROWTYPE
             |record_name%TYPE
             |record_type_name
             |table_name%ROWTYPE} ];

 

如：

 

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

然后在存储过程中引用这个游标：

 

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;
/

 

Oracle 9i及以后版本，可省略第一步的定义，用sys_refcursor来代替：

 

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

 

在程序中如何调用呢？

 

ADO中：

 

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

 

Java中：

 

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();
  }
}