sql - ORACLE-SQL : stored procedure using array as parameter for select

Question

我必须在我的存储过程中使用一个数组。
所以我创建了一个类型变量:

CREATE OR REPLACE TYPE integer_array is table of number;

然后我尝试编写我的存储过程，但我无法编译它:

create or replace
PROCEDURE SP_TEST(
      i_id_profiles in integer_array, 
      o_clients OUT SYS_REFCURSOR
   )
AS
BEGIN
open o_clients for 
    FOR i IN 1..i_id_profiles.count LOOP
      select a.id_client from b_client a, i_client_profile b where a.id_client = b.id_client 
      and b.id_profile = i_id_profiles(i);
    END LOOP;
END SP_TEST;

你能帮我吗？我想获得我选择的 SYS_REFCURSOR。

谢谢

错误:

PLS-00103: Encountered the symbol "FOR" when expecting one of the following: ( - + case mod new not null select with
continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map

Answer 1

您为查询(静态或动态)打开引用游标，不能为 for loop 打开引用游标构造或任何类型的循环构造。它只是在语义上不正确。
此外，在这种情况下，根本不需要任何类型的循环。正如您创建的 integer_array作为 sql 类型(架构对象)，您可以使用 table运算符简单地从该类型的实例中进行选择(将其表示为表格)。因此，您的程序可能如下所示:

create or replace PROCEDURE SP_TEST(
      i_id_profiles in integer_array, 
      o_clients OUT SYS_REFCURSOR
   )
AS
BEGIN
  open o_clients for 
      select a.id_client 
         from b_client a 
         join i_client_profile b 
           on (a.id_client = b.id_client)
         join table(i_id_profiles)  s
           on b.id_profile = s.column_value;
END SP_TEST;