oracle存储过程实现大数据量表的复制

create or replace procedure tableBak
(
p_Table in varchar2,
p_TableBack in varchar2
)
as
pragma autonomous_transaction;
n_insertFirst number:=0;
n_insertLast number:=10000;
n_insertAll number:=0;
begin
select count(*) into allNumber from p_Table;
while 1=1 loop
EXECUTE IMMEDIATE
'create table as '||p_Table||sysdata||_bak||' select * from '|| p_TableBak ||' where rownum <= n_insertLast and rownum > n_insertFirst '
if SQL%NOTFOUND then
   exit;
else
  n_insertFirst:=n_insertLast;
   n_insertLast:=n_insertLast + 10000;
   if n_insertLast>n_insertAll then
   n_insertLast:=n_insertAll;
end if;
commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
end;

 

 

 

 

 

----------------------------------------------------

create or replace procedure p_tran_data  
as  
last_date date;  
begin  
select max(coldt) into last_date from tbnameB;  
insert into tbnameB select * from tbnameA where coldt > last_date;  
commit;
end;