批量插入，update

 

#####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;

转载于:https://www.cnblogs.com/feiyun8616/p/8342967.html