oracle 如何在oracle存储过程中填充数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3988636/
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 populate an array in an oracle stored procedure?
提问by Niraj Choubey
How to use array( Varray) in store procedure. Actually,i have make a stored procedure from which i retrieve a list of elements.
如何在存储过程中使用数组（Varray）。实际上，我制作了一个存储过程，从中检索元素列表。
For example:
例如：
create or replace procedure GetTargetFields ( fileformat in varchar2,
filefields out Varray(4) )
IS
BEGIN
SELECT id
INTO filefields
FROM tablename;
END;
回答by Vincent Malgrat
use BULK COLLECT INTO:
使用BULK COLLECT INTO：
SQL> CREATE OR REPLACE TYPE vrray_4 AS VARRAY(4) OF VARCHAR2(10);
2 /
Type created
SQL> CREATE OR REPLACE PROCEDURE GetTargetFields(fileformat IN VARCHAR2,
2 filefields OUT vrray_4) IS
3 BEGIN
4 SELECT dummy BULK COLLECT INTO filefields FROM dual;
5 END;
6 /
Procedure created
SQL> DECLARE
2 x vrray_4;
3 BEGIN
4 GetTargetFields(NULL, x);
5 END;
6 /
PL/SQL procedure successfully completed
Also make sure that your query doesn't return more than 4 rows (for a VARRAY(4)) or you will run into ORA-22165
还要确保您的查询不返回超过 4 行（对于 a VARRAY(4)），否则您将遇到 ORA-22165
回答by andr
Niraj. You should use the principles Vincent provided, but I suggest you use nested table type instead of varray in case you don't need exactlyvarray type in your logic. This will save you from ORA-22165 error if the query returns more then 4 rows - nested tabled will be automatically expanded to the size needed. You define nested table type as follows:
尼拉吉。您应该使用 Vincent 提供的原则，但我建议您使用嵌套表类型而不是 varray，以防您的逻辑中不需要完全使用varray 类型。如果查询返回超过 4 行，这将使您免于 ORA-22165 错误 - 嵌套表将自动扩展到所需的大小。您可以按如下方式定义嵌套表类型：
declare
type TStrTab is table of varchar2(10);
fStrTab TStrTab := TStrTab();
begin
select ... bulk collect into fStrTab from...
end;
More information about PL/SQL collection types can be found in official Oracle PL-SQL User's Guide and Reference Chapter 5.
有关 PL/SQL 集合类型的更多信息可以在官方 Oracle PL-SQL 用户指南和参考第 5 章中找到。
回答by Dave Costa
Two things:
两件事情：
You need to declare a named type -- you can't use VARRAY directly in a parameter declaration. (Unless this has changed in 11g.)
您需要声明一个命名类型——不能在参数声明中直接使用 VARRAY。（除非这在 11g 中发生了变化。）
You need to use BULK COLLECT to use a single query to populate a collection.
您需要使用 BULK COLLECT 来使用单个查询来填充集合。
Example:
例子：
CREATE TYPE fieldlist AS VARRAY(4) OF NUMBER;
CREATE PROCEDURE GetTargetFields( filefields OUT fieldlist )
AS
BEGIN
SELECT id BULK COLLECT INTO filefields FROM tablename;
END;
