如何编写PL/SQL过程判断SQL语句的操作类型（DML/DDL/其他）

直接通过字符串匹配判断SQL类型完全不可靠（比如带注释、换行、大小写混合的复杂语句），依赖Oracle内置解析引擎是唯一靠谱的路径。以下是我在项目中用过的两种实践方案：

方案一：利用DBMS_UTILITY.PARSE_SQL获取语法树 #

这个过程直接调用Oracle的解析器，返回SQL的语法树结构，其中包含明确的操作类型字段，是最稳定的方式。

完整PL/SQL过程 #

CREATE OR REPLACE PROCEDURE classify_sql(
  p_sql IN VARCHAR2,
  p_sql_type OUT VARCHAR2
) IS
  l_sql_tree DBMS_UTILITY.SQL_TREE;
BEGIN
  -- 解析SQL获取语法树，自动忽略注释、换行等干扰
  DBMS_UTILITY.PARSE_SQL(p_sql, l_sql_tree);
  
  -- 按操作类型分类
  CASE l_sql_tree.operation
    WHEN 'SELECT' THEN p_sql_type := 'SELECT';
    WHEN 'INSERT' THEN p_sql_type := 'INSERT';
    WHEN 'UPDATE' THEN p_sql_type := 'UPDATE';
    WHEN 'DELETE' THEN p_sql_type := 'DELETE';
    -- 覆盖常见DDL类型
    WHEN 'CREATE' THEN p_sql_type := 'DDL';
    WHEN 'ALTER' THEN p_sql_type := 'DDL';
    WHEN 'DROP' THEN p_sql_type := 'DDL';
    WHEN 'TRUNCATE' THEN p_sql_type := 'DDL';
    WHEN 'RENAME' THEN p_sql_type := 'DDL';
    ELSE p_sql_type := 'OTHER';
  END CASE;
EXCEPTION
  WHEN OTHERS THEN
    -- 解析失败（无效SQL、权限不足等）默认返回OTHER
    p_sql_type := 'OTHER';
END classify_sql;
/

测试示例 #

-- 测试带注释的SELECT
DECLARE
  v_type VARCHAR2(20);
BEGIN
  classify_sql('/* 部门查询 */ SELECT deptno, dname FROM dept WHERE loc = ''NY''', v_type);
  DBMS_OUTPUT.PUT_LINE(v_type); -- 输出 SELECT
END;
/

-- 测试DDL
DECLARE
  v_type VARCHAR2(20);
BEGIN
  classify_sql('ALTER TABLE emp ADD email VARCHAR2(100)', v_type);
  DBMS_OUTPUT.PUT_LINE(v_type); -- 输出 DDL
END;
/

-- 测试无效SQL
DECLARE
  v_type VARCHAR2(20);
BEGIN
  classify_sql('INVALID SQL SYNTAX', v_type);
  DBMS_OUTPUT.PUT_LINE(v_type); -- 输出 OTHER
END;
/

方案二：通过DBMS_SQL解析后查动态视图 #

如果需要更细粒度的命令类型（比如区分不同DDL的子类型），可以用DBMS_SQL解析后，查询V$SQLAREA的COMMAND_TYPE字段：

CREATE OR REPLACE PROCEDURE classify_sql(
  p_sql IN VARCHAR2,
  p_sql_type OUT VARCHAR2
) IS
  l_cursor_id INTEGER;
  l_sql_id VARCHAR2(13);
  l_command_type NUMBER;
BEGIN
  l_cursor_id := DBMS_SQL.OPEN_CURSOR;
  BEGIN
    DBMS_SQL.PARSE(l_cursor_id, p_sql, DBMS_SQL.NATIVE);
    l_sql_id := DBMS_SQL.LAST_SQL_ID;
    
    SELECT command_type INTO l_command_type
    FROM v$sqlarea
    WHERE sql_id = l_sql_id;
    
    CASE l_command_type
      WHEN 3 THEN p_sql_type := 'SELECT';
      WHEN 2 THEN p_sql_type := 'INSERT';
      WHEN 6 THEN p_sql_type := 'UPDATE';
      WHEN 7 THEN p_sql_type := 'DELETE';
      -- 常见DDL的命令类型码
      WHEN 1 THEN p_sql_type := 'DDL';
      WHEN 4 THEN p_sql_type := 'DDL';
      WHEN 8 THEN p_sql_type := 'DDL';
      ELSE p_sql_type := 'OTHER';
    END CASE;
  EXCEPTION
    WHEN OTHERS THEN
      p_sql_type := 'OTHER';
  END;
  DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
END classify_sql;
/

实践注意事项 #

• 权限要求：执行DBMS_UTILITY或DBMS_SQL需要用户被授予对应权限，普通用户可通过GRANT EXECUTE ON DBMS_UTILITY TO your_user;授权。
• 长SQL支持：如果需要处理超过VARCHAR2长度限制的SQL，可将参数改为CLOB类型，DBMS_UTILITY.PARSE_SQL支持CLOB输入。
• 共享池影响：解析SQL会占用少量共享池资源，单条解析几乎无影响，批量处理时可考虑清理临时SQL。

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