Oracle存储过程使用NUMBER集合作为SQL IN条件报错如何解决

报错原因说明 #

你遇到的两个报错都是PL/SQL集合使用的基础语法问题，根因如下：

• ORA-00932 数据类型不一致：普通SELECT ... INTO语法只能接收单行查询结果，你要把多行ITEM_ID存入集合，必须使用批量收集语法，不能直接用普通INTO赋值。
• PLS-00642 本地集合类型不允许在SQL中使用：Oracle 12c之前的版本不支持将PL/SQL块内自定义的嵌套表/数组类型直接用在SQL语句中，使用系统预置的schema级集合类型即可避开这个问题。
  另外你原代码还有几个基础语法疏漏：IF判断后缺少THEN关键字、SQL语句结尾漏写分号，这些也会直接导致编译失败。

修正后的集合实现代码 #

直接使用系统自带的SYS.ODCINUMBERLIST（NUMBER类型内置变长数组）存储ID集合，注意两点：

1. 多行结果赋值给集合必须用BULK COLLECT INTO
2. SQL中引用集合作为过滤源时，需要用TABLE()函数将集合转换为关系表结构，内置集合的数值列固定名为COLUMN_VALUE

CREATE OR REPLACE PROCEDURE GET_ITEMS(
    P_CR OUT SYS_REFCURSOR,
    IN_ITEM_TYPE VARCHAR2
)
IS
    V_INVENTORY_ITEMS SYS.ODCINUMBERLIST;
BEGIN
    IF IN_ITEM_TYPE = 'TYPE1' THEN
        SELECT ITEM_ID 
        BULK COLLECT INTO V_INVENTORY_ITEMS 
        FROM ITEM_MASTER
        WHERE CATEGORY IN ('CAT1', 'CAT2');
    ELSE
        SELECT ITEM_ID 
        BULK COLLECT INTO V_INVENTORY_ITEMS 
        FROM ITEM_MASTER
        WHERE CATEGORY IN ('CAT3', 'CAT4');
    END IF;

    OPEN P_CR FOR
        SELECT * FROM ORDER_LINES
        WHERE ITEM_ID IN (
            SELECT COLUMN_VALUE FROM TABLE(V_INVENTORY_ITEMS)
        );
END GET_ITEMS;
/

更简洁的无集合实现方案 #

如果你不需要对收集到的ITEM_ID集合做额外的中间处理，完全可以不用定义集合变量，直接在游标查询中通过子查询完成过滤，逻辑和需求完全一致，还能避开所有集合相关的语法坑：

CREATE OR REPLACE PROCEDURE GET_ITEMS(
    P_CR OUT SYS_REFCURSOR,
    IN_ITEM_TYPE VARCHAR2
)
IS
BEGIN
    OPEN P_CR FOR
        SELECT ol.* 
        FROM ORDER_LINES ol
        WHERE ol.ITEM_ID IN (
            SELECT im.ITEM_ID
            FROM ITEM_MASTER im
            WHERE 
                (IN_ITEM_TYPE = 'TYPE1' AND im.CATEGORY IN ('CAT1', 'CAT2'))
                OR 
                (NVL(IN_ITEM_TYPE,'x') <> 'TYPE1' AND im.CATEGORY IN ('CAT3', 'CAT4'))
        );
END GET_ITEMS;
/

内容的提问来源于stack exchange，提问作者Smitty-Werben-Jager-Manjenson