The DataDirect provider for Oracle调用存储过程使用Cursor类型参数

When executing a stored procedure that returns a REF CURSOR parameter, the Microsoft Oracle provider requires that the REF CURSOR be bound as an OracleParameter object of type OracleDbType.Cursor.

The DataDirect provider for Oracle does not require these parameters to be bound at all. The DataDirect provider returns the results from REF CURSORs as a result set from the stored procedure, either through an OracleDataReader returned from OracleCommand.ExecuteReader or through an OracleDataAdapter.

This provides for easier coding as you do not need to do anything for REF CURSORs to be returned to the application. It also provides additional interoperability across providers as result sets from stored procedures are returned in the same manner with all of the DataDirect providers.

If multiple REF CURSORs are returned, then the DataDirect Oracle provider returns them as multiple result sets in the order of the REF CURSOR parameters in the Create Procedure statement. Therefore, you should simply remove the bindings for any parameters of this type.

The following code example creates a stored procedure and REF CURSOR on the Oracle server:

create or replace package EMP_PACKAGE AS
TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE;
END EMP_PACKAGE;
create or replace procedure empcursor (emp_cv IN OUT
EMP_PACKAGE.EmpCurType,
salary IN NUMBER)
as
BEGIN
OPEN emp_cv FOR SELECT * FROM emp WHERE sal > salary;

END;

Thus a statement in the Microsoft Oracle provider would need to be changed from:

OracleCommand DBCmd = new OracleCommand("empcursor", DBConn);
DBCmd.CommandType = CommandType.StoredProcedure;
DBCmd.Parameters.Add("emp_cv", OracleType.Cursor).Direction

= ParameterDirection.Output;
DBCmd.Parameters.Add ("salary", OracleType.Number,15,"").Value = 30000;
OracleDataReader myDataReader;
myDataReader = DBCmd.ExecuteReader();

to:

OracleCommand DBCmd = new OracleCommand("empcursor", DBConn);

DBCmd.CommandType = CommandType.StoredProcedure;
DBCmd.Parameters.Add ("anyname", OracleDbType.Number,15,"").Value = 30000;
OracleDataReader myDataReader;
myDataReader = DBCmd.ExecuteReader();

Note that in the above example, only the second parameter in the stored procedure ("salary") is bound to the DataDirect Oracle provider.

转载于:https://www.cnblogs.com/qb371/archive/2011/10/21/2366292.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值