参考游标(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 OUTsys_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();
}
}