Oracle中如何使用集合作为参数执行动态SQL？

你碰到的这个问题我之前踩过坑，核心原因是你定义的type my_type as table of number属于PL/SQL专属的块级别类型，没办法直接被SQL引擎识别——而EXECUTE IMMEDIATE最终是把动态语句交给SQL引擎执行的，SQL层面只认全局的SQL级别类型，所以才会抛出“表达式必须为SQL类型”的错误。

给你两个靠谱的解决方案：

方案1：创建SQL级别的集合类型 #

把集合类型定义成数据库全局的SQL类型，而不是在PL/SQL块内部定义。这样SQL引擎就能直接识别它了：

-- 先在数据库层面创建SQL类型
CREATE OR REPLACE TYPE my_type AS TABLE OF NUMBER;
/

-- 然后在PL/SQL块中使用
DECLARE
  v_collection my_type := my_type(1, 2, 3);
  v_result SYS_REFCURSOR;
BEGIN
  -- 动态查询中用TABLE()函数展开集合
  EXECUTE IMMEDIATE 'SELECT column_value FROM TABLE(:1)'
    INTO v_result
    USING v_collection;
  
  -- 这里可以添加游标处理逻辑，比如遍历结果
  -- ...
END;
/

如果是要在WHERE条件里用集合过滤，比如WHERE id IN (SELECT column_value FROM TABLE(:1))，也是同样的用法，只要确保集合是SQL级别类型即可。

方案2：使用Oracle内置的SQL集合类型 #

如果你不想创建全局类型，可以直接用Oracle自带的SQL级别集合类型，比如SYS.ODCINUMBERLIST（专门存数字列表的类型），省去自己定义的步骤：

DECLARE
  v_collection SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(1, 2, 3);
  v_result SYS_REFCURSOR;
BEGIN
  EXECUTE IMMEDIATE 'SELECT column_value FROM TABLE(:1)'
    INTO v_result
    USING v_collection;
END;
/

这类内置类型还有很多，比如SYS.ODCIVARCHAR2LIST用于字符串列表，都可以直接在SQL和PL/SQL之间通用。

简单总结一下：只要把集合换成SQL引擎能识别的全局SQL类型（不管是自己创建的还是Oracle内置的），就能解决PLS-00457的错误，让EXECUTE IMMEDIATE正常处理集合参数。

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