将数据数组作为输入参数传递给Oracle过程 [英] Passing an array of data as an input parameter to an Oracle procedure
问题描述
我正在尝试将(varchar
)数据数组传递给Oracle过程.可以从SQL * Plus或从另一个PL/SQL过程调用Oracle过程,如下所示:
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:
BEGIN
pr_perform_task('1','2','3','4');
END;
pr_perform_task
将读取每个输入参数并执行任务.
pr_perform_task
will read each of the input parameters and perform the tasks.
我不确定该如何实现.我的第一个想法是使用类型为varray
的输入参数,但是当过程定义如下时,却出现了Error: PLS-00201: identifier 'VARRAY' must be declared
错误:
I'm not sure as to how I can achieve this. My first thought was to use an input parameter of type varray
but I'm getting Error: PLS-00201: identifier 'VARRAY' must be declared
error, when the procedure definiton looks like this:
CREATE OR REPLACE PROCEDURE PR_DELETE_RECORD_VARRAY(P_ID VARRAY) IS
总而言之,如何将数据作为数组传递,让SP遍历每个参数并执行任务?
To summarize, how can I pass the data as an array, let the SP loop through each of the parameters and perform the task ?
我正在使用Oracle 10gR2作为数据库.
I'm using Oracle 10gR2 as my database.
推荐答案
这是一种实现方法:
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
要扩大我对@dcp答案的评论,如果想要使用关联数组,可以按照以下方法实现那里提出的解决方案:
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:
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>
这需要创建一个独立的Oracle TYPE(不能是关联数组),并要求定义一个包,所有人都可以看到它,以便定义的TYPE可以被所有人使用.
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过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!