oracle表同步 job,Oracle 同步表记录的程序

1.将该程序按照一个Oracle的JOB每10分钟执行一次,同步一个表的数据到另一个表.

2.该程序每1万行一提交,根据情况修改为合适的值.[@more@]

create or replace procedure sync_table(p_table_name varchar2,

p_to_table_name varchar2,

p_table_colume varchar2) as

v_col1 number(10);

v_end_col1 number(10);

sqlstr1 varchar2(2000);

begin

sqlstr1 := 'select nvl(max('||p_table_colume||'),0) from ' || p_to_table_name;

execute immediate sqlstr1 into v_col1;

sqlstr1 := 'select max('||p_table_colume||') from ' || p_table_name;

execute immediate sqlstr1 into v_end_col1;

loop

exit when v_end_col1 = v_col1;

sqlstr1 := 'select nvl(max('||p_table_colume||'),0) from ' ||p_to_table_name;

execute immediate sqlstr1 into v_col1;

sqlstr1 := 'insert into ' || p_to_table_name || ' select * from ' ||

p_table_name || ' where '||p_table_colume||' > '||v_col1||' and rownum <= 10000';

execute immediate sqlstr1;

if SQL%rowcount = 10000 then

commit;

else

null;

end if;

end loop;

commit;

exception

when others then

null;

end;