Oracle存储过程返回DataSet(多个DataTable)给C#

原创于 2023-11-30 22:06:41 发布 · 1.3k 阅读

23 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #c# #数据库

本文介绍了如何在C#中通过Oracle.ManagedDataAccess.Client调用名为GET_SENDEXCEL_XY的存储过程，涉及参数传递、RefCursor使用以及数据库连接管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、存储过程
CREATE OR REPLACE PROCEDURE GET_SENDEXCEL_XY
(

ZGJ182X OUT SYS_REFCURSOR ,
ZGJ182Y OUT SYS_REFCURSOR ,
ZXN182X OUT SYS_REFCURSOR ,
ZXN182Y OUT SYS_REFCURSOR ,
ZLH182X OUT SYS_REFCURSOR ,
ZLH182Y OUT SYS_REFCURSOR
)
IS
mysql varchar(8000) :='' ;
mysql_first varchar(8000) :='' ;
mysql_gj varchar(8000) :='' ;
mysql_xn varchar(8000) :='' ;
mysql_lh varchar(8000) :='' ;
mysql_gj_na varchar(8000):='';
mysql_yb varchar(8000) :='' ;
firstday varchar(50) :=''; ---前三周第1天


BEGIN

select to_char(m.fstartdate,'yyyy-mm-dd') into firstday
from scada_db.avg_week_month m
where to_char(m.fstartdate,'yyyy-mm-dd') <=to_char(sysdate-14-1,'yyyy-mm-dd') --当天往前一天
and to_char(m.fenddate,'yyyy-mm-dd')>=to_char(sysdate-14-1,'yyyy-mm-dd');

select a.sqltext into mysql_first
from sysolap.AVG_DIA_SQL a where a.code='send_xy_All3';
mysql_first:=replace(mysql_first,'{firstday}',firstday); ---首先替换周日期

mysql_gj:=replace(mysql_first,'{GJXN}','ZGJPA'); ---初始化一期
mysql_xn:=replace(mysql_first,'{GJXN}','ZXNPA'); ----初始化二期
mysql_lh:= replace(mysql_first,'{GJXN}','ZLHPA'); ----初始化龙X
mysql_gj_na:= replace(mysql_first,'{GJXN}','ZXNNA'); ----初始化西XNA
mysql_yb := replace(mysql_first,'{GJXN}','ZYBPA'); ----初始化宜X182
mysql_first:='';

----1 ZGJ182X
mysql:=replace(mysql_gj,'{table}','pcx182');
mysql:=replace(mysql,'{边宽对角}','边宽182');

open ZGJ182X for mysql ;

----2 ZGJ182Y
mysql:=replace(mysql_gj,'{table}','pcy182');
mysql:=replace(mysql,'{边宽对角}','边宽182');
open ZGJ182Y for mysql ;

---3 ZXN182X
mysql:=replace(mysql_xn,'{table}','pcx182');
mysql:=replace(mysql,'{边宽对角}','边宽182');
open ZXN182X for mysql ;

-- 4 ZXN182Y
mysql:=replace(mysql_xn,'{table}','pcy182');
mysql:=replace(mysql,'{边宽对角}','边宽182');
open ZXN182Y for mysql ;

---31 ZLHPA182X
mysql:=replace(mysql_lh,'{table}','pcx182');
mysql:=replace(mysql,'{边宽对角}','边宽182');
open ZLH182X for mysql ;

-- 41 ZLHPA182Y
mysql:=replace(mysql_lh,'{table}','pcy182');
mysql:=replace(mysql,'{边宽对角}','边宽182');
open ZLH182Y for mysql ;


END;

二、C#程序接收存储过程

/// <summary>
/// 获取数据
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="conString">数据库连接串</param>
/// <param name="strARR">入参数组</param>
/// <returns></returns>
private DataSet getData(string procName, string conString, params string[] strARR)
{
using (Oracle.ManagedDataAccess.Client.OracleConnection con = new Oracle.ManagedDataAccess.Client.OracleConnection())
{

OracleCommand cmd2 = new OracleCommand();
OracleDataAdapter oda2 = new OracleDataAdapter(cmd2);
OracleParameter par1 = null;
DataSet ds = new DataSet();
con.ConnectionString = 数据库连接串;
con.Open();
cmd2.Connection = con;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = procName;
foreach (string item in strARR)
{
par1 = new OracleParameter(item, OracleDbType.RefCursor);
par1.Direction = ParameterDirection.Output;
cmd2.Parameters.Add(par1);
}
oda2.Fill(ds);
con.Close();
return ds;
}
}