Oracle中如何使用用户定义类型PE_REG.CRM_IDS进行查询匹配？

这个问题我之前也碰到过——Oracle对于自定义VARRAY这类集合类型，确实不能直接用=来做等值判断，得换几种方式来处理：

先说说报错原因 #

你遇到的ORA-00932错误，核心原因是Oracle不支持直接用=运算符比较VARRAY这种集合类型。虽然查询结果显示成PE_REG.CRM_IDS('10035005')的格式，但数据库并不会把它当成普通字符串或标量类型来处理，直接用=会触发类型不匹配的报错。

方法1：针对单元素VARRAY直接访问索引（最适合你的场景） #

从你查询到的结果来看，这个VARRAY里只有一个元素。这种情况下可以直接通过索引访问元素，然后做等值判断：

SELECT * 
FROM purecov_summary 
WHERE nav_crm_id(1) = '10035005';

⚠️ 注意：如果VARRAY可能为空或者包含多个元素，建议先加过滤条件，比如：

SELECT * 
FROM purecov_summary 
WHERE nav_crm_id IS NOT NULL 
  AND CARDINALITY(nav_crm_id) = 1 
  AND nav_crm_id(1) = '10035005';

方法2：使用MEMBER OF检查元素是否存在 #

如果你的VARRAY可能包含多个元素，只是想查询包含某个特定值的行，可以用MEMBER OF操作符：

SELECT * 
FROM purecov_summary 
WHERE '10035005' MEMBER OF nav_crm_id;

这个语句会返回所有nav_crm_id集合中包含10035005的行，不管集合里有多少个元素。

方法3：自定义比较函数（完全匹配整个集合） #

如果你需要判断两个VARRAY是否完全相等（元素顺序、数量、值都一致），可以自定义一个比较函数：
首先创建函数：

CREATE OR REPLACE FUNCTION compare_crm_ids(
    p_crm1 PE_REG.CRM_IDS,
    p_crm2 PE_REG.CRM_IDS
) RETURN NUMBER IS
BEGIN
    -- 先判断空值和元素数量
    IF p_crm1 IS NULL AND p_crm2 IS NULL THEN
        RETURN 1;
    ELSIF p_crm1 IS NULL OR p_crm2 IS NULL THEN
        RETURN 0;
    ELSIF p_crm1.COUNT != p_crm2.COUNT THEN
        RETURN 0;
    ELSE
        -- 逐个元素比较
        FOR i IN 1..p_crm1.COUNT LOOP
            IF p_crm1(i) != p_crm2(i) THEN
                RETURN 0;
            END IF;
        END LOOP;
        RETURN 1;
    END IF;
END;
/

然后在查询中使用：

SELECT * 
FROM purecov_summary 
WHERE compare_crm_ids(nav_crm_id, PE_REG.CRM_IDS('10035005')) = 1;

方法4：使用SET()转换后比较（忽略顺序和重复） #

如果你只关心两个集合的元素是否相同（不考虑顺序和重复），可以用SET()函数把VARRAY转成嵌套表后比较：

SELECT * 
FROM purecov_summary 
WHERE SET(nav_crm_id) = SET(PE_REG.CRM_IDS('10035005'));

⚠️ 注意：SET()会自动去重并排序，所以如果你的业务逻辑要求严格匹配顺序和重复元素，这个方法不适用。

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