如何在Oracle中通过存储过程按映射表最大值重置所有序列

假设你的映射表结构如下（可根据实际场景调整字段）：

CREATE TABLE seq_table_mapping (
    seq_name VARCHAR2(128) NOT NULL, -- 序列名称
    table_name VARCHAR2(128) NOT NULL, -- 关联表名称
    id_column VARCHAR2(128) NOT NULL -- 表中自增主键列名
);

存储过程实现 #

CREATE OR REPLACE PROCEDURE reset_seqs_from_mapping
IS
    v_max_val NUMBER;
    v_seq_name VARCHAR2(128);
    v_table_name VARCHAR2(128);
    v_id_column VARCHAR2(128);
    v_sql VARCHAR2(1000);
    v_current_seq_val NUMBER;
BEGIN
    -- 遍历映射表中的所有关联记录
    FOR rec IN (SELECT seq_name, table_name, id_column FROM seq_table_mapping) LOOP
        v_seq_name := rec.seq_name;
        v_table_name := rec.table_name;
        v_id_column := rec.id_column;
        
        -- 查询目标表主键的最大值
        v_sql := 'SELECT NVL(MAX(' || v_id_column || '), 0) FROM ' || v_table_name;
        EXECUTE IMMEDIATE v_sql INTO v_max_val;
        
        -- 获取序列当前值，处理序列未初始化的情况
        v_sql := 'SELECT ' || v_seq_name || '.CURRVAL FROM DUAL';
        BEGIN
            EXECUTE IMMEDIATE v_sql INTO v_current_seq_val;
        EXCEPTION
            WHEN OTHERS THEN
                -- 序列未被调用过，先执行NEXTVAL初始化
                v_sql := 'SELECT ' || v_seq_name || '.NEXTVAL FROM DUAL';
                EXECUTE IMMEDIATE v_sql INTO v_current_seq_val;
        END;
        
        -- 调整序列至目标值
        IF v_max_val > v_current_seq_val THEN
            -- 修改序列增量，一次性跳到目标值
            v_sql := 'ALTER SEQUENCE ' || v_seq_name || ' INCREMENT BY ' || (v_max_val - v_current_seq_val);
            EXECUTE IMMEDIATE v_sql;
            -- 触发序列值更新
            EXECUTE IMMEDIATE 'SELECT ' || v_seq_name || '.NEXTVAL FROM DUAL' INTO v_current_seq_val;
            -- 恢复序列默认增量为1
            EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || v_seq_name || ' INCREMENT BY 1';
        ELSIF v_max_val = v_current_seq_val THEN
            DBMS_OUTPUT.PUT_LINE('序列 ' || v_seq_name || ' 已与表 ' || v_table_name || ' 的最大值同步');
        ELSE
            DBMS_OUTPUT.PUT_LINE('序列 ' || v_seq_name || ' 当前值大于表 ' || v_table_name || ' 的最大值，无需调整');
        END IF;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('所有映射关系中的序列重置完成');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('重置过程出错：' || SQLERRM);
        RAISE;
END;
/

使用说明 #

• 确保seq_table_mapping表已正确维护序列与表、主键列的对应关系
• 执行存储过程前，需拥有ALTER SEQUENCE权限以及对应表的查询权限
• 执行存储过程：

BEGIN
    reset_seqs_from_mapping;
END;
/

该场景需依赖序列与表的命名一致性（示例采用常见规则：前缀SEQ_或后缀_S），可根据实际命名规则调整匹配逻辑。

存储过程实现 #

CREATE OR REPLACE PROCEDURE reset_all_oracle_seqs
IS
    v_max_val NUMBER;
    v_seq_name VARCHAR2(128);
    v_table_name VARCHAR2(128);
    v_id_column VARCHAR2(128);
    v_sql VARCHAR2(1000);
    v_current_seq_val NUMBER;
