#####setting 1
create table t as select * from all_objects where 1 =2;
###.模拟逐行提交的情况，注意观察执行时间
DECLARE
BEGIN
FOR cur IN (SELECT * FROM t_ref) LOOP
INSERT INTO t VALUES cur;
COMMIT;
END LOOP;
END;
/
###模拟批量提交
DECLARE
v_count NUMBER;
BEGIN
FOR cur IN (SELECT * FROM t_ref) LOOP
INSERT INTO t VALUES cur;
v_count := v_count + 1;
IF v_count >= 100 THEN
COMMIT;
v_count :=0;
END IF;
END LOOP;
COMMIT;
END;
/
更高级的方法，体验一下极限速度。
DECLARE
CURSOR cur IS
SELECT * FROM t_ref where column=<value>; <-就按照条件筛选数据,
TYPE rec IS TABLE OF t_ref%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 100;
FORALL i IN 1 .. recs.COUNT
INSERT INTO t VALUES recs (i);
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
/
#####setting 2:
--用 rownum 来限定取出的记录数来测试
cursor all_contacts_cur is
selectsr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;
###seting 3:
要么就按照条件筛选数据，分别提交
如:
insert into table_a select * from table_b where type=1;
commit;
insert into table_a select * from table_b where type=2;
commit;
就是举个例子,最后的type要是个全集
######update
declare
i int;--定义变量
v_count int;--定义变量
v_loop int;--定义变量
begin
select count(*) into v_count from test;--计算表内数据总数
select ceil(v_count/10) into v_loop from dual;--计算需要循环次数
i:=1;--为i赋值
while i<=v_loop loop--循环退出条件
update test set begintime=sysdate where begintime is null and rownum<=10;--执行更新
commit;--提交
i:=i+1;--i依次加1
end loop;--结束循环
end;
