#####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要是个全集
#####setting 3:
http://m.blog.itpub.net/30345407/viewspace-2126548/
http://blog.csdn.net/benwang_/article/details/6830383
http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html
http://blog.csdn.net/u011098327/article/details/53941046
######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;