BEGIN
    -- 遍历所有用户自定义序列
    FOR seq_rec IN (SELECT sequence_name FROM user_sequences) LOOP
        v_seq_name := seq_rec.sequence_name;
        
        -- 根据命名规则匹配关联表名
        IF v_seq_name LIKE 'SEQ_%' THEN
            v_table_name := SUBSTR(v_seq_name, 4);
        ELSIF v_seq_name LIKE '%_S' THEN
            v_table_name := SUBSTR(v_seq_name, 1, LENGTH(v_seq_name)-2);
        ELSE
            DBMS_OUTPUT.PUT_LINE('无法识别序列 ' || v_seq_name || ' 对应的表，跳过');
            CONTINUE;
        END IF;
        
        -- 获取目标表的主键列
        BEGIN
            SELECT column_name INTO v_id_column
            FROM user_constraints uc
            JOIN user_cons_columns ucc ON uc.constraint_name = ucc.constraint_name
            WHERE uc.table_name = UPPER(v_table_name)
              AND uc.constraint_type = 'P';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- 尝试匹配默认主键列名（ID/表名_ID）
                BEGIN
                    v_sql := 'SELECT column_name FROM user_tab_columns WHERE table_name = UPPER(''' || v_table_name || ''') AND column_name IN (''ID'', ''' || UPPER(v_table_name) || '_ID'')';
                    EXECUTE IMMEDIATE v_sql INTO v_id_column;
                EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                        DBMS_OUTPUT.PUT_LINE('无法找到表 ' || v_table_name || ' 的主键列，跳过序列 ' || v_seq_name);
                        CONTINUE;
                END;
        END;
        
        -- 查询表主键最大值
        BEGIN
            v_sql := 'SELECT NVL(MAX(' || v_id_column || '), 0) FROM ' || v_table_name;
            EXECUTE IMMEDIATE v_sql INTO v_max_val;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('查询表 ' || v_table_name || ' 最大值出错：' || SQLERRM || '，跳过序列 ' || v_seq_name);
                CONTINUE;
        END;
        
        -- 获取序列当前值，处理未初始化情况
        BEGIN
            v_sql := 'SELECT ' || v_seq_name || '.CURRVAL FROM DUAL';
            EXECUTE IMMEDIATE v_sql INTO v_current_seq_val;
        EXCEPTION
            WHEN OTHERS THEN
                EXECUTE IMMEDIATE 'SELECT ' || v_seq_name || '.NEXTVAL FROM DUAL' INTO v_current_seq_val;
        END;
        
        -- 调整序列至目标值
        IF v_max_val > v_current_seq_val THEN
            v_sql := 'ALTER SEQUENCE ' || v_seq_name || ' INCREMENT BY ' || (v_max_val - v_current_seq_val);
            EXECUTE IMMEDIATE v_sql;
            EXECUTE IMMEDIATE 'SELECT ' || v_seq_name || '.NEXTVAL FROM DUAL' INTO v_current_seq_val;
            EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || v_seq_name || ' INCREMENT BY 1';
            DBMS_OUTPUT.PUT_LINE('已重置序列 ' || v_seq_name || ' 到表 ' || v_table_name || ' 的最大值 ' || v_max_val);
        ELSIF v_max_val = v_current_seq_val THEN
            DBMS_OUTPUT.PUT_LINE('序列 ' || v_seq_name || ' 已与表 ' || v_table_name || ' 的最大值同步');
        ELSE
            DBMS_OUTPUT.PUT_LINE('序列 ' || v_seq_name || ' 当前值大于表 ' || v_table_name || ' 的最大值，无需调整');
        END IF;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('所有可识别的序列重置完成');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('全局重置过程出错：' || SQLERRM);
        RAISE;
END;
/

使用说明 #

• 若序列与表的命名规则不同，需修改v_table_name的匹配逻辑
• 需拥有SELECT权限访问user_sequences、user_constraints等系统视图，以及ALTER SEQUENCE权限和表的查询权限
• 执行存储过程：

BEGIN
    reset_all_oracle_seqs;
END;
/

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