将数据数组作为输入参数传递给 Oracle 过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2885575/
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
Passing an array of data as an input parameter to an Oracle procedure
提问by Sathyajith Bhat
I'm trying to pass an array of (varchar) data into an Oracle procedure. The Oracle procedure would be either called from SQL*Plus or from another PL/SQL procedure like so:
我正在尝试将 ( varchar) 数据数组传递到 Oracle 过程中。Oracle 过程将从 SQL*Plus 或另一个 PL/SQL 过程调用，如下所示：
BEGIN
pr_perform_task('1','2','3','4');
END;
pr_perform_taskwill read each of the input parameters and perform the tasks.
pr_perform_task将读取每个输入参数并执行任务。
I'm not sure as to how I can achieve this. My first thought was to use an input parameter of type varraybut I'm getting Error: PLS-00201: identifier 'VARRAY' must be declarederror, when the procedure definiton looks like this:
我不确定如何实现这一目标。我的第一个想法是使用类型的输入参数，但当过程定义如下所示时，varray我收到Error: PLS-00201: identifier 'VARRAY' must be declared错误消息：
CREATE OR REPLACE PROCEDURE PR_DELETE_RECORD_VARRAY(P_ID VARRAY) IS
CREATE OR REPLACE PROCEDURE PR_DELETE_RECORD_VARRAY(P_ID VARRAY) IS
To summarize, how can I pass the data as an array, let the SP loop through each of the parameters and perform the task ?
总而言之，如何将数据作为数组传递，让 SP 循环遍历每个参数并执行任务？
I'm using Oracle 10gR2 as my database.
我使用 Oracle 10gR2 作为我的数据库。
回答by DCookie
This is one way to do it:
这是一种方法：
SQL> set serveroutput on
SQL> CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50);
2 /
Type created
SQL> CREATE OR REPLACE PROCEDURE testing (t_in MyType) IS
2 BEGIN
3 FOR i IN 1..t_in.count LOOP
4 dbms_output.put_line(t_in(i));
5 END LOOP;
6 END;
7 /
Procedure created
SQL> DECLARE
2 v_t MyType;
3 BEGIN
4 v_t := MyType();
5 v_t.EXTEND(10);
6 v_t(1) := 'this is a test';
7 v_t(2) := 'A second test line';
8 testing(v_t);
9 END;
10 /
this is a test
A second test line
To expand on my comment to @dcp's answer, here's how you could implement the solution proposed there if you wanted to use an associative array:
为了扩展我对@dcp 的回答的评论，如果您想使用关联数组，这里是如何实现那里提出的解决方案：
SQL> CREATE OR REPLACE PACKAGE p IS
2 TYPE p_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
3
4 PROCEDURE pp (inp p_type);
5 END p;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p IS
2 PROCEDURE pp (inp p_type) IS
3 BEGIN
4 FOR i IN 1..inp.count LOOP
5 dbms_output.put_line(inp(i));
6 END LOOP;
7 END pp;
8 END p;
9 /
Package body created
SQL> DECLARE
2 v_t p.p_type;
3 BEGIN
4 v_t(1) := 'this is a test of p';
5 v_t(2) := 'A second test line for p';
6 p.pp(v_t);
7 END;
8 /
this is a test of p
A second test line for p
PL/SQL procedure successfully completed
SQL>
This trades creating a standalone Oracle TYPE (which cannot be an associative array) with requiring the definition of a package that can be seen by all in order that the TYPE it defines there can be used by all.
这需要创建一个独立的 Oracle TYPE（不能是关联数组），并要求定义一个所有人都可以看到的包，以便所有人都可以使用它在那里定义的 TYPE。
回答by dcp
If the types of the parameters are all the same (varchar2for example), you can have a package like this which will do the following:
如果参数的类型都相同（varchar2例如），你可以有一个这样的包，它将执行以下操作：
CREATE OR REPLACE PACKAGE testuser.test_pkg IS
TYPE assoc_array_varchar2_t IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
PROCEDURE your_proc(p_parm IN assoc_array_varchar2_t);
END test_pkg;
CREATE OR REPLACE PACKAGE BODY testuser.test_pkg IS
PROCEDURE your_proc(p_parm IN assoc_array_varchar2_t) AS
BEGIN
FOR i IN p_parm.first .. p_parm.last
LOOP
dbms_output.put_line(p_parm(i));
END LOOP;
END;
END test_pkg;
Then, to call it you'd need to set up the array and pass it:
然后，要调用它，您需要设置数组并传递它：
DECLARE
l_array testuser.test_pkg.assoc_array_varchar2_t;
BEGIN
l_array(0) := 'hello';
l_array(1) := 'there';
testuser.test_pkg.your_proc(l_array);
END;
/
