oracle 如何从plsql存储过程返回集合类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14247779/
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
How to return collection type from plsql stored procedure
提问by javanoob
I have the below stored procedure in PL/SQL:
我在 PL/SQL 中有以下存储过程：
CREATE OR REPLACE PROCEDURE sample_procedure AS
DECLARE
TYPE list_of_names_t
IS TABLE OF emp.emp_index%TYPE;
ignoreIndexes LIST_OF_NAMES_T := List_of_names_t();
BEGIN
-- Logic here which fills the values in the collection ignoreIndexes
END;
When in invoke this stored procedure from outside like below:
当从外部调用此存储过程时，如下所示：
SET SERVEROUTPUT ON
EXEC sample_procedure
-- Line YY
@ Line YY, I want to retrieve the records from emp table where index is not there in the ignoreindexescollection which was prepared in the stored procedure.
@行YY，我想从ignoreindexes存储过程中准备的集合中没有索引的emp表中检索记录。
1) How to return the nested table ignoreindexeswhich was created in stored procedure to the outside world so that i can use the indexes in that table
1) 如何将ignoreindexes在存储过程中创建的嵌套表返回给外界，以便我可以使用该表中的索引
Thanks in Advance
提前致谢
回答by Justin Cave
First, they type would need to be declared outside the procedure in order for the type definition to be visible to code outside the procedure. You can either declare the type in SQL
首先，它们的类型需要在过程外声明，以便类型定义对过程外的代码可见。您可以在 SQL 中声明类型
CREATE TYPE list_of_names_t
AS TABLE OF NUMBER;
or you can declare it in PL/SQL
或者你可以在 PL/SQL 中声明它
CREATE OR REPLACE PACKAGE types_package
AS
TYPE list_of_names_t
IS TABLE OF emp.emp_index%type;
END;
Your procedure would then have to either use and return the SQL type
然后您的过程将不得不使用并返回 SQL 类型
CREATE OR REPLACE PROCEDURE sample_procedure(
p_ignore_indexes OUT list_of_names_t
)
AS
BEGIN
-- Logic here which fills the values in the collection p_ignore_indexes
END;
or the PL/SQL type
或 PL/SQL 类型
CREATE OR REPLACE PROCEDURE sample_procedure(
p_ignore_indexes OUT types_package.list_of_names_t
)
AS
BEGIN
-- Logic here which fills the values in the collection p_ignore_indexes
END;
Of course, if the purpose of your code is to return a collection, it would make more sense to write a function than a procedure
当然，如果你的代码的目的是返回一个集合，那么写一个函数比写一个过程更有意义
CREATE OR REPLACE FUNCTION sample_function
RETURN types_package.list_of_names_t
AS
ignore_indexes types_package.list_of_names_t;
BEGIN
-- Logic here which fills the values in the collection ignore_indexes
RETURN ignore_indexes;
END;
When you call the procedure, you would do something like
当你调用程序时，你会做类似的事情
DECLARE
l_ignore_indexes types_package.list_of_names_t;
BEGIN
l_ignore_indexes := sample_function;
-- Do something with l_ignore_indexes
END;
or
或者
DECLARE
l_ignore_indexes types_package.list_of_names_t;
BEGIN
sample_procedure( l_ignore_indexes );
-- Do something with l_ignore_indexes
END;
