从 Oracle 函数返回表

2021-12-24 00:00:00 function oracle11g oracle plsql

我在这里查看了许多解决方案来尝试解决这个问题，它们已经取得了相当大的进展，但现在我在一些我似乎无法克服的错误中陷入困境.

I've looked at many solutions here to try to solve this and they have gotten pretty far but now I'm in the weeds on some errors that I can#t seem to get past.

我使用的是 Oracle 11g.我需要一个函数来返回记录集(表).这是我正在使用的代码:

I am on Oracle 11g. I need a function to return a record set (table). Here is the code I'm using:

CREATE TYPE T_TABLE IS OBJECT
(
    Field1 int
    , Field2 int
);

CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;

CREATE OR REPLACE FUNCTION FN_MyFunction
RETURN T_TABLE_COLL 
IS
BEGIN
  FOR I IN (SELECT Field1, Field2 FROM Table1) LOOP
    IF I.Field1 = 1 THEN
        BEGIN           
            INSERT INTO T_TABLE     
            SELECT Field1, Field2
            FROM Table2
            WHERE Field2 = I.Field2;
        END;
    ELSIF I.Field1 = 2 THEN
        BEGIN           
            INSERT INTO T_TABLE     
            SELECT Field1, Field2
            FROM Table2
            WHERE Field2 = I.Field2;
        END;  
  END IF;
  END LOOP;
  RETURN T_SMRYACCT_TABLE_COLL;
END;

我从中收到的错误是:

  1. FUNCTION FN_MyFunction 行和 PL/SQL 上的语句被忽略:ORA-04044:过程、函数、包或类型在每行 INSERT INTO T_TABLE_COLL 行中均不允许使用

  1. Statement Ignored on the FUNCTION FN_MyFunction line and PL/SQL: ORA-04044: procedure, function, package, or type is not allowed here on each line INSERT INTO T_TABLE_COLL line

PLS-00330:在 RETURN 行中无效使用类型名称或子类型名称

PLS-00330: invalid use of type name or subtype name on the RETURN line

我对表格类型做错了什么?

What am I doing wrong with the table types?

推荐答案

T_TABLE_COLL 是一个集合.您不能对集合使用 insert.

T_TABLE_COLL is a collection. You cannot use insert on collections.

CREATE OR REPLACE FUNCTION FN_MyFunction
RETURN T_TABLE_COLL
IS
  l_res_coll T_TABLE_COLL;
  l_index number;
BEGIN
  l_res_coll := T_TABLE_COLL();
  FOR I IN (SELECT col1, col2 FROM Table1)
  LOOP
    IF I.col1 = 1 THEN
      l_res_coll.extend;
      l_index := l_res_coll.count;  
      l_res_coll(l_index):= T_TABLE(i.col1, i.col2);
    END IF;
  END LOOP;
  return l_res_coll;
END;

作用中的作用

select *
  from table(FN_MyFunction())

要获取有关什么是集合以及如何使用它们的更多信息，请阅读 这个

To get more information about what collections are and how to use them read this

相关文章