ORACLE中如何批量重置序列

背景：

数据库所有序列都重置为1了，所以要将所有的序列都更新为对应的表主键（这里是id）的最大值+1。我这里序列的规则是SEQ_表名。

BEGINENHANCED_SYNC_SEQUENCES('WJ_CPP'); -- 替换为你的模式名
END;
/
CREATE OR REPLACE PROCEDURE ENHANCED_SYNC_SEQUENCES(p_schema_name IN VARCHAR2) ISv_table_name VARCHAR2(30);v_seq_name VARCHAR2(30);v_max_id NUMBER;v_new_seq_value NUMBER;v_current_seq_value NUMBER;v_increment_by NUMBER;v_table_exists NUMBER;v_step VARCHAR2(100); -- 记录当前执行步骤，便于排查CURSOR c_sequences ISSELECT sequence_nameFROM all_sequencesWHERE sequence_owner = UPPER(p_schema_name)AND sequence_name LIKE 'SEQ\_%' ESCAPE '\';
BEGINDBMS_OUTPUT.PUT_LINE('=== 开始处理模式: ' || UPPER(p_schema_name) || ' ===');FOR seq_rec IN c_sequences LOOPv_seq_name := seq_rec.sequence_name;DBMS_OUTPUT.PUT_LINE(CHR(10) || '--- 处理序列: ' || v_seq_name || ' ---');-- 步骤1: 提取表名v_step := '提取表名';v_table_name := SUBSTR(v_seq_name, 5);DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 对应表名 -> ' || v_table_name);-- 步骤2: 检查表是否存在v_step := '检查表是否存在';SELECT COUNT(*) INTO v_table_existsFROM all_tablesWHERE owner = UPPER(p_schema_name)AND table_name = UPPER(v_table_name);IF v_table_exists = 0 THENDBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 警告 - 表不存在，跳过');CONTINUE;END IF;DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 表存在');-- 步骤3: 检查ID列是否存在v_step := '检查ID列是否存在';DECLAREv_id_exists NUMBER;BEGINSELECT COUNT(*) INTO v_id_existsFROM all_tab_columnsWHERE owner = UPPER(p_schema_name)AND table_name = UPPER(v_table_name)AND column_name = 'ID';IF v_id_exists = 0 THENDBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 警告 - 无ID列，跳过');CONTINUE;END IF;END;DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: ID列存在');-- 步骤4: 查询表最大IDv_step := '查询表最大ID';BEGINEXECUTE IMMEDIATE 'SELECT NVL(MAX(ID), 0) FROM ' || p_schema_name || '.' || v_table_nameINTO v_max_id;v_new_seq_value := v_max_id + 1;DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 表最大ID=' || v_max_id || ', 目标序列值=' || v_new_seq_value);EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 错误 - ' || SQLERRM);CONTINUE;END;-- 步骤5: 获取序列当前值v_step := '获取序列当前值';BEGINEXECUTE IMMEDIATE 'SELECT ' || p_schema_name || '.' || v_seq_name || '.NEXTVAL FROM DUAL'INTO v_current_seq_value;DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 序列当前值=' || v_current_seq_value);EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 错误 - ' || SQLERRM);CONTINUE;END;-- 步骤6: 判断是否需要调整v_step := '判断是否需要调整';IF v_current_seq_value >= v_new_seq_value THENDBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 无需调整（当前值 >= 目标值）');CONTINUE;END IF;DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 需要调整（当前值 < 目标值）');-- 步骤7: 调整序列v_step := '调整序列';BEGIN-- 计算增量v_increment_by := v_new_seq_value - v_current_seq_value;DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 临时增量=' || v_increment_by);-- 修改增量EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_schema_name || '.' || v_seq_name || ' INCREMENT BY ' || v_increment_by;-- 触发增量EXECUTE IMMEDIATE 'SELECT ' || p_schema_name || '.' || v_seq_name || '.NEXTVAL FROM DUAL' INTO v_current_seq_value;DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 调整后的值=' || v_current_seq_value);-- 恢复增量EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_schema_name || '.' || v_seq_name || ' INCREMENT BY 1';DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 恢复增量为1，处理成功');EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 错误 - ' || SQLERRM);-- 尝试恢复增量BEGINEXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_schema_name || '.' || v_seq_name || ' INCREMENT BY 1';EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 恢复增量失败 - ' || SQLERRM);END;CONTINUE;END;END LOOP;DBMS_OUTPUT.PUT_LINE(CHR(10) || '=== 所有序列处理完毕 ===');
END ENHANCED_SYNC_SEQUENCES;
/