假设从把A的数据插入到Bcreate or replace procedure insertb as a_row a%rowtype; cu sys_refcursor; begin open cu for select * from a; loop fetch cu into a_row; exit when cu%notfound; insert into b values a_row; if mod(cu%rowcount, 1000) = 0 then commit; end if; end loop; commit; close cu; end; 不过mod的效率低些。可以参考下
DECLARE CURSOR C_A IS SELECT * FROM TABLE_A; TYPE BB IS RECORD(..); BEGIN OPEN C_A; LOOP FETCH C_A BULK COLLECT INTO BB LIMIT 1000; FOR I IN 1 .. BB.COUNT LOOP INSERT INTO TABLE_B VALUES(BB.col1,bb.col2,..) ; END LOOP; COMMIT; EXIT WHEN C_PAYMENT_FEE%NOTFOUND; END LOOP; END;试下
a_row a%rowtype;
cu sys_refcursor;
begin
open cu for
select * from a;
loop
fetch cu
into a_row;
exit when cu%notfound;
insert into b values a_row;
if mod(cu%rowcount, 1000) = 0 then
commit;
end if; end loop;
commit;
close cu;
end;
不过mod的效率低些。可以参考下
CURSOR C_A IS SELECT * FROM TABLE_A;
TYPE BB IS RECORD(..);
BEGIN
OPEN C_A;
LOOP
FETCH C_A BULK COLLECT
INTO BB LIMIT 1000;
FOR I IN 1 .. BB.COUNT LOOP
INSERT INTO TABLE_B
VALUES(BB.col1,bb.col2,..) ;
END LOOP;
COMMIT;
EXIT WHEN C_PAYMENT_FEE%NOTFOUND;
END LOOP;
END;试下