没有自定义类型或游标的Oracle函数如何返回表? [英] How can a table be returned from an Oracle function without a custom type or cursor?

问题描述

我希望从Oracle函数返回一个结果表.使用游标将是最简单的，但是我必须研究的应用程序不会接受游标作为返回值.另一种方法是创建一个类型(可能包装在包装中)以与此功能一起使用.但是，创建几种类型(我要编写4个以上的函数)似乎有点多余，只是我可以返回表结果.我还缺少其他选择吗?

I am looking to return a table of results from an Oracle function. Using a cursor would be easiest, but the application I have to work this into will not accept a cursor as a return value. The alternative is to create a type (likely wrapped in a package) to go along with this function. However, it seems somewhat superfluous to create several types (I have 4+ functions to write) just so I can return table results. Is there an alternative that I am missing?

推荐答案

更新:请参见有关不带大小限制的TABLE解决方案的第一条评论.

UPDATE: See the first comment for TABLE solution withou limit of size.

返回 VARRAY 或使用 PIPELINED 函数从中查询.

Return an VARRAY or use a PIPELINED function to query from them.

• 对于 VARRAY ，请查看这篇文章以获取详细信息.那里的代码示例:

• For VARRAY look in this article for a detail. Code example from there:

CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30)
/

CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAY
AS
  l_data EmpArray := EmpArray();
  CURSOR c_emp IS SELECT ename FROM EMP;
BEGIN
  FOR emp_rec IN c_emp LOOP
    l_data.extend;
    l_data(l_data.count) := emp_rec.ename;
  END LOOP;
RETURN l_data;

END;

对于 PiPELINED 函数结帐此处.代码示例:

For PiPELINED functions checkout here. Code example:

create or replace function Lookups_Fn return lookups_tab
  pipelined
is
  v_row lookup_row;
begin
  for j in 1..10
  loop
    v_row :=
      case j
        when 1 then lookup_row ( 1, 'one' )
        --...
        when 7 then lookup_row ( 7, 'seven' )
        else        lookup_row ( j, 'other' )
      end;
    pipe row ( v_row );
  end loop;
  return;
end Lookups_Fn;
/

select * from table ( Lookups_Fn );

这篇关于没有自定义类型或游标的Oracle函数如何返回表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！