Oracle PL/SQL统计指定表中各字段的空值、空字符串或零值比例

-- 创建结果存储表
CREATE TABLE table_analysis_result (
    database_name    VARCHAR2(100),
    table_name       VARCHAR2(100),
    column_name      VARCHAR2(100),
    data_type        VARCHAR2(100),
    empty_count      NUMBER,
    total_rows       NUMBER,
    empty_percent    NUMBER
);

DECLARE
    v_dbname        VARCHAR2(100);
    v_table_name    VARCHAR2(100);
    v_column_name   VARCHAR2(100);
    v_data_type     VARCHAR2(100);
    v_total_rows    NUMBER;
    v_empty_count   NUMBER;
    v_sql           VARCHAR2(4000);
    
    -- 定义需要分析的表（修改这里添加需要分析的表）
    TYPE table_list IS TABLE OF VARCHAR2(100);
    v_tables table_list := table_list('EMPLOYEES', 'DEPARTMENTS'); -- 示例表名
    
BEGIN
    -- 获取数据库名称
    SELECT name INTO v_dbname FROM v$database;
    
    -- 遍历所有需要分析的表
    FOR i IN 1..v_tables.COUNT LOOP
        BEGIN
            -- 获取表的总行数
            EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_tables(i) INTO v_total_rows;
            
            -- 跳过空表
            CONTINUE WHEN v_total_rows = 0;
            
            -- 遍历表的所有列
            FOR col IN (SELECT column_name, data_type
                        FROM user_tab_columns
                        WHERE table_name = v_tables(i)) 
            LOOP
                -- 根据数据类型构建不同查询
                IF col.data_type IN ('VARCHAR2','CHAR','NVARCHAR2','NCHAR') THEN
                    v_sql := 'SELECT COUNT(*) FROM ' || v_tables(i) ||
                             ' WHERE NVL(TRIM(' || col.column_name || '), '' '') = '' ''';
                ELSIF col.data_type IN ('NUMBER','INTEGER','FLOAT') THEN
                    v_sql := 'SELECT COUNT(*) FROM ' || v_tables(i) ||
                             ' WHERE NVL(' || col.column_name || ', 0) = 0';
                ELSE
                    v_sql := 'SELECT COUNT(*) FROM ' || v_tables(i) ||
                             ' WHERE ' || col.column_name || ' IS NULL';
                END IF;
                
                -- 执行动态SQL获取统计结果
                BEGIN
                    EXECUTE IMMEDIATE v_sql INTO v_empty_count;
                EXCEPTION
                    WHEN OTHERS THEN
                        v_empty_count := NULL; -- 处理不支持的操作（如LOB类型）
                END;
                
                -- 插入分析结果
                INSERT INTO table_analysis_result VALUES (
                    v_dbname,
                    v_tables(i),
                    col.column_name,
                    col.data_type,
                    NVL(v_empty_count, 0),
                    v_total_rows,
                    ROUND(NVL(v_empty_count, 0) / v_total_rows * 100, 2)
                );
            END LOOP;
        EXCEPTION
            WHEN OTHERS THEN
                NULL; -- 跳过不存在或无法访问的表
        END;
    END LOOP;
    COMMIT;
END;
/

-- 查询最终结果
SELECT 
    database_name AS "数据库",
    table_name AS "表名",
    column_name AS "字段名",
    data_type AS "字段类型",
    empty_count AS "空值数",
    total_rows AS "总行数",
    empty_percent || '%' AS "空值比例"
FROM table_analysis_result
ORDER BY table_name, column_name;

代码说明：

1. 结果表创建：先创建table_analysis_result表用于存储分析结果
2. 数据库信息获取：通过v$database视图获取数据库名称
3. 表遍历逻辑：
   • 在v_tables数组中定义需要分析的表名
   • 通过动态SQL获取每个表的总行数
   • 跳过没有数据的空表
4. 列遍历逻辑：
   • 通过user_tab_columns视图获取表的所有列信息
   • 根据数据类型生成不同的统计查询：
     • 字符串类型：统计TRIM后为空字符串的记录
     • 数字类型：统计值为0的记录（包含NULL转换后的0）
     • 其他类型：直接统计NULL值
5. 异常处理：
   • 处理不存在的表
   • 处理不支持的操作（如对LOB类型字段进行TRIM操作）
6. 结果展示：
   • 最终查询结果包含数据库名、表名、字段名、字段类型、空值数、总行数和空值比例
   • 百分比显示保留两位小数

使用注意事项：

1. 修改v_tables数组的值来指定需要分析的表
2. 需要具有访问v$database视图的权限
3. 需要具有访问分析表的SELECT权限
4. 对大表执行时可能需要较长时间，建议在业务低峰期运行
5. 结果精度取决于统计时的数据状态，建议在静态数据环境下执行

执行结果示例：

数据库       | 表名      | 字段名   | 字段类型 | 空值数 | 总行数 | 空值比例
-----------|----------|---------|---------|-------|-------|--------
HRDB       | EMPLOYEES| EMAIL   | VARCHAR2| 5     | 1000  | 0.50%
HRDB       | EMPLOYEES| SALARY  | NUMBER  | 12    | 1000  | 1.20%
HRDB       | DEPARTMENTS| MANAGER_ID| NUMBER| 3     | 100   | 3.00%