oracle过程返回表的自定义数据类型记录 [英] oracle procedure returning table of custom data type record

问题描述

我创建了对象"类型的自定义数据类型

I created a custom data type of the type "object"

create type myrecord is object(col1 varchar2(10),col2 varchar2(10));

我创建了一个记录类型的表

I created a table which is of the type record

create type mytableobject is table of myrecord;

现在我已经使用

"execute immediate" (select * from table1) bulk collect into mytableobject.

我想通过存储过程返回此mytable对象.

I want to return this mytableobject in a STORED procedure.

我该如何实现? 我该怎么称呼该程序?

How do i achieve this? And how do i call the procedure?

推荐答案

您可以将自己的类型用作存储过程的OUT参数.您显示的表人口没有多大意义，所以我认为这是您必须真正做的:

You can use your own type as an OUT parameter to a stored procedure. The table population you showed doesn't make much sense, so I think this is what you must really be doing:

create or replace procedure myproc(mytable out mytableobject) is
begin
  select myrecord(col1, col2)
  bulk collect into mytable
  from table1;
end myproc;
/

然后，您可以通过声明该类型的局部变量并将其传递给另一个过程或匿名块来调用它:

You can then call that from another procedure or anonymous block by declaring a local variable of that type and passing it in:

declare
  tab mytableobject;
begin
  myproc(tab);
end;
/

您似乎更想要一个函数，尤其是因为您提到了要返回它.几乎相同:

It seems more likely that you want a function, particularly since you mentioned returning it. That's pretty much the same:

create or replace function myfunc
return mytableobject is
  mytable mytableobject;
begin
  select myrecord(col1, col2)
  bulk collect into mytable
  from table1;

  return mytable;
end myfunc;
/

declare
  tab mytableobject;
begin
  tab := myfunc;
end;
/

但更有用的是，您可以从SQL调用该函数:

But more usefully you can call the function from SQL:

select * from table(myfunc);

SQL提琴.

这篇关于oracle过程返回表的自定义数据类型记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！