首先建表：
-- Create table
create table ABIN1
(
ID1 NUMBER,
NAME1 NVARCHAR2(100),
CREATETIME1 DATE default sysdate
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table ABIN2
(
ID2 NUMBER,
NAME2 NVARCHAR2(100),
CREATETIME2 DATE default sysdate
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table ABIN3
(
ID NUMBER,
NAME1 NVARCHAR2(100),
NAME2 NVARCHAR2(100),
CREATETIME1 DATE,
CREATETIME2 DATE
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
建立oracle存儲過程：
create or replace procedure enforcesome
is
begin
declare
cursor mycur is select t.id1,t.name1,t.createtime1,s.name2,s.createtime2 from abin1 t,abin2 s where t.id1=s.id2;
sid abin1.id1%type;
sname1 abin1.name1%type;
sname2 abin2.name2%type;
screatetime1 abin1.createtime1%type;
screatetime2 abin2.createtime2%type;
mysql varchar2(2000);
begin
open mycur;
loop
fetch mycur into sid,sname1,screatetime1,sname2,screatetime2;
exit when mycur%NOTFOUND;
mysql:='insert into abin3 (id,name1,name2,createtime1,createtime2) values ('||''''||sid||''''||','||''''||sname1||''''||','||''''||sname2||''''||','||''''||screatetime1||''''||','||''''||screatetime2||''''||')';
execute immediate mysql;
commit;
end loop;
close mycur;
end;
end;
編寫oracle Job定時器：
declare
myjob number;
begin
SYS.dbms_job.submit(myjob,'enforcesome;',sysdate,'sysdate+1/2880');
commit;
end;
執行Job定時器代碼：