c# oracle 返回数据集,C#显示存储过程返回的游标数据集

1、--Oracle 创建包 申明自定义游标类型

create or replace package pk_Var is

type csr_tp is ref cursor;

end;

2、--Oracle 创建存储过程返回数据集合

CREATE OR REPLACE PROCEDURE pr_getdata(

v_empno VARCHAR2 ,  RC1 out PK_Var.csr_tp  )

AS

sSQL  varchar2(2000) ;

begin

sSQL :=  ' select * from emp where empno=v_empno'  ;

Open RC1 for sSQL;

return;

end  pr_getdata;

3、c#代码如下：

//创建oracle参数

OracleParameter[] sqlParme = new OracleParameter[2]

//参数1

sqlParme[0] = new OracleParameter("RC1", OracleType.Cursor);

sqlParme[0].Direction = ParameterDirection.Output;

sqlParme[1] = new OracleParameter("TKALA", OracleType.VarChar);

sqlParme[1].Direction = ParameterDirection.Input;

sqlParme[1].Value = aHoleOldNum;

//从存储过程中获取dataTable数据源

private DataTable DBExecStoredProcedure(string storeureName, OracleParameter[] sqlParme)

{

try

{

//使用微软的ORACLE访问接口

if (DBHelper.G_pDBConn.State == ConnectionState.Closed)//获取数据连接

DBHelper.G_pDBConn.Open();

OracleCommand oraCmd = new OracleCommand(storeureName, DBHelper.G_pDBConn);

oraCmd.CommandType = CommandType.StoredProcedure;

oraCmd.Parameters.Clear();//先清空

foreach (OracleParameter parme in sqlParme)

{

oraCmd.Parameters.Add(parme);

}

DataTable table = new DataTable();

DateTime BegTime = System.DateTime.Now;

OracleDataAdapter da1 = new OracleDataAdapter(oraCmd);//取出数据

da1.Fill(table);

return table;

}

catch (Exception ex)

{

MessageBox.Show("执行存储过程失败："+ex.Message,"调试");

return null;

}

finally {

DBHelper.G_pDBConn.Close();

}

}