.net - 使用 .NET 中的嵌套表调用 Oracle 存储过程

Question

给出下表:

create table egyen (
  id number not null, 
  tajszam varchar2(9),
  nev varchar2(10),
  primary key(id)
);

以及以下包:

create or replace package pck_egyen is
  type egyentab is table of egyen%rowtype;
  procedure list(ret$ out egyentab);
end pck_egyen;
/
create or replace package body pck_egyen is
  procedure list(ret$ out egyentab) is
  begin
    ret$ := egyentab();
    for rec in (select * from egyen) loop
      ret$.extend;
      ret$(ret$.count) := rec;
    end loop;
  end;
end pck_egyen;
/

有人可以为我提供一个使用 ODP.NET 11g 的工作 .NET 代码，它可以调用这个存储过程，而无需以任何方式改变表或类型或包吗？我不知道如何设置 OracleParameter 来支持 pck_egyen.egyentab 类型...

我以前没有使用过 UDT，我尝试了一下，但它无法识别包中嵌入的类型。全局类型不支持 %rowtype。好像我被卡住了。最初我想返回 ref 游标，就像一个魅力，但不是通过 dblink。太多愚蠢的限制。

Answer 1

这是一个有趣的问题，特别是因为需要使用 .NET 跨 dblinks 使用数据和存储过程。不跨 dblinks 使用游标的限制导致您使用 pl/sql 表，但您不能在 .NET 中轻松使用这些表(无需经历设置和维护全局对象的痛苦)。

因此，我建议同时使用引用游标函数和带有输出表参数的过程。您将能够轻松地从 .NET 调用 ref 游标函数(您不需要跨 dblinks 选择)，并且对于 dblink 数据库工作，使用相应的过程。例如:

create table test1 (
    col1 number,
    col2 varchar2(10),
    col3 date default sysdate not null
);
insert into test1(col1,col2) values (1,'A');
insert into test1(col1,col2) values (1,'X');
insert into test1(col1,col2) values (2,'B');

commit;

CREATE OR REPLACE package TEST_PKG as
  type t_test1_tab is table of test1%rowtype;
  -- weak ref cursor
  function get_test1_cur (i_num in number) return sys_refcursor;
  -- uses rowtype for table
  procedure get_test1_tab(i_num in number, o_tab out t_test1_tab);
end;


CREATE OR REPLACE package body TEST_PKG as
  function get_test1_cur (i_num in number) return sys_refcursor is
    l_cur sys_refcursor;
  begin
    open l_cur for select * from test1 where col1=i_num;
    return l_cur;
  end;

  procedure get_test1_tab(i_num in number, o_tab out t_test1_tab) is
    l_rec test1%rowtype;
    l_tab t_test1_tab := t_test1_tab();
    l_cur sys_refcursor;
  begin
    l_cur := get_test1_cur(i_num);
    loop
      fetch l_cur into l_rec;
      exit when l_cur%notfound;
      l_tab.extend;
      l_tab(l_tab.last) := l_rec;
    end loop;
    close l_cur;
    o_tab := l_tab;
  end;

end;

将任何需要的逻辑放入您的引用游标函数中。该过程只是调用函数并创建表(使用行类型)。

跨数据库链接使用数据库调用的过程:

declare
  l_tab test_pkg.t_test1_tab@dblinkA;
begin
  test_pkg.get_test1_tab@dblinkA(1, l_tab);
  -- show count
  dbms_output.put_line('Table has ' || l_tab.count || ' rows.');
end;

使用该函数进行 odp.net 调用。例如(代码片段):

OracleConnection con = new OracleConnection(connStr);

// create the command object and set attributes
OracleCommand cmd = new OracleCommand("test_pkg.get_test1_cur", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = false;

// create parameter object for the cursor
OracleParameter p_refcursor = new OracleParameter();
// create any input parameters to the function
OracleParameter p_num = new OracleParameter();

p_refcursor.OracleDbType = OracleDbType.RefCursor;
p_refcursor.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p_refcursor);

// add any input parameters
p_num.OracleDbType = OracleDbType.Int32;
p_num.Direction = ParameterDirection.Input;
p_num.Value = 1;
cmd.Parameters.Add(p_num);

// create a data adapter to use with the data set
OracleDataAdapter da = new OracleDataAdapter(cmd);

// create the data set
DataSet ds = new DataSet();

// fill the data set
da.Fill(ds);

这种方法应该易于维护，因为您不仅使用 rowtype，而且过程只是调用函数。