oracle存储过程数据迁移,oracle 存储过程迁移数据

废话少说，首先建表：

-- Create table

create table ABIN4

(

ID1         NUMBER,

NAME1       NVARCHAR2(100),

CREATETIME1 DATE

)

tablespace SYSTEM

pctfree 10

pctused 40

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

-- Create table

create table ABIN5

(

ID1         NUMBER,

NAME1       NVARCHAR2(100),

CREATETIME1 DATE

)

tablespace SYSTEM

pctfree 10

pctused 40

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

建立表迁移存储过程(如果表abin5里面已经存在abin4里面迁移过来的记录，那么就不再执行插入操作)：

create or replace procedure abing

is

begin

declare

cursor mycur is select t.id1,t.name1,t.createtime1 from abin4 t;

sid abin4.id1%type;

sname abin4.name1%type;

screatetime abin4.createtime1%type;

num number;

begin

open mycur;

loop

fetch mycur into sid,sname,screatetime;

select count(*) into num from abin5 t where t.id1=sid and t.name1=sname and t.createtime1=screatetime;

if(num=0) then

insert into abin5 (id1,name1,createtime1) values (sid,sname,screatetime);

end if;

exit when mycur%NOTFOUND;

commit;

end loop;

close mycur;

end;

end;

建立Oracle定时器Job，让系统定时的去执行操作：

declare

myjob1 number;

begin

sys.dbms_job.submit(myjob1,'abing;',sysdate,'sysdate+1/2880');

commit;

end;