解决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"，这是系统为我们定义好了的。所以，第二种方案好象有点多此一举。