执行存储过程返回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#中执行存储过程
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#代码我们是打开的一个事务,为会么呢: |
|