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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值