对应于Postgres函数TEXT[]参数和ANY操作符?

Question

我正在将Postgres PLPGSQL函数移植到Oracle存储过程中，但是它有一个TEXT[]数组作为参数，然后在ANY操作中使用这个参数-特别是:

create or replace function func_name
(
...
in_array_param text[],
...
) 
as $$
declare 
begin
...
select column_1,
column_2 
from table_x           
where (column_a = value)
and (column_b = any(in_array_param));
...
end;

我不记得Oracle历史上有类似的(在SQL中使用PL/SQL数组)，但随着PL/SQL的发展，我想知道现在是否有这样做的选项?我使用Oracle 19c，但可以移动到21c，如果有一个新的结构，将有助于。

我已经尝试使用VARRAY，并使用IN操作符:

type my_array_type is varray(10) of varchar2(255);
var_array my_array_type := my_array_type('1', '2', '3');
procedure my_test(param_a varchar2, param_b my_array_type) is
var_col test_tab.col2%type;
begin
select col2 into var_col
from test_tab
where col1 in param_b;
end my_test;
my_test('2', var_array);

这会导致PLS-00642错误:

ORA-06550: line 11, column 21:
PLS-00642: local collection types not allowed in SQL statements

Answer 1

看一下下面的例子。

使用内置数据类型(使用它;如果没有必要，为什么要创建自己的呢?)过程搜索员工，并在作为集合传递的工作列表中查找最后一个(按字母顺序)员工姓名。

你要找的是第10行。

SQL> create or replace procedure p_test
2    (param_a in number, param_b in sys.odcivarchar2list)
3  is
4    l_ename emp.ename%type;
5  begin
6    select max(e.ename)
7      into l_ename
8      from emp e
9      where e.deptno = param_a
10        and e.job in (select * from table(param_b));
11    dbms_output.put_line('Result: ' || l_ename);
12  end;
13  /
Procedure created.

样本数据:

SQL> select ename, job from emp where deptno = 20 order by job, ename;
ENAME      JOB
---------- ---------
FORD       ANALYST         --> this
SCOTT      ANALYST         --> this
ADAMS      CLERK
SMITH      CLERK
JONES      MANAGER         --> this

在分析师和经理中谁是最后一个?这些是福特，琼斯和斯科特，所以-斯科特是:

SQL> set serveroutput on
SQL> exec p_test(param_a => 20, param_b => sys.odcivarchar2list('ANALYST', 'MANAGER'));
Result: SCOTT
PL/SQL procedure successfully completed.
SQL>