Oracle如何动态分析指定schema与表的元数据并修复PLSQL报错

错误原因说明 #

你遇到的ORA-00933错误是动态SQL拼接不规范导致的，核心问题包括以下几点：

• 字符串类型的入参拼接时没有加单引号转义，且WHERE条件拼接前后缺少空格，导致SQL语句被拼接成非法格式，比如owner = 0DS03AND segment_name = ODS_SALES属于完全非法的SQL语法
• EXECUTE IMMEDIATE的INTO关键字错误写在了动态SQL内部，正确语法应该是EXECUTE IMMEDIATE 动态SQL字符串 INTO 接收变量 USING 绑定变量
• NULL值判断逻辑错误，SQL中不能使用<> null判断非空，必须使用IS NOT NULL，否则判断永远不成立
• 行数统计的SQL缺少表名，只拼接了schema名称，执行时会直接报错
• 索引、主键查询没有做单返回值处理，如果表存在多个索引、联合主键时会触发返回多行的运行时错误

修复后的完整代码 #

这里直接优化为支持入参的存储过程，同时使用绑定变量避免SQL注入和单引号转义问题：

CREATE OR REPLACE PROCEDURE get_table_info(
    p_schema_name IN VARCHAR2,
    p_table_name IN VARCHAR2,
    v_pk_columns OUT VARCHAR2,
    v_ind_exists_flg OUT NUMBER,
    v_data_volume_mb OUT NUMBER,
    v_row_cnt OUT NUMBER,
    v_column_cnt OUT NUMBER
) IS
BEGIN
    -- 1.查询表数据存储容量（单位MB）
    EXECUTE IMMEDIATE 'SELECT NVL(SUM(bytes)/1024/1024,0) FROM dba_segments WHERE owner = :1 AND segment_name = :2'
        INTO v_data_volume_mb USING UPPER(p_schema_name), UPPER(p_table_name);
    
    -- 2.查询表行数（如果需要实时精准值可替换为动态拼接COUNT(*)查询，大表性能较差）
    EXECUTE IMMEDIATE 'SELECT NVL(NUM_ROWS,0) FROM all_tables WHERE owner = :1 AND table_name = :2'
        INTO v_row_cnt USING UPPER(p_schema_name), UPPER(p_table_name);
    
    -- 3.查询索引存在标识
    EXECUTE IMMEDIATE 'SELECT CASE WHEN EXISTS(SELECT 1 FROM dba_ind_columns WHERE table_owner = :1 AND table_name = :2) THEN 1 ELSE 0 END FROM DUAL'
        INTO v_ind_exists_flg USING UPPER(p_schema_name), UPPER(p_table_name);
    
    -- 4.查询表列数
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM all_tab_columns WHERE owner = :1 AND table_name = :2'
        INTO v_column_cnt USING UPPER(p_schema_name), UPPER(p_table_name);
    
    -- 5.查询主键列，联合主键自动用逗号拼接
    EXECUTE IMMEDIATE 'SELECT NVL(LISTAGG(cols.column_name,'','') WITHIN GROUP(ORDER BY cols.position),''无主键'') 
                        FROM all_constraints cons 
                        JOIN all_cons_columns cols ON cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner
                        WHERE cons.owner = :1 AND cons.table_name = :2 AND cons.constraint_type = ''P'''
        INTO v_pk_columns USING UPPER(p_schema_name), UPPER(p_table_name);
END;
/

测试调用示例 #

DECLARE
    v_pk VARCHAR2(1000);
    v_ind_flg NUMBER;
    v_size NUMBER;
    v_rows NUMBER;
    v_cols NUMBER;
BEGIN
    get_table_info('0DS03','ODS_SALES',v_pk,v_ind_flg,v_size,v_rows,v_cols);
    DBMS_OUTPUT.PUT_LINE('主键列：'||v_pk);
    DBMS_OUTPUT.PUT_LINE('是否存在索引：'||v_ind_flg);
    DBMS_OUTPUT.PUT_LINE('存储容量(MB)：'||v_size);
    DBMS_OUTPUT.PUT_LINE('行数：'||v_rows);
    DBMS_OUTPUT.PUT_LINE('列数：'||v_cols);
END;
/

注意事项 #

• 执行该存储过程的账号需要有dba_segments、dba_ind_columns的查询权限，如果没有权限可以将dba_前缀替换为user_，仅查询当前账号下的表信息
• 如果需要精准的实时行数，可以将第二步的SQL替换为'SELECT COUNT(*) FROM '||p_schema_name||'.'||p_table_name，大表查询会比较慢

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