存储过程返回多数据集 Delphi 中处理方法

1.在C# 中我们可以通过DataSet 来接收多个数据集，但在Delphi 中没有这样的集合对象，因此处理起来稍微麻烦点，不过还是能处理的。这里举个例子：

Oracle Code

--包头
create or replace package mypackage is
type 
  cur_type is ref cursor;
  procedure pr_getEmployee(emp_cur out nocopy cur_type);
  procedure pr_getEmpAndDept(nodata_cur out cur_type,emp_cur out  cur_type,dept_cur out  cur_type,cur_nation out cur_type);
end;

--包体

create or replace package body mypackage is
procedure pr_getEmployee(emp_cur out nocopy cur_type) as
begin
  open emp_cur for select a.employeeno 员工编号,a.employeename 员工姓名,b.departmentname 所在部门,a.sex 性别,
  a.employeedate 出生日期,a.workdate 工作日期 from employee a
  left join department b on a.departmentno=b.departmentno;
end;

procedure pr_getEmpAndDept(nodata_cur out cur_type,emp_cur out  cur_type,dept_cur out  cur_type,cur_nation out cur_type) as
begin
  open nodata_cur for select 1 from dual;
  open emp_cur for select * from employee;
  open dept_cur for select * from department;
  open cur_nation for select * from dcnation; 
end;

end;

--说明：在存储过程pr_getEmpAndDept 中刻意增加了一个无用的nodata_cur 游标，这个游标用于返回空数据。因为在多次测试中发现第一个参数返回结果集在Delphi 中接收为空，即只有元数据，具体原因待有时间再查。

Delphi Code

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, Ora, DB, MemDS, DBAccess,
  MemTableDataEh, MemTableEh, DBGridEhGrouping, ToolCtrlsEh,
  DBGridEhToolCtrls, GridsEh, DBAxisGridsEh, DBGridEh;

type
  TForm1 = class(TForm)
    OraSession1: TOraSession;
    OraStoredProc1: TOraStoredProc;
    OraQuery1: TOraQuery;
    OraQuery2: TOraQuery;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    Button1: TButton;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    OraQuery3: TOraQuery;
    DBGrid3: TDBGrid;
    DataSource3: TDataSource;
    MemTableEh1: TMemTableEh;
    DBGridEh1: TDBGridEh;
    DataSource4: TDataSource;
    OraQuery4: TOraQuery;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  OraSession1.Connected:=True;
  OraStoredProc1.Connection:=OraSession1;
  OraStoredProc1.Params.Clear;
  OraStoredProc1.Params.CreateParam(ftCursor,'nodata_cur',ptOutput);
  OraStoredProc1.Params.CreateParam(ftCursor,'EMP_CUR',ptOutput);
  OraStoredProc1.Params.CreateParam(ftCursor,'DEPT_CUR',ptOutput);
  OraStoredProc1.Params.CreateParam(ftCursor,'cur_nation',ptOutput);
  try
    OraStoredProc1.ExecProc;
  except on e:Exception do
  begin
    ShowMessage(e.Message);
  end;
  end;
  OraQuery4.Cursor:=OraStoredProc1.parambyname('nodata_cur').AsCursor;  
  OraQuery1.Cursor:=OraStoredProc1.parambyname('EMP_CUR').AsCursor;
  OraQuery2.Cursor:=OraStoredProc1.parambyname('DEPT_CUR').AsCursor;
  OraQuery3.Cursor:=OraStoredProc1.Parambyname('cur_nation').AsCursor; 
   //OraTable1.Cursor:= OraStoredProc1.Parambyname('cur_nation').AsCursor;
  OraQuery1.Open;
  OraQuery2.Open;
  OraQuery3.Open;
  OraQuery4.Open;

end;

end.

--说明： 存储过程类TOraStoredProcedure 中定义的参数类型可以转换为Cursor 游标类型，而TOraQuery和TOraTable 数据集组件中正好也定义了一个Cursor 属性用于获取游标，这就提供了便利！