如何在Oracle PL/SQL的WHERE IN子句中使用关联数组？

关联数组是PL/SQL专属类型，SQL引擎无法直接识别，因此不能直接在IN子句中使用。需要先将其转换为SQL能处理的结构，以下是两种可行方案：

方案一：转换为SQL嵌套表类型使用 #

1. 先在SQL级别定义一个嵌套表类型（必须是SQL级，不能在PL/SQL块内定义）：

CREATE OR REPLACE TYPE Ids_t AS TABLE OF NUMBER;
/

2. 修改存储过程，将输入的关联数组遍历赋值到这个SQL嵌套表中，再通过TABLE()函数将其转为行集供查询使用：

PROCEDURE GetData(Ids IN Ids_t_a, results OUT SYS_REFCURSOR) IS
  v_ids Ids_t := Ids_t(); -- 初始化SQL嵌套表
BEGIN
  -- 遍历关联数组，将元素导入嵌套表
  FOR i IN Ids.FIRST .. Ids.LAST LOOP
    v_ids.EXTEND;
    v_ids(v_ids.COUNT) := Ids(i);
  END LOOP;

  -- 用TABLE()函数将嵌套表转为行集，实现IN查询
  OPEN results FOR
    SELECT Id, Name 
    FROM Person p 
    WHERE p.Id IN (SELECT COLUMN_VALUE FROM TABLE(v_ids));
END;
/

方案二：使用临时表（适合大量ID场景） #

如果传入的ID数量较多，用临时表的方式性能更稳定：

PROCEDURE GetData(Ids IN Ids_t_a, results OUT SYS_REFCURSOR) IS
BEGIN
  -- 创建会话级临时表（仅当前会话可见，提交后自动清空）
  EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE temp_ids(id NUMBER) ON COMMIT DELETE ROWS';
  
  -- 清空临时表，避免会话残留数据
  DELETE FROM temp_ids;
  
  -- 将关联数组的ID插入临时表
  FOR i IN Ids.FIRST .. Ids.LAST LOOP
    INSERT INTO temp_ids VALUES(Ids(i));
  END LOOP;
  
  -- 基于临时表执行查询
  OPEN results FOR
    SELECT Id, Name 
    FROM Person p 
    WHERE p.Id IN (SELECT id FROM temp_ids);
END;
/

注意事项 #

• 方案一中的Ids_t是SQL级类型，必须提前创建，否则SQL引擎无法识别。
• 小量ID用方案一更高效，大量ID建议用方案二。

内容的提问来源于stack exchange，提问作者Mr. Boy