廢話少說(shuō)，首先建表：
-- 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
);
建立表遷移存儲(chǔ)過(guò)程(如果表abin5里面已經(jīng)存在abin4里面遷移過(guò)來(lái)的記錄，那么就不再執(zhí)行插入操作)：
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定時(shí)器Job，讓系統(tǒng)定時(shí)的去執(zhí)行操作：
declare
myjob1 number;
begin
sys.dbms_job.submit(myjob1,'abing;',sysdate,'sysdate+1/2880');
commit;
end;