如何比较两个集合_MULTISET运算符处理集合交集与差集-Oracle-PHP中文网

multiset运算符不能直接用=或in比较集合，需用multiset except双向为空判断相等；intersect/union按重复次数取min/max；未初始化嵌套表会报ora-06530；性能敏感场景建议用table()展开+标准sql替代。

sql 中 `multiset` 运算符不支持直接用 `=` 或 `in` 比较两个集合

oracle 和一些支持对象关系特性的数据库里，multiset 是用来操作嵌套表（nested table）的集合运算符，但它本身不是“可比较值”，而是返回一个新集合。你不能写 col1 multiset intersect col2 = col3 这种表达式——会报 ora-00920: invalid relational operator。

真正能判断“两个多集是否相等”的方式，是用集合运算后做空性检查：

MULTISET EXCEPT 两次：若 A MULTISET EXCEPT B 为空且 B MULTISET EXCEPT A 也为空，则 A ≡ B（元素及重复次数完全一致）
注意：这要求两个嵌套表类型完全兼容（同类型、同元素类型），否则编译就失败
如果只关心“是否有交集”，用 CARDINALITY(A MULTISET INTERSECT B) > 0 更直观，但要注意 CARDINALITY 对空集合返回 NULL，得用 NVL 包一层

`MULTISET INTERSECT` 和 `MULTISET UNION` 的重复计数逻辑容易误解

它们不是数学集合运算，而是“多重集（multiset）”运算：重复元素按最小/最大出现次数保留。

比如 NESTED_TABLE('a','a','b') MULTISET INTERSECT NESTED_TABLE('a','b','b','b') 结果是 NESTED_TABLE('a','b')（每个元素取 min(2,1)=1 和 min(1,3)=1 次），不是 NESTED_TABLE('a') 也不是去重后的 NESTED_TABLE('a','b') —— 看似一样，但语义不同。

MULTISET INTERSECT：各元素保留其在两边出现次数的较小值
MULTISET UNION：默认是 UNION ALL 语义（叠加次数），加 UNION DISTINCT 才去重，但 Oracle 不支持这个语法；真要“并集去重”，得先 SET() 转成单值集合再 union
MULTISET EXCEPT：A EXCEPT B = A 中去掉“B里有的那些次数”，即每个元素保留 max(0, count_A − count_B)

在 PL/SQL 函数或 WHERE 子句中用 `MULTISET` 前必须确认嵌套表已初始化

未初始化的嵌套表变量（如声明了但没赋值 := empty_type()）在参与 MULTISET 运算时会触发 ORA-06530: Reference to uninitialized composite，而不是返回空结果。

所有嵌套表变量在使用前必须显式初始化，哪怕只是 my_nt := my_nt_type();
查询中从表查出的嵌套表列，如果源行该字段为 NULL，也会导致同样错误；需用 NVL(col, my_nt_type()) 防御
在 WHERE 里用 MULTISET 表达式会导致全表扫描（无法走索引），性能敏感场景建议提前物化或改用 JOIN 模拟

替代方案：不用 `MULTISET` 也能实现交集/差集逻辑

多数业务场景下，硬上 MULTISET 反而增加维护成本和隐式转换风险。更稳的做法是展开嵌套表，用标准 SQL 关联处理。

例如：要找两个用户权限集合的差集（用户 A 有但用户 B 没有的权限），与其写：

SELECT * FROM users u
WHERE u.id = :a_id
AND CARDINALITY(
  u.permissions MULTISET EXCEPT (
    SELECT permissions FROM users WHERE id = :b_id
  )
) > 0

不如用：

SELECT a.perm FROM TABLE(SELECT permissions FROM users WHERE id = :a_id) a
MINUS
SELECT b.perm FROM TABLE(SELECT permissions FROM users WHERE id = :b_id) b

TABLE() 展开后可用 MINUS/INTERSECT，语义清晰、执行计划可控、支持索引
如果嵌套表很大，记得在嵌套表类型上建 NESTED TABLE ... STORE AS 并对存储表加索引
PL/SQL 中频繁做集合运算？考虑用关联数组（TYPE t_arr IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER）+ 循环比对，比嵌套表 + MULTISET 快得多，也更容易调试

真正卡住人的从来不是语法能不能写出来，而是没意识到 MULTISET 返回的是类型受限的新集合，不是布尔值，也不能短路。一旦当成条件表达式直觉使用，错得悄无声息。