数据迁移案例
/* 创建Test_KIKI_01表循环插入100万数据（循环插入数据每1000条数据提交一次） 复制Test_KIKI_01表结构到Test_KIKI_02和Test_KIKI_03表 当Test_KIKI_02表发生增删改的时候造作的相应数据插入到Test_KIKI_03表中（创建触发器） 割接A表 id 能整除2的数据迁移到 Test_KIKI_02 表中。（创建存储过程） 在切割中： 当id 结尾为2 时，username=li; 当id 结尾为4 时，username=wang; 当id 结尾为6 时，username=zhao; 当id 结尾为8 时，username=liu; 当发生异常时，异常信息写入Log_KIKI_01表 */
--创建主表 create table Test_KIKI_01( id number(10) primary key , name varchar2(20), password varchar2(50), time date ); --创建序列 create sequence seq_Test_KIKI_01; --循环添加一百万数据 declare v_no_frist number:=1; v_no_last number:=1000000; v_count number:=0; begin for i in v_no_frist .. v_no_last loop --循环100万次 insert into Test_KIKI_01 values(seq_Test_KIKI_01.nextval, dbms_random.string('a',5),--随机获取26个字母的5个字母 dbms_random.string('a',10),--随机获取26个字母的10个字母 sysdate); v_count:=v_count+1; if v_count>=1000 then --每1000次提交一次 commit; v_count:=0; end if; end loop; commit; end; / --复制P_Test_KIKI_01表结构到P_Test_KIKI_02 create table Test_KIKI_02 as select * from Test_KIKI_01 where 1=2;--(1=1表示复制表结构和表数据，1=2表示只复制表结构) create table Test_KIKI_03 as select * from Test_KIKI_01 where 1=2; --创建错误记录表 create table Log_KIKI_01( id number(20), sqlcode varchar2(255), sqlerror varchar2(255) ); --创建触发器：当对表P_Test_KIKI_02进行操作的时候，把操作内容复制到表P_Test_KIKI_03 create or replace trigger tr_Test_KIKI_02 --创建tr_Test_KIKI_02触发器 after insert or update or delete --在添加、修改和删除之后 on Test_KIKI_02 --当操作Test_KIKI_02表时触发次触发器 for each row --行级触发器 begin if inserting then --添加时 insert into Test_KIKI_03 values(:new.id,:new.name,:new.password,:new.time);--插入新添加的值（:new表示新值） elsif updating then --修改时 insert into Test_KIKI_03 values(:old.id,:old.name,:old.password,:old.time);--插入被修改的老值（：old表示老值） elsif deleting then --删除时 insert into Test_KIKI_03 values(:old.id,:old.name,:old.password,:old.time);--插入被删除的老值（：old表示老值） end if; end; / --创建存储过程:Test_KIKI_01中的数据按照id为偶数的复制到表Test_KIKI_02中，并且把id末尾为2.4.6.8的姓名改为‘li’‘wang’‘zaho’‘liu’ create or replace procedure P_Test_KIKI_02 is type type_a is table of Test_KIKI_01%rowtype index by binary_integer; v_type_a type_a; type type_b_cur is ref cursor; v_type_b_cur type_b_cur; v_sql varchar2(255); v_count number:=1000; v_no number:=0; v_sqlerror varchar2(255); v_sqlcode varchar2(255); begin v_sql:='select * from Test_KIKI_01 where mod(id,2)=0 '; open v_type_b_cur for v_sql; loop fetch v_type_b_cur bulk collect into v_type_a limit v_count; if v_type_a.count=0 then exit; end if; for i in v_type_a.first .. v_type_a.last loop if substr(v_type_a(i).id,-1,1)=2 then v_type_a(i).name :='li'; elsif substr(v_type_a(i).id,-1,1)=4 then v_type_a(i).name :='wang'; elsif substr(v_type_a(i).id,-1,1)=6 then v_type_a(i).name :='zhao'; elsif substr(v_type_a(i).id,-1,1)=8 then v_type_a(i).name :='liu'; end if; begin insert into Test_KIKI_02 values(v_type_a(i).id,v_type_a(i).name,v_type_a(i).password,v_type_a(i).time); v_no:=v_no+1; exception when others then v_sqlerror :=substr(sqlerrm,1,200); insert into Log_KIKI_01 values(i,v_sqlcode,v_sqlerror); end; if v_no>=v_count then commit; v_no:=0; end if; end loop; commit; end loop; close v_type_b_cur; end P_Test_KIKI_02; / create or replace procedure P_Test_KIKI_03 is type type_a is table of Test_KIKI_01%rowtype index by binary_integer; --自定义类型，类型来自Test_KIKI_01 v_type_a type_a; --定义变量，类型是上面的自定义类型 type type_b_cur is ref cursor; --定义游标类型 v_type_b_cur type_b_cur; --定义游标变量，指定上面自定义类型 v_sql varchar2(255); --定义变量，用于接收Test_KIKI_01的所有数据 v_count number:=1000; --定义变量用于分批次提交和分组 v_no number:=0; --定义计数器 v_sqlerror varchar2(255); --定义异常错误名 v_sqlcode varchar2(255); --定义异常错误编号 begin v_sql:='select * from Test_KIKI_01 where mod(id,2)=0 ';--为v_sql赋值（查询出能被2整除的所有数据）； open v_type_b_cur for v_sql;--打开游标，数据来自v_sql loop fetch v_type_b_cur bulk collect into v_type_a limit v_count;--游标从集合里取数据，1000一组 if v_type_a.count=0 then --集合里没有数据时进入 exit; --退出 end if; for i in v_type_a.first .. v_type_a.last loop --for循环 从第一个到最后一个 begin --赋值 insert into Test_KIKI_02 values(v_type_a(i).id, --decode（substr(列，取最后1位，从1开始匹配）， --与2匹配，相同就赋值‘li’不同进行下一个匹配， --与4匹配，相同就赋值‘wang’不同进行下一个匹配， --与6匹配，相同就赋值‘zhao’不同进行下一个匹配， --与8匹配，相同就赋值‘liu’不同进行下一个匹配， --都不匹配赋值原有数据) decode(substr(v_type_a(i).id,-1,1),'2','li','4','wang','6','zhao','8','liu',v_type_a(i).name), v_type_a(i).password, v_type_a(i).time); v_no:=v_no+1; --计数器 exception when others then --自定义异常信息 v_sqlerror :=substr(sqlerrm,1,200);--截取异常信息（不截取会导致异常信息过长，从而插入失败） insert into Log_KIKI_01 values(i,v_sqlcode,v_sqlerror);--异常插入异常表中 end; if v_no>=v_count then --每1000条保存一次 commit; v_no:=0; --计数器归0 end if; --结束if end loop; commit;--插入数据 end loop; close v_type_b_cur;--关闭游标 end P_Test_KIKI_03; --关闭存储过程 / --调用（执行）存储过程 begin P_Test_KIKI_02; end; --调用（执行）存储过程 begin P_Test_KIKI_03; end; --查询结果 select * from Test_KIKI_01; select count(*) from Test_KIKI_01; select * from Test_KIKI_02; select count(*) from Test_KIKI_02; select * from Test_KIKI_03; select count(*) from Test_KIKI_03; select * from Log_KIKI_01; select count(*) from Log_KIKI_01;
创建Test_KIKI_01表循环插入100万数据（循环插入数据每1000条数据提交一次）复制Test_KIKI_01表结构到Test_KIKI_02和Test_KIKI_03表当Test_KIKI_02表发生增删改的时候造作的相应数据插入到Test_KIKI_03表中（创建触发器）割接A表 id 能整除2的数据迁移到 Test_KIKI_02 表中。（创建存储过程）在切割中： 当id 结尾为2 时，username=li; 当id 结尾为4 时，username=wang; 当id 结尾为6 时，username=zhao; 当id 结尾为8 时，username=liu;当发生异常时，异常信息写入Log_KIKI_01表
--创建主表create table Test_KIKI_01( id number(10) primary key , name varchar2(20), password varchar2(50), time date);--创建序列create sequence seq_Test_KIKI_01;
--循环添加一百万数据declare v_no_frist number:=1; v_no_last number:=1000000; v_count number:=0;begin for i in v_no_frist .. v_no_last loop --循环100万次 insert into Test_KIKI_01 values(seq_Test_KIKI_01.nextval, dbms_random.string('a',5),--随机获取26个字母的5个字母 dbms_random.string('a',10),--随机获取26个字母的10个字母 sysdate); v_count:=v_count+1; if v_count>=1000 then --每1000次提交一次 commit; v_count:=0; end if; end loop; commit;end;/
--复制P_Test_KIKI_01表结构到P_Test_KIKI_02create table Test_KIKI_02 as select * from Test_KIKI_01 where 1=2;--(1=1表示复制表结构和表数据，1=2表示只复制表结构)
create table Test_KIKI_03 as select * from Test_KIKI_01 where 1=2;
--创建错误记录表create table Log_KIKI_01( id number(20), sqlcode varchar2(255), sqlerror varchar2(255));
--创建触发器：当对表P_Test_KIKI_02进行操作的时候，把操作内容复制到表P_Test_KIKI_03create or replace trigger tr_Test_KIKI_02 --创建tr_Test_KIKI_02触发器 after insert or update or delete --在添加、修改和删除之后 on Test_KIKI_02 --当操作Test_KIKI_02表时触发次触发器 for each row --行级触发器begin if inserting then --添加时 insert into Test_KIKI_03 values(:new.id,:new.name,:new.password,:new.time);--插入新添加的值（:new表示新值） elsif updating then --修改时 insert into Test_KIKI_03 values(:old.id,:old.name,:old.password,:old.time);--插入被修改的老值（：old表示老值） elsif deleting then --删除时 insert into Test_KIKI_03 values(:old.id,:old.name,:old.password,:old.time);--插入被删除的老值（：old表示老值） end if;end;/
--创建存储过程:Test_KIKI_01中的数据按照id为偶数的复制到表Test_KIKI_02中，并且把id末尾为2.4.6.8的姓名改为‘li’‘wang’‘zaho’‘liu’create or replace procedure P_Test_KIKI_02 is type type_a is table of Test_KIKI_01%rowtype index by binary_integer; v_type_a type_a; type type_b_cur is ref cursor; v_type_b_cur type_b_cur; v_sql varchar2(255); v_count number:=1000; v_no number:=0; v_sqlerror varchar2(255); v_sqlcode varchar2(255);begin v_sql:='select * from Test_KIKI_01 where mod(id,2)=0 ';
open v_type_b_cur for v_sql;
loop fetch v_type_b_cur bulk collect into v_type_a limit v_count; if v_type_a.count=0 then exit; end if; for i in v_type_a.first .. v_type_a.last loop if substr(v_type_a(i).id,-1,1)=2 then v_type_a(i).name :='li'; elsif substr(v_type_a(i).id,-1,1)=4 then v_type_a(i).name :='wang'; elsif substr(v_type_a(i).id,-1,1)=6 then v_type_a(i).name :='zhao'; elsif substr(v_type_a(i).id,-1,1)=8 then v_type_a(i).name :='liu'; end if; begin insert into Test_KIKI_02 values(v_type_a(i).id,v_type_a(i).name,v_type_a(i).password,v_type_a(i).time); v_no:=v_no+1; exception when others then v_sqlerror :=substr(sqlerrm,1,200); insert into Log_KIKI_01 values(i,v_sqlcode,v_sqlerror); end; if v_no>=v_count then commit; v_no:=0; end if; end loop; commit; end loop; close v_type_b_cur;end P_Test_KIKI_02;/
create or replace procedure P_Test_KIKI_03 is type type_a is table of Test_KIKI_01%rowtype index by binary_integer; --自定义类型，类型来自Test_KIKI_01 v_type_a type_a; --定义变量，类型是上面的自定义类型 type type_b_cur is ref cursor; --定义游标类型 v_type_b_cur type_b_cur; --定义游标变量，指定上面自定义类型 v_sql varchar2(255); --定义变量，用于接收Test_KIKI_01的所有数据 v_count number:=1000; --定义变量用于分批次提交和分组 v_no number:=0; --定义计数器 v_sqlerror varchar2(255); --定义异常错误名 v_sqlcode varchar2(255); --定义异常错误编号begin v_sql:='select * from Test_KIKI_01 where mod(id,2)=0 ';--为v_sql赋值（查询出能被2整除的所有数据）；
open v_type_b_cur for v_sql;--打开游标，数据来自v_sql
loop fetch v_type_b_cur bulk collect into v_type_a limit v_count;--游标从集合里取数据，1000一组 if v_type_a.count=0 then --集合里没有数据时进入 exit; --退出 end if; for i in v_type_a.first .. v_type_a.last loop --for循环 从第一个到最后一个
begin --赋值 insert into Test_KIKI_02 values(v_type_a(i).id, --decode（substr(列，取最后1位，从1开始匹配）， --与2匹配，相同就赋值‘li’不同进行下一个匹配， --与4匹配，相同就赋值‘wang’不同进行下一个匹配， --与6匹配，相同就赋值‘zhao’不同进行下一个匹配， --与8匹配，相同就赋值‘liu’不同进行下一个匹配， --都不匹配赋值原有数据) decode(substr(v_type_a(i).id,-1,1),'2','li','4','wang','6','zhao','8','liu',v_type_a(i).name), v_type_a(i).password, v_type_a(i).time); v_no:=v_no+1; --计数器 exception when others then --自定义异常信息 v_sqlerror :=substr(sqlerrm,1,200);--截取异常信息（不截取会导致异常信息过长，从而插入失败） insert into Log_KIKI_01 values(i,v_sqlcode,v_sqlerror);--异常插入异常表中 end; if v_no>=v_count then --每1000条保存一次 commit; v_no:=0; --计数器归0 end if; --结束if end loop; commit;--插入数据 end loop; close v_type_b_cur;--关闭游标end P_Test_KIKI_03; --关闭存储过程/
--调用（执行）存储过程begin P_Test_KIKI_02;end;
--调用（执行）存储过程begin P_Test_KIKI_03;end;
--查询结果select * from Test_KIKI_01;select count(*) from Test_KIKI_01;
select * from Test_KIKI_02;select count(*) from Test_KIKI_02;
select * from Test_KIKI_03;select count(*) from Test_KIKI_03;
select * from Log_KIKI_01;select count(*) from Log_KIKI_01;
