从Oracle函数返回多个值 [英] Return Multiple Values from Oracle Function

问题描述

我想创建一个函数，该函数将多行返回到对象类型的表中.

I want to create a function that returns multiple rows into a table that is of object type.

我已经创建了一个对象和一个嵌套表对象，现在当我运行该函数时，会显示一条错误消息

I have created an object and a nested table object and now when I run the function there is an error which says

PL/SQL:忽略了SQL语句 PL/SQL:ORA-00947:值不足

PL/SQL: SQL Statement ignored PL/SQL: ORA-00947: not enough values

-- Object type creation
create or replace type test_object_sn as object
(
    column_1 varchar2(30),
    column_2 varchar2(30),
    column_3 number 
);


-- Table of object
create or replace type test_otable_sn as table of test_object_sn; 

-- function (where I get an error)
create or replace function load_test_object_sn
return test_otable_sn
as  
    details test_otable_sn;
begin
    with ad as (select 'a', 'b', 4   from dual
    union all 
    select 'r', '5', 3  from dual
    union all
    select 'g', 's', 3  from dual)
    select * into details from ad; 

    return details;
end;

我想用数据加载test_otable_sn表对象，然后通过我的load_test_object_sn函数使用table()函数查询它 例如select * from table(load_test_object_sn);

I want to have the test_otable_sn table object loaded with the data and then query it using the table() function via my load_test_object_sn function e.g. select * from table(load_test_object_sn);

推荐答案

更新:

您知道如何针对我有一个sql的情况修改此设置吗? 包含在字符串变量中的语句要执行?

do you know how to modify this for scenario whereby I have an sql statement contained in a string variable to execute?

是的，我们可以使用游标引用(SYS_REFCURSOR)和OPEN/FETCH/CLOSE代替CURSOR和CURSOR FOR LOOP.

Yes, we can use a cursor reference (SYS_REFCURSOR) and OPEN/FETCH/CLOSE instead of a CURSOR and CURSOR FOR LOOP.

语法为OPEN <cursor-reference> FOR <string-containing-sql-statement>.见下文.

CREATE OR REPLACE FUNCTION load_test_object_sn
RETURN test_otable_sn
AS  
  details test_otable_sn := test_otable_sn();

  -- Variable stores SQL statement for cursor
  l_sql CLOB :=
    q'[with ad as (
         select 'a' column_1, 'b' column_2, 4 column_3 from dual union all
         select 'r', '5', 3  from dual union all 
         select 'g', 's', 3  from dual
       )
       select *
         from ad]';

  -- Cursor reference allows us to open cursor for SQL statement above
  rc SYS_REFCURSOR;

  -- Define object instance to store each row fetched from the cursor
  l_obj test_object_sn := test_object_sn(NULL, NULL, NULL);

  i PLS_INTEGER := 1;
BEGIN

  -- Explicitly open, fetch from, and close the cursor
  OPEN rc FOR l_sql;
  LOOP
    FETCH rc INTO l_obj.column_1, l_obj.column_2, l_obj.column_3;
    EXIT WHEN rc%NOTFOUND;
    details.extend();
    details(i) := test_object_sn(l_obj.column_1, l_obj.column_2, l_obj.column_3);
    i := i + 1;
  END LOOP;
  CLOSE rc;

  RETURN details;
END;

原始答案:

不幸的是，不能以这种方式将SELECT * INTO与集合一起使用，因此这是填充表的另一种方法:

Original answer:

Unfortunately, one can't use SELECT * INTO with a collection in this manner, so here's an alternative way to populate the table:

create or replace function load_test_object_sn
return test_otable_sn
as  
    details test_otable_sn := test_otable_sn();
    cursor c_ad is
    with ad as (select 'a' column_1, 'b' column_2, 4 column_3   from dual
    union all 
    select 'r', '5', 3  from dual
    union all
    select 'g', 's', 3  from dual)
    select * from ad;
    i pls_integer := 1;

begin

   for ad_rec in c_ad loop     
      details.extend();
      details(i) := test_object_sn(ad_rec.column_1, ad_rec.column_2, ad_rec.column_3);
      i := i + 1;
   end loop;

    return details;
end;
/

输出:

SQL> SELECT * FROM TABLE(load_test_object_sn);

COLUMN_1   COLUMN_2     COLUMN_3
---------- ---------- ----------
a          b                   4
r          5                   3
g          s                   3

这篇关于从Oracle函数返回多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！