创建/截断表并填充数据的Oracle存储过程优化问询

需求实现目标：

• 若目标表不存在则创建
• 若目标表已存在则截断表
• 向表中填充初始数据

当前问题：

• 存储过程执行耗时过长
• 尝试操作表时触发ORA-00054资源忙错误

原简化代码：

CREATE OR REPLACE PROCEDURE report_init_sp AS
BEGIN

    -- Create table, if not exists
    DECLARE
        err EXCEPTION;
        PRAGMA EXCEPTION_INIT (err, -20001);
    BEGIN
        EXECUTE IMMEDIATE q'[
        CREATE TABLE sao_report_tbl
            (id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            proc_name VARCHAR(100) NOT NULL,
            proc_start TIMESTAMP NULL,
            proc_end TIMESTAMP NULL,
            proc_status VARCHAR(100) NULL,
            proc_runtime NUMBER NULL,
            row_count NUMBER NULL,
            PRIMARY KEY (id))
        ]';
    EXCEPTION
        WHEN OTHERS
            THEN RAISE_APPLICATION_ERROR( -20001, q'[Create table failed.]' );
    END;

    -- Truncate table
    DECLARE
        err EXCEPTION;
        PRAGMA EXCEPTION_INIT (err, -20001);
    BEGIN
        EXECUTE IMMEDIATE 'TRUNCATE TABLE sao_report_tbl';
        EXCEPTION
    WHEN OTHERS
        THEN RAISE_APPLICATION_ERROR( -20001, q'[Truncate table failed.]' );
    END;

    -- Populate table with procedure names (proc_name)
    DECLARE
        err EXCEPTION;
        PRAGMA EXCEPTION_INIT (err, -20001);
    BEGIN
    EXECUTE IMMEDIATE q'[
    INSERT
    INTO
        sao_report_tbl(proc_name)
    VALUES ('sao_eligible_members')
    ]';
    EXECUTE IMMEDIATE q'[
    INSERT
    INTO
        sao_report_tbl(proc_name)
    VALUES ('sao_ffs_claim')
    ]';
    EXECUTE IMMEDIATE q'[
    INSERT
    INTO
        sao_report_tbl(proc_name)
    VALUES ('sao_ffs_claim_notes')
    ]';
    EXCEPTION
        WHEN OTHERS
            THEN RAISE_APPLICATION_ERROR( -20001, q'[INSERT failed.]' );
    END;
END report_init_sp;

核心优化点 #

1. 预检查表存在性，避免无效异常抛出 #

原代码直接执行创建语句，无论表是否存在都会进入异常分支，额外消耗资源。通过查询数据字典USER_TABLES判断表是否存在，仅在不存在时执行创建逻辑。

2. 合并多次INSERT为批量操作 #

原代码多次调用EXECUTE IMMEDIATE执行单条INSERT，增加了上下文切换开销。改用多值INSERT一次性插入所有数据，减少执行次数。

3. 精准捕获异常，针对性处理 #

原代码统一捕获所有异常并抛出模糊错误，不利于排查。针对不同操作捕获特定错误（如创建表时的ORA-00955、截断时的ORA-00054），并保留原始错误信息辅助定位。

4. 简化代码结构，合并冗余DECLARE块 #

原代码每个操作单独声明异常块，可合并到主过程的异常处理逻辑，简化代码层级。

优化后的存储过程代码 #

CREATE OR REPLACE PROCEDURE report_init_sp AS
    v_table_exists NUMBER;
BEGIN
    -- 检查目标表是否存在
    SELECT COUNT(1)
    INTO v_table_exists
    FROM USER_TABLES
    WHERE TABLE_NAME = 'SAO_REPORT_TBL';

    -- 表不存在则执行创建
    IF v_table_exists = 0 THEN
        EXECUTE IMMEDIATE q'[
            CREATE TABLE sao_report_tbl
            (id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
             proc_name VARCHAR(100) NOT NULL,
             proc_start TIMESTAMP NULL,
             proc_end TIMESTAMP NULL,
             proc_status VARCHAR(100) NULL,
             proc_runtime NUMBER NULL,
             row_count NUMBER NULL,
             PRIMARY KEY (id))
        ]';
    END IF;

    -- 尝试截断表，资源忙时尝试获取排他锁后再操作
    BEGIN
        EXECUTE IMMEDIATE 'TRUNCATE TABLE sao_report_tbl';
    EXCEPTION
        WHEN ORA-00054 THEN
            -- 尝试加排他锁（NOWAIT表示不等待，若需等待可移除该关键字）
            EXECUTE IMMEDIATE 'LOCK TABLE sao_report_tbl IN EXCLUSIVE MODE NOWAIT';
            EXECUTE IMMEDIATE 'TRUNCATE TABLE sao_report_tbl';
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001, '截断表失败: ' || SQLERRM);
    END;

    -- 批量插入初始数据
    EXECUTE IMMEDIATE q'[
        INSERT INTO sao_report_tbl(proc_name)
        VALUES ('sao_eligible_members'),
               ('sao_ffs_claim'),
               ('sao_ffs_claim_notes')
    ]';
    COMMIT; -- 根据业务需求决定是否立即提交

EXCEPTION
    WHEN ORA-00955 THEN
        RAISE_APPLICATION_ERROR(-20002, '表已存在，无需重复创建');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20000, '存储过程执行失败: ' || SQLERRM);
END report_init_sp;

额外说明 #

• 若TRUNCATE因资源忙无法执行，改用LOCK TABLE尝试获取排他锁；若仍失败，可根据业务场景调整为等待锁（移除NOWAIT）或改用DELETE FROM sao_report_tbl; COMMIT;（大表场景下TRUNCATE性能更优）。
• 多值INSERT语法支持Oracle 11g及以上版本，低版本可改用INSERT ALL实现批量插入。
• 异常处理中加入SQLERRM保留原始错误信息，便于快速定位问题。

内容的提问来源于stack exchange，提问作者J. Sizzler