oracle 存储过程返回数据集是以游标参数的形式返回,同时返回多个数据集就相当于多几个输出类型的游标参数,而在程序处理中如何处理这种多结果集的存储过程?
今天研究了Delphi7 和 c# 两种语言,D7 ODBC 方式连接,OraStoreProced 对象无法处理多个对象,查看其父类TCustomOraQuery,不想c# 断开式数据集可以接收多个集合,即DataSet 等同于 List
- 示例
Oracle
--包头
create or replace package mypackage is
type cur_type is ref cursor;
procedure pr_get_data(cur1 out cur_type,cur2 out cur_type);
end;
--包体
create or replace package body mypackage is
procedure pr_get_data(cur1 out cur_type,cur2 out cur_type) as
begin
open cur1 for select * from sales;
open cur2 for select * from t1;
end;
end;
c# 调用
- C# 示例
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
namespace WinStoreProced
{
public partial class Form1 : Form
{
private string connstr = "Data Source=(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCL)));User Id=t1;Password=tt123;";
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
using (OracleConnection conn = new OracleConnection(connstr)) {
try
{
conn.Open();
Text = "Success";
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "mypackage.pr_get_data";
cmd.Parameters.Clear();
cmd.Parameters.Add("data1", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add("data2", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Prepare();
try
{
OracleDataAdapter oda = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
oda.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
dataGridView2.DataSource = ds.Tables[1];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
catch(Exception ex) {
MessageBox.Show(ex.Message);
}
}
}
}
}
- 结果