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;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/62484/viewspace-919176/ ，如需转载，请注明出处，否则将追究法律责任。
