PL/SQL中对字符串执行集合运算：找出未选中的菜单选项

针对需求（从变量形式的逗号分隔有效菜单中，排除用户选择里的有效项，返回未被选择的选项并赋值给变量），以下是几种可行的实现方式：

方法一：嵌套表集合 + 集合操作 #

利用PL/SQL的嵌套表拆分字符串，通过集合的差集操作得到结果，逻辑清晰易扩展：

DECLARE
  -- 定义字符串类型的嵌套表
  TYPE str_table_type IS TABLE OF VARCHAR2(10);
  -- 输入变量（对应案例1）
  v_onmenu VARCHAR2(100) := '06,02,00,03';
  v_choice VARCHAR2(100) := '06,01,00';
  -- 中间集合变量
  v_menu_items str_table_type;
  v_valid_choices str_table_type;
  v_remaining_items str_table_type;
  -- 结果变量
  v_result VARCHAR2(100);
BEGIN
  -- 拆分菜单选项为集合
  SELECT REGEXP_SUBSTR(v_onmenu, '[^,]+', 1, LEVEL)
  BULK COLLECT INTO v_menu_items
  FROM DUAL
  CONNECT BY LEVEL <= REGEXP_COUNT(v_onmenu, ',') + 1;

  -- 提取选择中的有效选项（即同时存在于菜单中的项）
  SELECT DISTINCT REGEXP_SUBSTR(v_choice, '[^,]+', 1, LEVEL)
  BULK COLLECT INTO v_valid_choices
  FROM DUAL
  CONNECT BY LEVEL <= REGEXP_COUNT(v_choice, ',') + 1
  WHERE REGEXP_SUBSTR(v_choice, '[^,]+', 1, LEVEL) MEMBER OF v_menu_items;

  -- 计算差集：菜单选项减去已选有效项
  v_remaining_items := v_menu_items MULTISET EXCEPT v_valid_choices;

  -- 将集合拼接为逗号分隔的字符串
  IF v_remaining_items IS NOT EMPTY THEN
    SELECT LISTAGG(column_value, ',') WITHIN GROUP (ORDER BY column_value)
    INTO v_result
    FROM TABLE(v_remaining_items);
  END IF;

  -- 输出结果（可替换为赋值给业务变量）
  DBMS_OUTPUT.PUT_LINE(v_result); -- 案例1输出：02,03
END;
/

方法二：APEX_STRING工具包（适用于Oracle APEX环境） #

如果使用Oracle APEX，APEX_STRING提供了现成的字符串拆分函数，代码更简洁：

DECLARE
  -- 输入变量（对应案例2）
  v_onmenu VARCHAR2(100) := '06,02,00';
  v_choice VARCHAR2(100) := '00,01,06';
  -- 结果变量
  v_result VARCHAR2(100);
BEGIN
  -- 直接通过集合筛选拼接结果
  SELECT LISTAGG(column_value, ',') WITHIN GROUP (ORDER BY column_value)
  INTO v_result
  FROM TABLE(APEX_STRING.SPLIT(v_onmenu, ','))
  WHERE column_value NOT IN (
    SELECT column_value
    FROM TABLE(APEX_STRING.SPLIT(v_choice, ','))
    WHERE column_value IN (SELECT column_value FROM TABLE(APEX_STRING.SPLIT(v_onmenu, ',')))
  );

  DBMS_OUTPUT.PUT_LINE(v_result); -- 案例2输出：02
END;
/

方法三：纯正则表达式（无需集合，适合简单场景） #

通过正则替换直接移除已选的有效项，无需定义额外类型，适合短字符串处理：

DECLARE
  -- 输入变量（对应案例1）
  v_onmenu VARCHAR2(100) := '06,02,00,03';
  v_choice VARCHAR2(100) := '06,01,00';
  -- 结果变量
  v_result VARCHAR2(100);
BEGIN
  -- 构建匹配已选有效项的正则模式，替换为空
  v_result := REGEXP_REPLACE(
    v_onmenu,
    '(^|,)(' || REGEXP_REPLACE(v_choice, '([^,]+)', '\1|') || ')(,|$)',
    '\3',
    1,
    0,
    'c'
  );
  -- 清理多余的逗号（开头、结尾、连续逗号）
  v_result := TRIM(BOTH ',' FROM REGEXP_REPLACE(v_result, ',{2,}', ','));

  DBMS_OUTPUT.PUT_LINE(v_result); -- 案例1输出：02,03
END;
/

适用场景说明 #

• 集合方法：适合复杂字符串处理，支持去重、排序等扩展需求，兼容性好（无需APEX）。
• APEX_STRING方法：代码最简洁，依赖Oracle APEX环境。
• 正则方法：无需额外类型定义，代码量少，但长字符串场景下效率略低，正则模式构建需注意边界处理。

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