如何从V$SQL中提取SQL连接语句引用的所有表或视图？

需求与示例 #

需从V$SQL中提取SQL语句所有JOIN子句里引用的表或视图：

示例SQL（来自V$SQL） #

select * from ADANPT 
join BTABP on column1=column2 
left outer join HSTUDI on column3=column4
full outer join TERW on column5=column6;

目标提取结果 #

BTABP
HSTUDI
TERW

问题 #

单条JOIN时，可使用substr和instr函数提取JOIN至ON之间的字符串，但多条JOIN时不知如何实现，是否需要使用循环？

解决方案：无需显式循环，用正则表达式+层级查询实现 #

在Oracle中，不需要编写PL/SQL循环，直接用正则表达式结合CONNECT BY层级查询就能批量提取所有JOIN子句中的表/视图，示例如下：

-- 模拟V$SQL中的SQL语句，实际使用时替换为V$SQL的查询
WITH sql_source AS (
    SELECT 'select * from ADANPT 
join BTABP on column1=column2 
left outer join HSTUDI on column3=column4
full outer join TERW on column5=column6;' AS sql_text FROM dual
)
SELECT TRIM(REGEXP_SUBSTR(sql_text, '(LEFT OUTER JOIN|RIGHT OUTER JOIN|FULL OUTER JOIN|JOIN)\s+(\w+)', 1, LEVEL, 'i', 2)) AS joined_table
FROM sql_source
CONNECT BY LEVEL <= REGEXP_COUNT(sql_text, '(LEFT OUTER JOIN|RIGHT OUTER JOIN|FULL OUTER JOIN|JOIN)\s+\w+', 1, 'i');

语句说明 #

1. REGEXP_COUNT：统计SQL中所有JOIN子句的数量，以此确定层级查询的遍历次数
2. REGEXP_SUBSTR：
   • 第5个参数'i'表示忽略大小写，兼容Join、LEFT outer JOIN等大小写不规范的写法
   • 第6个参数2表示提取正则表达式中第二个捕获组的内容，也就是JOIN关键字后的表/视图名称
3. TRIM：去除表名前后可能存在的空格
4. CONNECT BY：替代显式循环，自动遍历所有JOIN子句

扩展适配 #

如果SQL中存在带引号的表名（如"BTABP"），可以调整正则表达式，将(\w+)替换为(["]?\w+["]?)，兼容带引号的标识符。

实际对接V$SQL时，直接将sql_source替换为V$SQL的查询即可，例如：

SELECT TRIM(REGEXP_SUBSTR(sql_text, '(LEFT OUTER JOIN|RIGHT OUTER JOIN|FULL OUTER JOIN|JOIN)\s+(\w+)', 1, LEVEL, 'i', 2)) AS joined_table
FROM V$SQL
WHERE sql_id = '你的SQL_ID' -- 根据实际条件过滤
CONNECT BY LEVEL <= REGEXP_COUNT(sql_text, '(LEFT OUTER JOIN|RIGHT OUTER JOIN|FULL OUTER JOIN|JOIN)\s+\w+', 1, 'i')
AND PRIOR sql_id = sql_id
AND PRIOR SYS_GUID() IS NOT NULL; -- 避免层级查询出现笛卡尔积

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