Oracle PL/SQL存储过程无报错但未执行预期操作求助

熟悉SQL但刚接触Oracle，编写了PRC_UNIQUE_COUNTS存储过程，用于统计指定表各列的唯一值数量并写入myschema.TBL_UNIQUE_COUNTS表。执行EXEC PRC_UNIQUE_COUNTS('mychema.mytable');时提示“PL/SQL procedure successfully completed”，但未截断目标表也未填充数据，无报错日志，请求排查问题原因。

存储过程原代码：

CREATE OR REPLACE PROCEDURE PRC_UNIQUE_COUNTS (
    p_table_name IN VARCHAR2
) IS
    v_column_name  VARCHAR2(128);
    v_unique_count NUMBER;
    v_sql          VARCHAR2(4000);
BEGIN
   -- Truncate the output table
    EXECUTE IMMEDIATE 'TRUNCATE TABLE myschema.TBL_UNIQUE_COUNTS';
    FOR t IN (
        SELECT
            column_name
        FROM
            all_tab_columns
        WHERE
            table_name = upper(p_table_name)
        ORDER BY
            column_id
    ) LOOP
        v_column_name := t.column_name;
      
      -- Dynamic SQL to get the count of unique values in the column
        v_sql := 'SELECT COUNT(DISTINCT '
                 || v_column_name
                 || ') FROM '
                 || p_table_name;
        EXECUTE IMMEDIATE v_sql
        INTO v_unique_count;
      
      -- Insert the result into the output table
        INSERT INTO myschema.tbl_unique_counts (
            col_name,
            unique_count
        ) VALUES (
            v_column_name,
            v_unique_count
        );

    END LOOP;

    COMMIT;  -- Commit the transaction
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('An error occurred: ' || sqlerrm);
END;

核心问题点 #

• Truncate执行失败但未感知：如果myschema.TBL_UNIQUE_COUNTS不存在、当前用户无TRUNCATE权限，或者传入的表名拼写错误（比如mychema应为myschema），会导致Truncate语句执行失败触发异常。但默认dbms_output是关闭的，异常信息无法输出，过程静默结束，看起来像执行成功但无实际操作。
• 表列查询逻辑错误：传入的参数是带Schema的表名（如mychema.mytable），但all_tab_columns的table_name字段仅存储表名（不含Schema），owner字段才对应Schema。原代码用table_name = upper(p_table_name)匹配，会把带Schema的全名当成表名查询，找不到任何列，循环体完全不执行，自然不会插入数据。

修正步骤 #

1. 修复表名解析逻辑：拆分传入的带Schema表名，分别匹配all_tab_columns的owner和table_name字段。
2. 开启服务器输出：执行存储过程前先执行SET SERVEROUTPUT ON;，以便查看异常信息。
3. 增强异常处理：异常块中回滚事务并重新抛出异常，确保调用者能感知错误；也可考虑将错误信息写入日志表。
4. 处理大小写敏感列名：如果列名包含特殊字符或大小写敏感，用双引号包裹列名避免解析错误。

修正后的存储过程 #

CREATE OR REPLACE PROCEDURE PRC_UNIQUE_COUNTS (
    p_table_name IN VARCHAR2
) IS
    v_owner        VARCHAR2(128);
    v_table        VARCHAR2(128);
    v_column_name  VARCHAR2(128);
    v_unique_count NUMBER;
    v_sql          VARCHAR2(4000);
BEGIN
    -- 拆分传入的表名，分离Schema和表名
    IF INSTR(p_table_name, '.') > 0 THEN
        v_owner := UPPER(SUBSTR(p_table_name, 1, INSTR(p_table_name, '.') - 1));
        v_table := UPPER(SUBSTR(p_table_name, INSTR(p_table_name, '.') + 1));
    ELSE
        -- 未指定Schema时使用当前用户
        v_owner := USER;
        v_table := UPPER(p_table_name);
    END IF;

    -- 截断目标表
    EXECUTE IMMEDIATE 'TRUNCATE TABLE myschema.TBL_UNIQUE_COUNTS';

    -- 遍历目标表的所有列
    FOR t IN (
        SELECT column_name
        FROM all_tab_columns
        WHERE owner = v_owner
          AND table_name = v_table
        ORDER BY column_id
    ) LOOP
        v_column_name := t.column_name;

        -- 构造动态SQL统计唯一值，处理大小写敏感列名
        v_sql := 'SELECT COUNT(DISTINCT "' || v_column_name || '") FROM ' || p_table_name;
        EXECUTE IMMEDIATE v_sql INTO v_unique_count;

        -- 插入统计结果
        INSERT INTO myschema.tbl_unique_counts (
            col_name,
            unique_count
        ) VALUES (
            v_column_name,
            v_unique_count
        );

    END LOOP;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('错误信息: ' || sqlerrm || '，错误代码: ' || sqlcode);
        ROLLBACK;
        RAISE; -- 重新抛出异常，让调用者明确感知错误
END;

执行注意事项 #

• 执行前先开启服务器输出：SET SERVEROUTPUT ON;
• 确保当前用户对myschema.TBL_UNIQUE_COUNTS有TRUNCATE、INSERT权限，对目标表有SELECT权限
• 检查传入的表名拼写是否正确（比如mychema是否应为myschema）

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