ORACLE中如何批量重置序列

背景：

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

BEGIN
  ENHANCED_SYNC_SEQUENCES('WJ_CPP'); -- 替换为你的模式名
END;
/
CREATE OR REPLACE PROCEDURE ENHANCED_SYNC_SEQUENCES(p_schema_name IN VARCHAR2) IS
  v_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 IS
    SELECT sequence_name
    FROM all_sequences
    WHERE sequence_owner = UPPER(p_schema_name)
      AND sequence_name LIKE 'SEQ\_%' ESCAPE '\';
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== 开始处理模式: ' || UPPER(p_schema_name) || ' ===');
  
  FOR seq_rec IN c_sequences LOOP
    v_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_exists
    FROM all_tables
    WHERE owner = UPPER(p_schema_name)
      AND table_name = UPPER(v_table_name);
      
    IF v_table_exists = 0 THEN
      DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 警告 - 表不存在，跳过');
      CONTINUE;
    END IF;
    DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 表存在');
    
    -- 步骤3: 检查ID列是否存在
    v_step := '检查ID列是否存在';
    DECLARE
      v_id_exists NUMBER;
    BEGIN
      SELECT COUNT(*) INTO v_id_exists
      FROM all_tab_columns
      WHERE owner = UPPER(p_schema_name)
        AND table_name = UPPER(v_table_name)
        AND column_name = 'ID';
        
      IF v_id_exists = 0 THEN
        DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 警告 - 无ID列，跳过');
        CONTINUE;
      END IF;
    END;
    DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: ID列存在');
    
    -- 步骤4: 查询表最大ID
    v_step := '查询表最大ID';
    BEGIN
      EXECUTE IMMEDIATE 'SELECT NVL(MAX(ID), 0) FROM ' || p_schema_name || '.' || v_table_name
        INTO 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);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 错误 - ' || SQLERRM);
        CONTINUE;
    END;
    
    -- 步骤5: 获取序列当前值
    v_step := '获取序列当前值';
    BEGIN
      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);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 错误 - ' || SQLERRM);
        CONTINUE;
    END;
    
    -- 步骤6: 判断是否需要调整
    v_step := '判断是否需要调整';
    IF v_current_seq_value >= v_new_seq_value THEN
      DBMS_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，处理成功');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 错误 - ' || SQLERRM);
        -- 尝试恢复增量
        BEGIN
          EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_schema_name || '.' || v_seq_name || ' INCREMENT BY 1';
        EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('步骤[' || v_step || ']: 恢复增量失败 - ' || SQLERRM);
        END;
        CONTINUE;
    END;
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE(CHR(10) || '=== 所有序列处理完毕 ===');
END ENHANCED_SYNC_SEQUENCES;
/