返回dataset oracle,C#中用Oracle 执行存储过程返回DataSet

最新推荐文章于 2023-11-30 22:06:41 发布

转载最新推荐文章于 2023-11-30 22:06:41 发布 · 391 阅读

文章标签：

#返回dataset oracle

本文介绍了如何使用Oracle存储过程进行数据查询，并展示了通过.NET框架调用存储过程的具体实现方式，包括连接数据库、创建事务、执行存储过程及获取输出结果集。

CREATE OR REPLACE PROCEDURE 你的存储过程名字

(

参数1 参数类型, 参数2 参数类型, ......

V_CS OUT SYS_REFCURSOR -----关键的游标

)

--定义变量......

BEGIN

--数据的处理......

OPEN V_CS FOR SELECT * FROM 你需要查询的表;

RETURN ;

END;

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];

//自己项目中的代码

IDbConnection con = this.DataManager.DataProvide.GetConnection();

con.Open();

IDbTransaction trans = con.BeginTransaction();

IDbCommand cmd = con.CreateCommand();

cmd.Transaction = trans;

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.CommandText = "p_trans_tj";

OracleParameter p;

p = new OracleParameter("userid", OracleType.VarChar, 2000);

cmd.Parameters.Add(p);

p.Direction = System.Data.ParameterDirection.Input;

p.Value = userid;

p = new OracleParameter("typeid", OracleType.VarChar, 2000);

cmd.Parameters.Add(p);

p.Direction = System.Data.ParameterDirection.Input;

p.Value = typeid;

p = new OracleParameter("years", OracleType.VarChar, 2000);

cmd.Parameters.Add(p);

p.Direction = System.Data.ParameterDirection.Input;

p.Value = years;

p = new OracleParameter("months", OracleType.Float, 2);

cmd.Parameters.Add(p);

p.Direction = System.Data.ParameterDirection.Input;

p.Value = months;

p = new OracleParameter("cur_out", 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);