如何在PL/SQL中定位向指定表执行INSERT/MERGE操作的数据库对象

你之前用USER_DEPENDENCIES只能拿到依赖目标表的所有对象，但没法区分是只读操作还是写入操作。要精准找出执行INSERT或MERGE的对象，核心思路是扫描对象的源代码，判断是否包含目标操作语句。

下面是可行的PL/SQL脚本：

SET SERVEROUTPUT ON;
DECLARE
  v_table_name VARCHAR2(100) := 'DATREDPUB';
  v_found BOOLEAN;
BEGIN
  -- 遍历所有依赖目标表的对象（去重，避免包和包体重复）
  FOR dep IN (SELECT DISTINCT NAME, TYPE
              FROM USER_DEPENDENCIES
              WHERE REFERENCED_NAME = v_table_name
                AND REFERENCED_TYPE = 'TABLE'
                AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER'))
  LOOP
    v_found := FALSE;
    -- 逐行读取对象源代码并转为大写，兼容大小写差异
    FOR src IN (SELECT UPPER(TEXT) AS TEXT
                FROM USER_SOURCE
                WHERE NAME = dep.NAME
                  AND TYPE = dep.TYPE
                ORDER BY LINE)
    LOOP
      -- 检查是否包含INSERT/MERGE语句，同时排除注释中的无效匹配
      IF (INSTR(src.TEXT, 'INSERT INTO ' || UPPER(v_table_name)) > 0 OR
          INSTR(src.TEXT, 'MERGE INTO ' || UPPER(v_table_name)) > 0) THEN
        -- 跳过单行注释（注释在语句前的情况）
        IF INSTR(src.TEXT, '--') = 0 OR INSTR(src.TEXT, '--') > INSTR(src.TEXT, 'INSERT INTO ') THEN
          -- 跳过未闭合的多行注释（简单判断逻辑）
          IF NOT (INSTR(src.TEXT, '/*') > 0 AND INSTR(src.TEXT, '*/') = 0) THEN
            v_found := TRUE;
            EXIT;
          END IF;
        END IF;
      END IF;
    END LOOP;

    IF v_found THEN
      DBMS_OUTPUT.PUT_LINE('对象: ' || dep.NAME || ', 类型: ' || dep.TYPE || ', 操作: INSERT/MERGE');
    END IF;
  END LOOP;
END;
/

关键说明 #

1. 源代码扫描：通过USER_SOURCE获取对象的实际代码，逐行检查是否包含目标操作语句
2. 大小写兼容：统一转为大写，避免因代码或表名大小写不一致导致漏判
3. 注释过滤：简单处理单行和多行注释，减少注释中提及表名导致的误报
4. 对象类型限制：只关注包、包体、函数、存储过程、触发器这些可能包含DML操作的对象

额外优化：触发器的快速定位 #

对于触发器，无需扫描代码，直接通过USER_TRIGGERS就能获取触发事件：

SELECT TRIGGER_NAME, TRIGGER_TYPE
FROM USER_TRIGGERS
WHERE TABLE_NAME = 'DATREDPUB'
  AND TRIGGER_TYPE LIKE '%INSERT%';

这个查询可以直接返回所有针对该表的INSERT触发器，效率更高。

注意事项 #

• 如果代码中使用了表别名（比如INSERT INTO d ...，其中d是DATREDPUB的别名），当前脚本会漏判，需要额外处理别名的匹配逻辑
• 多行注释的处理是简化版，如果存在嵌套注释或跨多行的注释，可能需要更复杂的字符串解析逻辑，但大部分场景下足够使用

内容的提问来源于stack exchange，提问作者Jan Michálek