C#顯示存儲過程返回的游標(biāo)數(shù)據(jù)集

1、--Oracle 創(chuàng)建包 申明自定義游標(biāo)類型

create or replace package pk_Var is

type csr_tp is ref cursor;

end;

2、--Oracle 創(chuàng)建存儲過程返回?cái)?shù)據(jù)集合

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#代碼如下：

//創(chuàng)建oracle參數(shù)

 OracleParameter[] sqlParme = new OracleParameter[2]

            //參數(shù)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數(shù)據(jù)源

        private DataTable DBExecStoredProcedure(string storeureName, OracleParameter[] sqlParme)
        {
            try
            {
                //使用微軟的ORACLE訪問接口                 
                if (DBHelper.G_pDBConn.State == ConnectionState.Closed)//獲取數(shù)據(jù)連接
                    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);//取出數(shù)據(jù)
                da1.Fill(table);
                return table;
            }
            catch (Exception ex)
            {
                MessageBox.Show("執(zhí)行存儲過程失?。?+ex.Message,"調(diào)試");
                return null;
            }
            finally {
                DBHelper.G_pDBConn.Close();
            }
        }