oracle数据迁移

最近老大要我做几张流水表的数据迁移，要求每天定时清理A表180天前数据，把它先导入历史表B表中（B表结构和A表一致），然后删除a表180天前数据。

思路：先写一个迁移的存储过程，在建立一个oracle的job（定时任务）来定时执行 ：

---

create or replace procedure PB_CLEAR_TEST_1(
  in_keepday      number, --清理多少天前数据
  in_commit       NUMBER --每次COMMIT提交数据
)
as

  n_records       number:=0;--插入+清理计数器
  n_totcommit     number:=0;--事务提交次数
  n_totrecords    number:=0;--清理总记录数
  n_opencur       number:=0;--游标计数器
  c_trandate      varchar(8);
  n_insertrecords    number:=0;--插入总记录数

  paylog_type   tbl_test_log%rowtype;
  cursor cur_trans_log(trandate char) is
    select * from tbl_test_log
    where EXT_TXN_DT <= trandate;

begin

  DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy-mm-dd hh:mi:ss')||' begin deal records of tbl_test_log!');

  c_trandate := to_char(sysdate-in_keepday, 'yyyymmdd');

  loop

    n_opencur := n_opencur + 1;
    open cur_trans_log(c_trandate);
    loop
      fetch cur_trans_log into paylog_type;
      exit when cur_trans_log%NOTFOUND;
            
            --逐条插入历史表
          insert into tbl_test_his_log select * from tbl_test_log
          where INST_CODE= paylog_type.INST_CODE
      and   EXT_TXN_DT = paylog_type.EXT_TXN_DT
      and   MER_ID = paylog_type.MER_ID
      and   SERIALNO= paylog_type.SERIALNO;
      n_insertrecords := n_insertrecords + 1;
      n_totcommit := n_totcommit + 1;

      --逐条删除
      delete from tbl_test_log
      where INST_CODE= paylog_type.INST_CODE
      and   EXT_TXN_DT = paylog_type.EXT_TXN_DT
      and   MER_ID = paylog_type.MER_ID
      and   SERIALNO= paylog_type.SERIALNO;

      n_records := n_records + 2;
      n_totrecords := n_totrecords + 1;

      --超过指定条数提交
      if n_records >= in_commit then
        commit;
        n_records := 0;
        n_totcommit := n_totcommit + 2;
        
      end if;
    end loop;
    exit when cur_trans_log%NOTFOUND;
    close cur_trans_log;
  end loop;

  close cur_trans_log;

  commit;
  n_totcommit := n_totcommit + 1;

  DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy-mm-dd hh:mi:ss')||' Finished!');
  DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_insertrecords)||' records moved!'||' committed '||to_char(n_totcommit)||'. Open cursor '||to_char(n_opencur)||' times.');
  DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_totrecords)||' records moved!'||' committed '||to_char(n_totcommit)||'. Open cursor '||to_char(n_opencur)||' times.');

exception
  when others then
    rollback;
    DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'yyyy-mm-dd hh:mi:ss')||' Get exception! '||sqlerrm);
    DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_totrecords)||' records moved!'||' committed '||to_char(n_totcommit)||'. Open cursor '||to_char(n_opencur)||' times.');
    return;

end;
/

---

这个存储过程是前人留下的，我勉强看懂，这里行尾的/是我加的，因为我发现不加这个/，不会执行完，不然你得再在命令行里加个/让这个sql执行，还有这个存储过程之前是半年执行一次的，所以要循环移动和删除每一条数据，并要加上commit的条数来提高效率，不然一次性移动和删除大量数据会很慢的，不过我没搞懂拿到整个循环体的select 语句不会执行很慢吗？不知道还有大神指导下。这里我也偷懒了吧，每天执行的话，数据量不大，不用写这么复杂的，不用循环的。

        测试存储过程中发现，测试环境没有历史表的，然后get到一个新的知识点，创建一个和A表结果结果一样的b表语句：

create table_b as select * from table_a  where 1<>1;--只建立表结构，不把a表数据塞入新表中

create table_b as select * from table_a  ；建立和table_a表结构和数据一样的table_b表

当然你也可以查到a表的建立语句，然后改下表名，再去执行一下。不过这也显然方便多了，这里是oracle的建立一张表结构一样的表的方法，其他比如mysql也有类似语句，可以自行百度；

上述建立存储过程的sql执行发到sql执行窗口执行即可，如果是命令行也可以执行这样执行，最好放到一个文件里，然后@这个文件执行。

如果你想看这个存储过程有没有建立成功，可以执行：

select text from user_source where name ='存储过程名称' order by line;

如果你想执行这个存储过程，要看你在哪执行：

如果在sql窗口中执行，要按如下方式执行：

declare
begin
   test_pro_001;--存储过程名称，如果带参数，就加括号并带上参数；
end;

或者

call test_pro_001();

如果是在命令行窗口执行：

exec test_pro_001();

可以参考: https://blog.csdn.net/chxkyy/article/details/83562456 

下面写下用定时任务调起上面的存储过程：

declare   job number; BEGIN   DBMS_JOB.SUBMIT(           JOB => job,           WHAT => 'PB_CLEAR_EPAY_1(180,300);',            INTERVAL => 'trunc(sysdate + 1) + 65/(24*60)'        );   end; /

写成sql文件，在命令行里执行即可，sql窗口也可以执行；

然后执行select * from user_jobs;查看你刚生成的定时任务，next_date加next_sec就是下次执行时间，job是定时任务的id，

手动执行一次定时任务sql如下：

begin
   DBMS_JOB.RUN(28);--28是id
end;

删除定时任务是：

begin
  DBMS_JOB.REMOVE(28);
  DBMS_JOB.REMOVE(29);--可以删除多个
  DBMS_JOB.REMOVE(30);
  commit;
end;

停止一个定时任务：

begin   
  dbms_job.broken(31,true,sysdate);        /*停止一个job,jobId, job的ID,里面参数true也可是false，next_date（某一时刻停止）也可是sysdate（立刻停止）。   */
  dbms_job.broken(32,true,sysdate);
  dbms_job.broken(33,true,sysdate);
commit;
end;

停止后nest_date是4000/1/1；

这样就是一个完整的定时执行存储过程的方法了，存储过程和定时任务怎么写可以百度了。都是百度出来的