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();
            }
        }