如何用表存储的列表替代REGEXP_LIKE或LIKE的多值匹配？

原SQL通过正则匹配前缀筛选表：

SELECT
   OBJECT_NAME  
FROM ALL_OBJECTS 
WHERE
    REGEXP_LIKE ( OBJECT_NAME,
                  '^table1|^table2|^table3|..'
                  )
    AND OBJECT_TYPE = 'TABLE'

为避免编写冗长的正则表达式，已创建存储目标表名（或前缀）的TABLE_WITH_LIST表，期望实现类似如下逻辑的查询：

SELECT
   OBJECT_NAME  
FROM ALL_OBJECTS 
WHERE OBJECT_NAME LIKE (SELECT NAME FROM TABLE_WITH_LIST)
    AND OBJECT_TYPE = 'TABLE'

询问是否有可行的实现方法。

方法1：前缀匹配（对应原正则逻辑） #

如果TABLE_WITH_LIST中存储的是表名前缀（如table1、table2），用EXISTS子句结合通配符实现前缀匹配：

SELECT ao.OBJECT_NAME
FROM ALL_OBJECTS ao
WHERE ao.OBJECT_TYPE = 'TABLE'
  AND EXISTS (
    SELECT 1
    FROM TABLE_WITH_LIST twl
    WHERE ao.OBJECT_NAME LIKE twl.NAME || '%'
  );

twl.NAME || '%'拼接出前缀匹配规则，和原正则的^tableX效果一致。

方法2：精确表名匹配 #

如果TABLE_WITH_LIST中存储的是完整表名，直接用IN子句即可：

SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
  AND OBJECT_NAME IN (SELECT NAME FROM TABLE_WITH_LIST);

这种方式性能更优，属于精确匹配。

方法3：动态生成正则表达式 #

如果想完全复用原正则的匹配逻辑，可通过聚合函数将表中数据拼接成正则串：

SELECT ao.OBJECT_NAME
FROM ALL_OBJECTS ao
CROSS JOIN (
  SELECT '^(' || LISTAGG(twl.NAME, '|') WITHIN GROUP (ORDER BY twl.NAME) || ')' AS regex_pattern
  FROM TABLE_WITH_LIST twl
) regex
WHERE ao.OBJECT_TYPE = 'TABLE'
  AND REGEXP_LIKE(ao.OBJECT_NAME, regex.regex_pattern);

LISTAGG会把表中所有名称拼接成^table1|^table2|^table3格式的正则表达式，和原SQL逻辑完全一致。若表中数据量较大，需注意LISTAGG的长度限制（Oracle中可通过ON OVERFLOW TRUNCATE或调整相关参数处理）。

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