解決C#中用Oracle執行預存程序返回DataSet的問題

第一種方案

執行預存程序返回DataSet：

1.預存程序中 一定要有一個Output參數的遊標，以便返回預存程序

 

  
--建立預存程序的返回暫存資料表  
  
create global temporary table TMP_HIS_PPTN_JP  
  
(  
  
  STCD   VARCHAR2(12) not null,  
  
  STNM   VARCHAR2(50),  
  
  ADDVCD VARCHAR2(6),  
  
  RGNNM  VARCHAR2(50),  
  
  HISAVG NUMBER(13,3),  
  
  ACCP   NUMBER(10,1),  
  
  JP     NUMBER(10,2)  
  
)  
  
on commit delete rows;--[1]當事務完成後刪除資料  
  
alter table TMP_HIS_PPTN_JP  
  
  add primary key (STCD);  
  
--建立預存程序  
  
CREATE OR REPLACE PROCEDURE PROC_RAIN_JP(  
  
    V_STCDS VARCHAR2,--要求V_PTM1，V_PTM2不垮年 ,返回暫存資料表TMP_HIS_PPTN_JP  
  
    V_PTM1  VARCHAR2,  
  
    V_PTM2  VARCHAR2,  
  
    V_CS  OUT  SYS_REFCURSOR    
  
)   
  
AS  
  
--定義變數......  
  
BEGIN  
  
         --資料的處理......  
  
         OPEN V_CS FOR SELECT * FROM TMP_HIS_PPTN_JP;  
  
        RETURN ;  
  
END;  
 

2. 在C#中用Oracle執行預存程序

 

  
IDbConnection con = this.DBInterface.CreateConnection();//自已定義的資料提供者  
  
con.Open();  
  
 
  
IDbTransaction trans = con.BeginTransaction();  
  
cmd = con.CreateCommand();  
  
cmd.Transaction = trans;    //Set Transaction For Command  
  
 
  
cmd.CommandType = System.Data.CommandType.StoredProcedure;  
  
cmd.CommandText = "PROC_RAIN_JP";  
  
System.Data.OracleClient.OracleParameter p;  
  
p = new System.Data.OracleClient.OracleParameter("V_STCDS", System.Data.OracleClient.OracleType.VarChar, 2000);  
  
cmd.Parameters.Add(p);  
  
p.Direction = System.Data.ParameterDirection.Input;  
  
p.Value = STC;  
  
 
  
p = new System.Data.OracleClient.OracleParameter("V_PTM1", System.Data.OracleClient.OracleType.VarChar, 20);  
  
cmd.Parameters.Add(p);  
  
p.Direction = System.Data.ParameterDirection.Input;  
  
p.Value = this.getParamValue("SDATE");  
  
 
  
p = new System.Data.OracleClient.OracleParameter("V_PTM2", System.Data.OracleClient.OracleType.VarChar, 20);  
  
cmd.Parameters.Add(p);  
  
p.Direction = System.Data.ParameterDirection.Input;  
  
p.Value = this.getParamValue("EDATE");  
  
 
  
 
  
//輸出的DataSet  
  
p = new System.Data.OracleClient.OracleParameter("V_CS", System.Data.OracleClient.OracleType.Cursor);  
  
cmd.Parameters.Add(p);  
  
p.Direction = System.Data.ParameterDirection.Output;//設定為Output  
  
 
  
DataSet ds = new DataSet();  
  
IDbDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(cmd as System.Data.OracleClient.OracleCommand);  
  
da.Fill(ds);  
  
 
  
trans.Commit();  
  
if (con.State != ConnectionState.Closed)  
  
...{  
  
    try 
  
    ...{  
  
        con.Close();  
  
    }  
  
    catch 
  
    ...{  
  
    }  
  
}  
  
return ds.Tables[0];   
  
 
 

3.注意上邊的C#代碼我們是開啟的一個事務，為什麼呢：

如果我們沒有用事務的話，在sqlplus中調試是沒有任何問題的，但在是.net執行的時間就會報ORA-08103: object no longer exists  錯誤，原因就在預存程序中，暫存資料表的建立選項由on commit delete rows[1]如果改為on commit preserve rows; 就不會有問題，但是在ASP.Net頁中查詢暫存資料表資料時，每查一次都要多出一些重複記錄原因肯定是Oracle的會話串連沒有結束，導致每次執行預存程序都要先插入記錄。Oracle會話為什麼沒有結束，肯定是ASP.NET服務程式在資料連線池中保持著與資料庫的串連。但是為了效能我們也不能不用串連池。這樣基於Oracle 會話的暫存資料表是不能用了。

重新回到基於Oracle事務的暫存資料表，也就是暫存資料表的建立選項用on commit delete rows。然後，在ASP.Net應用程式中調用ODP自身的交易處理機制，這樣C#中用Oracle執行預存程序返回DataSet的問題得以解決！

註：

（1）理論上，不要在預存程序中執行Commit，即不要在預存程序中使用PL/SQL的交易處理， 否則ASP.NET頁面也無法得到資料，因為commit 後，暫存資料表中資料會自動清空。

（2）理論上，不用ODP的話，用OLEDB或微軟提供的ORACLE交易處理機制應該也可以。

 

第二種方案

--包頭  
  create   or   replace   package   pkg_test  
  as  
      type   myCursor   is   ref   cursor;  
      function   get(p_id   number)   return   myCursor;  
  end   pkg_test;  
   
   
  --包體  
  create   or   replace   package   body   pkg_test    
  as  
      --輸入ID   返回記錄集的函數  
      function   get(p_id   number)   return   myCursor   is  
            rc   myCursor;  
            strsql   varchar2(200);  
      begin  
            if   p_id=0   then    
                  open   rc   for   select   a.user_name   from   fnd_user   a   ;      
            else  
                  strsql:='select   a.user_name   from   fnd_user   a   where   a.user_id=:p_id';  
                  open   rc   for   strsql   using   p_id;  
            end   if;  
            return   rc;      
            end   get;  
             
  end   pkg_test;  
   
  --調用測試  
  set   serverout   on    
  declare    
      w_rc   pkg_test.myCursor;  
      w_name   varchar2(100);  
  begin  
      w_rc:=pkg_test.get(0);  
      loop  
      fetch   w_rc   into   w_name;  
                  exit   when   w_rc%notfound;  
      dbms_output.put_line(w_name);  
      end   loop;  
  end;  
  /  

 

可以看出，第二種方案也就在包頭定義的地方加了一個遊標的聲明（type   myCursor   is   ref   cursor），其實在第一種方案中我們用了"SYS_REFCURSOR"，這是系統為我們定義好了的。所以，第二種方案好象有點多此一舉。