1、创建表object_test和object_test1. 1
一、大批量数据处理
1、创建表object_test和object_test1
CREATE TABLE object_test AS
SELECT o.owner,o.object_name,o.last_ddl_time,o.status
FROM dba_objects o
WHERE 1>2;
CREATE TABLE object_test1 AS
SELECT o.owner,o.object_name,o.last_ddl_time,o.status
FROM dba_objects o
WHERE 1>2;
2、使用批量插入的方式插入数据
因为用批量插入的方法将dba_objects表中的数据插入到object_test中，只要了不到1秒的时间，所以执行了几次以后，让object_test的数据到达25万条，再插入到object_test1中。
DECLARE
CURSOR cur_objt_all IS
SELECT ot.owner,ot.object_name,ot.last_ddl_time,ot.status
FROM object_testot;
l_count NUMBER :=50000;
TYPE t_objt IS TABLE OF cur_objt_all%ROWTYPE INDEX BY BINARY_INTEGER;
v_t_objtt_objt;
BEGIN
OPEN cur_objt_all;
LOOP
FETCH cur_objt_all BULK COLLECT INTO v_t_objt LIMIT l_count;
EXIT WHEN cur_objt_all%NOTFOUND;
FORALL i IN v_t_objt.first..v_t_objt.last
INSERT INTO object_test1 VALUES v_t_objt(i);
COMMIT;
END LOOP;
COMMIT;
CLOSE cur_objt_all;
END;
1）查询表object_test中的数据
2）将object_test表的数据插入到object_test1表中
3） 按每批5万数据量进行插入提交，统共花了1.794s
3、在批处理处增加forall的异常处理
forall后只能有一条dml语句，但oracle让每执行一次这条dml语句的时候，都会设置一个savepoint，以方便出现异常状况时候的处理。如果直接处理exception，则发生异常的地方后面有数据的话，就会插入（假设dml语句做的是插入）不进去。这时，若save exceptions，则报错的地方就会回滚到上一个savepoint，即只记录此条发生的错误，然后继续运行下去。这样就会把符合约束的记录插入到表中，而报错的就会被跳过。最后，对该异常的数据，用dbms_output进行输出。
--purpose:实现大批量从一个表插入数据到另一个表中
DECLARE
CURSOR CUR_OBJT_ALL IS
SELECT OT.OWNER, OT.OBJECT_NAME, OT.LAST_DDL_TIME, OT.STATUS
FROM OBJECT_TEST OT;
L_COUNT NUMBER := 50000; --批处理的limit值
PO_ERROR_MSG VARCHAR2(500); --错误信息反馈
BULK_ERRORS EXCEPTION; --批处理异常
PRAGMA EXCEPTION_INIT(BULK_ERRORS, -24381);
TYPE T_OBJT IS TABLE OF CUR_OBJT_ALL%ROWTYPE INDEX BY BINARY_INTEGER;
V_T_OBJT T_OBJT;
BEGIN
OPEN CUR_OBJT_ALL;
LOOP
--批量插入
FETCH CUR_OBJT_ALL BULK COLLECT
INTO V_T_OBJT LIMIT L_COUNT;
EXIT WHEN CUR_OBJT_ALL%NOTFOUND;
--插入数据
BEGIN
FORALL I IN V_T_OBJT.FIRST .. V_T_OBJT.LAST SAVE EXCEPTIONS
INSERT INTO OBJECT_TEST1 VALUES V_T_OBJT (I);
--forall异常处理
EXCEPTION
WHEN BULK_ERRORS THEN
FOR N_ERROR_POINTER IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('error ' || N_ERROR_POINTER ||
': The error DML number is ' ||
SQL%BULK_EXCEPTIONS(N_ERROR_POINTER)
.ERROR_INDEX);
DBMS_OUTPUT.PUT_LINE('The Oracle error is ' ||
SQLERRM(-1 *
SQL%BULK_EXCEPTIONS(N_ERROR_POINTER)
.ERROR_CODE));
END LOOP;
END;
--提交
COMMIT;
END LOOP;
--扫尾处理
COMMIT;
CLOSE CUR_OBJT_ALL;
--异常处理
EXCEPTION
WHEN OTHERS THEN
PO_ERROR_MSG := 'Others exception raise!' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(PO_ERROR_MSG);
END;
结果：3.042seconds内完成插入操作
4、使用普通游标进行插入数据
1）--清空数据
TRUNCATE TABLE object_test1;
2）编写程序
考虑到批量处理的时候有5万条就提交一次，所以这里也有。
--purpose:使用普通游标实现大批量从一个表插入数据到另一个表中
--created by chenxi 2013-3-23
DECLARE
CURSOR CUR_OBJT_ALL IS
SELECT OT.OWNER, OT.OBJECT_NAME, OT.LAST_DDL_TIME, OT.STATUS
FROM OBJECT_TEST OT;
--记录类型
TYPE OBJECT_TEST_REC IS RECORD(
OWNER OBJECT_TEST.OWNER%TYPE,
OBJECT_NAME OBJECT_TEST.OBJECT_NAME%TYPE,
LAST_DDL_TIME OBJECT_TEST.LAST_DDL_TIME%TYPE,
STATUS OBJECT_TEST.STATUS%TYPE);
LV_OBJECT_TEST_REC OBJECT_TEST_REC;
LV_COUNT_COMMIT NUMBER := 50000; --插入5万条数据提交一次
LV_COUNT NUMBER := 0; --计数
BEGIN
OPEN CUR_OBJT_ALL;
LOOP
FETCH CUR_OBJT_ALL
INTO LV_OBJECT_TEST_REC;
EXIT WHEN CUR_OBJT_ALL%NOTFOUND;
INSERT INTO OBJECT_TEST1
(OWNER, OBJECT_NAME, LAST_DDL_TIME, STATUS)
VALUES
(LV_OBJECT_TEST_REC.OWNER,
LV_OBJECT_TEST_REC.OBJECT_NAME,
LV_OBJECT_TEST_REC.LAST_DDL_TIME,
LV_OBJECT_TEST_REC.STATUS);
--达到5万条的时候就提交一次
LV_COUNT := LV_COUNT + 1;
IF LV_COUNT = LV_COUNT_COMMIT THEN
COMMIT;
ELSE
NULL;
END IF;
END LOOP;
--扫尾操作
COMMIT;
CLOSE CUR_OBJT_ALL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Others exception raise!' || SQLERRM);
CLOSE CUR_OBJT_ALL;
END;
3）执行结果
结果：14.431seconds内完成插入操作
5、总结
由大批量插入只用了3.042seconds就完成了操作，而采用游标一条条插入就需要14.431seconds。
由上述数据，可以看出，节省的时间是相当可观的。而且oracle对大批量数据处理时出现的异常也有较完善的处理机制，允许我们在处理的时候出现部分错误，而不会影响所有数据的操作。所以，在处理大批量数据的时候，我们应该尽量使用bulk collect,forall来处理。
二、commit
1、每一条commit
1）编写程序
由于开始用了rownum对数据进行插入，直接就卡住了，就改成了下面这个简单的例子
--每一条commit
BEGIN
FOR L_COUNT IN 1 .. 100000 LOOP
INSERT INTO OBJECT_TEST1
(OWNER, OBJECT_NAME, LAST_DDL_TIME, STATUS)
Values(L_COUNT,'test',Sysdate,'test');
COMMIT;
END LOOP;
END;
2）运行结果
结果：在14.992s完成10万条数据的插入
2、最后commit
1）编写程序
--最后commit
BEGIN
FOR L_COUNT IN 1 .. 100000 LOOP
INSERT INTO OBJECT_TEST1
(OWNER, OBJECT_NAME, LAST_DDL_TIME, STATUS)
Values(L_COUNT,'test',Sysdate,'test');
END LOOP;
COMMIT;
END;
2）运行结果
结果：在5.991s完成10万条数据的插入
3、总结
每一条commit结果：在14.992s完成10万条数据的插入
最后commit结果：在5.991s完成10万条数据的插入
从上述数据，感觉差异是明显的。在达到一定数值的时候就commit一下，可以避免过多暂用内存空间，以及可能丢失大量数据。
三、forall与 for
1、forall
1)清空object_test2
TRUNCATE TABLE object_test2
2)程序编写
---forall
DECLARE
CURSOR CUR_BOJECT_TEST1 IS
SELECT OWNER, OBJECT_NAME, LAST_DDL_TIME, STATUS FROM OBJECT_TEST1;
TYPE T_OBJECT_TEST1 IS TABLE OF CUR_BOJECT_TEST1%ROWTYPE INDEX BY BINARY_INTEGER;
LV_OBJECT_TEST1 T_OBJECT_TEST1;
BEGIN
OPEN CUR_BOJECT_TEST1;
FETCH CUR_BOJECT_TEST1 BULK COLLECT
INTO LV_OBJECT_TEST1;
CLOSE CUR_BOJECT_TEST1;
FORALL I IN LV_OBJECT_TEST1.FIRST .. LV_OBJECT_TEST1.LAST
INSERT INTO OBJECT_TEST2 VALUES LV_OBJECT_TEST1 (I);
COMMIT;
END;
3）运行结果
结果：插入10万数据到object_test2，只用了0.296s
2、for
1)清空object_test2
TRUNCATE TABLE object_test2
2)程序编写
--for
DECLARE
CURSOR CUR_BOJECT_TEST1 IS
SELECT OWNER, OBJECT_NAME, LAST_DDL_TIME, STATUS FROM OBJECT_TEST1;
TYPE T_OBJECT_TEST1 IS TABLE OF CUR_BOJECT_TEST1%ROWTYPE INDEX BY BINARY_INTEGER;
LV_OBJECT_TEST1 T_OBJECT_TEST1;
BEGIN
OPEN CUR_BOJECT_TEST1;
FETCH CUR_BOJECT_TEST1 BULK COLLECT
INTO LV_OBJECT_TEST1;
CLOSE CUR_BOJECT_TEST1;
FOR I IN 1 .. LV_OBJECT_TEST1.COUNT LOOP
INSERT INTO OBJECT_TEST2
(OWNER, OBJECT_NAME, LAST_DDL_TIME, STATUS)
VALUES
(LV_OBJECT_TEST1(I).OWNER,
LV_OBJECT_TEST1(I).OBJECT_NAME,
LV_OBJECT_TEST1(I).LAST_DDL_TIME,
LV_OBJECT_TEST1(I).STATUS);
END LOOP;
COMMIT;
END;
3)运行结果
结果：插入10万数据到object_test2里，用了5.304s
3、总结
forall结果：插入10万数据到object_test2，只用了0.296s
for结果：插入10万数据到object_test2里，用了5.304s
在编写的过程中，尽量让其它因素都可以忽略，尽量使用大部分代码都相同，只有循环的部分是for与forall的差异。经过测试，我们可以看到在大数据量的循环中,forall有着绝对的优势。
posted on
