如何在PL/SQL存储过程多查询的WHERE子句中复用自定义列表？

你遇到的问题是因为PL/SQL自定义的嵌套表类型属于PL/SQL私有类型，SQL引擎无法直接识别，所以不能直接在IN子句中使用。下面是几种实用的解决方法：

方法1：使用系统预定义的SQL级集合类型 #

Oracle提供了SYS.ODCIVARCHAR2LIST这种全局SQL级别的字符串集合类型，SQL和PL/SQL都能识别，直接替换你的自定义类型即可：

declare
  v_aa SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('bat', 'cat', 'dog', 'turtle', 'monkey');
begin
  for r_loop in (select animal_name
                   from animals
                  where animal_name in (select column_value from table(v_aa)))
  loop   
    dbms_output.put_line(r_loop.animal_name);
  end loop;

  for r_loop in (select pet_name
                   from pets
                  where pet_type in (select column_value from table(v_aa)))
  loop   
    dbms_output.put_line(r_loop.pet_name);
  end loop;
end;
/

这里通过TABLE()函数将集合转换为SQL可查询的数据集，再用子查询取出元素匹配IN子句。

方法2：使用MEMBER OF操作符 #

如果不想用系统类型，保留自定义PL/SQL嵌套表，可以用MEMBER OF替代IN，它支持PL/SQL类型的集合匹配：

declare
  type t_aa is table of varchar2(12);
  v_aa t_aa := t_aa('bat', 'cat', 'dog', 'turtle', 'monkey');
begin
  for r_loop in (select animal_name
                   from animals
                  where animal_name member of v_aa)
  loop   
    dbms_output.put_line(r_loop.animal_name);
  end loop;

  for r_loop in (select pet_name
                   from pets
                  where pet_type member of v_aa)
  loop   
    dbms_output.put_line(r_loop.pet_name);
  end loop;
end;
/

注意：MEMBER OF是PL/SQL支持的语法，只能在PL/SQL块内的查询中使用。

方法3：创建全局SQL级自定义类型 #

如果需要在多个存储过程或SQL语句中复用这个集合类型，可以先创建一个SQL级别的类型，之后就能像系统类型一样使用：

-- 先在SQL层创建类型（只需执行一次）
create or replace type t_aa as table of varchar2(12);
/

然后在PL/SQL中使用：

declare
  v_aa t_aa := t_aa('bat', 'cat', 'dog', 'turtle', 'monkey');
begin
  for r_loop in (select animal_name
                   from animals
                  where animal_name in (select column_value from table(v_aa)))
  loop   
    dbms_output.put_line(r_loop.animal_name);
  end loop;

  for r_loop in (select pet_name
                   from pets
                  where pet_type in (select column_value from table(v_aa)))
  loop   
    dbms_output.put_line(r_loop.pet_name);
  end loop;
end;
/

内容的提问来源于stack exchange，提问作者Shaun