更新表所有id的存储过程

最近公司交给我一个任务，他们不小心删除了一个表空间的数据，现在要做数据恢复，本来是资深DBA应该做的事情，现在压到我身上,我才来1个月。不说废话了。

任务1：把一个有9亿条数据的表的id都增加到1000000000000000以后，说简单一点就是更新这个表所有的id字段，

刚拿到这个任务觉得如此简单啊“不就是一个update”就能搞定的事情吗？，下来细想，如果用update，那么我可能明天就开始找新工作了。

因为Oracle数据库有undo段，专门用于存储要rollback的数据，而且空间有限，不能存储这么多的数据量.而且update9Y条数据，那么这个事务有多大呢？在中途断了之后数据就会处于一种未知的状态，简单说：数据库出问题了。

解决方案：最后写了一个存储过程来解决。找到最大的id，找到最小的id,用between来做，每10W条数据提交一次。

存储过程与相关内容如下：

1.首先需要在需要操作的数据库中建如下表：DDL如下： -- Create table

create table INSERT_LOG_EXPECTION
(
  start_id    NUMBER(19),
  end_id      NUMBER(19),
  error_code  VARCHAR2(4000),
  err_message VARCHAR2(4000),
  info        VARCHAR2(50)
)

2.创建相关procedure如下

   create or replace procedure add_id_histplateinfonew_pro(current_add_number number,number_each_submit number)
    as
        executeNumber number(20) :=0;--当前id
        dataCountMax number(20);
        v_current_count_start number(20);
        v_current_count_end number(20);
        err_code varchar(4000);
        err_message varchar(4000);
        info varchar(20);
    begin
      select max(id) into dataCountMax from hist_plateinfo_new;
      select min(id) into executeNumber from hist_plateinfo_new;
      while  executeNumber<= dataCountMax
        loop
          begin
            update hist_plateinfo_new set id = id+current_add_number where id between executeNumber and executeNumber+number_each_submit;
            commit;
            exit when executeNumber >= dataCountMax;
            executeNumber := executeNumber+number_each_submit;
            if executeNumber>dataCountMax
              then
                begin
                  executeNumber :=dataCountMax;
                  end;
                  end if;
            end;
         end loop;
         exception when others then
              rollback;
              v_current_count_start :=executeNumber;
              v_current_count_end :=executeNumber+number_each_submit;
              err_code :='更新'||v_current_count_start||'->'||v_current_count_end||'id失败';
              err_message :=sqlerrm;
              info :='已回滚';
              insert into insert_log_expection values(v_current_count_start,v_current_count_end,err_code,err_message,info);
              commit;
     end;

3.执行存储过程语句说明:在plsql中执行为begin add_id_histplateinfonew_pro(需要加的序列值如：（999999999999999999900000000000），每多次个值提交一次(建议10W提交一次));

说明：用到while,exception when,变量定义，参数传递，if,loop等知识。

在cmd 环境下执行存储过程:exec add_id_histplateinfonew_pro(需要加的序列值如：（999999999999999999900000000000），每多次个值提交一次(建议10W提交一次));

任务已经完成，9亿数据，没有出现任何问题。

后续还有很多导出导入工作，自己也偏数据库，这也是一次锻炼的机会，好好把握。

原文链接：https://blog.csdn.net/u014492098/article/details/48008605