如何在没有自定义类型或游标的情况下从 Oracle 函数返回表？

How can a table be returned from an Oracle function without a custom type or cursor?

提问by

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?

我希望从 Oracle 函数返回结果表。使用游标将是最简单的，但我必须将其用于的应用程序不会接受游标作为返回值。另一种方法是创建一个类型（可能包装在一个包中）来配合这个函数。但是，创建几种类型（我有 4 个以上的函数要编写）似乎有些多余，只是为了返回表结果。有没有我缺少的替代方案？

回答by FerranB

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

更新：请参阅没有大小限制的 TABLE 解决方案的第一条评论。

Return an VARRAYor use a PIPELINEDfunction to query from them.

返回VARRAY或使用PIPELINED函数来查询它们。

• For VARRAYlook in thisarticle 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;

• For PiPELINEDfunctions 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 );
  

• 对于VARRAY看在这个文章的细节。那里的代码示例：

  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;
  

  结尾;

• 对于PiPELINED函数，请在此处查看。代码示例：

  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 );
  

回答by kurosch

You could always return XML from your function if that suits the application developers.

如果适合应用程序开发人员，您总是可以从您的函数返回 XML。

XML can be generated a number of ways in Oracle, depending on what you have installed and what version you're using.

在 Oracle 中可以通过多种方式生成 XML，具体取决于您安装的内容和使用的版本。

XMLTYPE is very useful in certain contexts, and can be generated from SQL using the XMLElement, XMLAttributes, XMLAgg, etc. built-in functions. If the client doesn't support XMLTYPE it can easily be cast to CLOB values.

XMLTYPE 在某些上下文中非常有用，可以使用 XMLElement、XMLAttributes、XMLAgg 等内置函数从 SQL 生成。如果客户端不支持 XMLTYPE，则可以轻松地将其转换为 CLOB 值。

Perhaps the simplest, though not the best (IMO) option is to use the dbms_xmlgen package:

也许最简单但不是最好的 (IMO) 选项是使用 dbms_xmlgen 包：

SQL> set serveroutput on size 1000;
SQL> exec dbms_output.put_line( dbms_xmlgen.getXML( 'select * from dual' ) );

Output:

输出：

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DUMMY>X</DUMMY>
 </ROW>
</ROWSET>

That gives you your "table" results in a single CLOB value.

这为您提供了单个 CLOB 值的“表”结果。