从 Oracle 函数返回表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12338573/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Returning a table from an Oracle function
提问by Matt
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.
我在这里查看了许多解决方案来尝试解决这个问题，它们已经走了很远，但现在我陷入了一些我似乎无法克服的错误中。
I am on Oracle 11g. I need a function to return a record set (table). Here is the code I'm using:
我在 Oracle 11g 上。我需要一个函数来返回一个记录集（表）。这是我正在使用的代码：
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;
The errors I receive from this are:
我从中收到的错误是：
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: invalid use of type name or subtype name on the RETURN line
FUNCTION FN_MyFunction 行和 PL/SQL 上的语句被忽略：ORA-04044：过程、函数、包或类型在每行 INSERT INTO T_TABLE_COLL 行上都不允许
PLS-00330：在 RETURN 行上无效使用类型名称或子类型名称
What am I doing wrong with the table types?
我对表格类型做错了什么？
回答by Nick Krasnov
T_TABLE_COLLis a collection. You cannot use inserton collections.
T_TABLE_COLL是一个集合。您不能在集合上使用插入。
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;
Function in action
功能在行动
select *
from table(FN_MyFunction())
To get more information about what collections are and how to use them read this
要获取有关什么是集合以及如何使用它们的更多信息，请阅读本文
