CREATE OR REPLACE PROCEDURE 你的存储过程名字
(
参数1 参数类型, 参数2 参数类型, ......
V_CS OUT SYS_REFCURSOR -----关键的游标
)
AS
--定义变量......
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);