如何使Oracle过程返回结果集 [英] How to make Oracle procedure return result sets

问题描述

SQL Server过程可以返回结果集.我有一个表 emp(emp__id，emp__name，...).以下过程将返回与提供的姓名匹配的员工列表.

SQL Server procedure can return result sets. I have a table emp(emp__id, emp__name, ...). The procedure below will return a list of employees that matched with the name provided.

CREATE OR REPLACE PROCEDURE get_employee_by_name ( @name VARCHAR(100) )
AS
SELECT emp_id, emp_name
FROM emp
WHERE emp_name = @name;

因此，在客户端代码中，要使用ADO.NET来获取数据.

So in the client code, to get the data I use ADO.NET.

SQLDataAdapter adapter = new SQLDataAdapter("get_employee_by_name", cnString);
SQLDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable dt = new DataTable("employee");

adapter.Fill(dt);

如何在PL/SQL中等效地进行编码?

How can I code equivalently in PL/SQL?

推荐答案

对存储过程使用Ref游标:
http://www.oradev.com/ref_cursor.jsp

Use a Ref cursor for the Stored Procedure:
http://www.oradev.com/ref_cursor.jsp

对于客户端，使用Oracle数据提供程序.您可以从Oracle下载它，其语法类似于SQLDataAdapter.像这样:

For the client part use the Oracle Data Provider. You can download it from Oracle and the syntax is similar to the SQLDataAdapter. Something like this:

OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = new OracleCommand("get_employee_by_name", Connection);
OracleParameter prm = da.SelectCommand.Parameters.Add("pName", OracleDbType.VarChar2);
prm.Direction = ParameterDirection.Input;
prm.Value = "MyName";
prm = da.SelectCommand.Parameters.Add("pResult", OracleDbType.RefCursor);
prm.Direction = ParameterDirection.Output;
DataTable dt = new DataTable();
da.Fill(dt);

这篇关于如何使Oracle过程返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！