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;

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

转载于:http://blog.itpub.net/62484/viewspace-919176/