将数据数组作为输入参数传递给 Oracle 过程

2021-12-05 00:00:00 oracle stored-procedures plsql

我正在尝试将一组 (varchar) 数据传递到 Oracle 过程中.Oracle 过程可以从 SQL*Plus 或另一个 PL/SQL 过程调用，如下所示:

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债看起来像这样:

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:

创建或替换程序 PR_DELETE_RECORD_VARRAY(P_ID VARRAY) 是

总而言之，如何将数据作为数组传递，让 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.

相关文章