Oracle数据库的BULK COLLECT用法之批量增删改的相关知识是本文我们主要要介绍的内容,FORALL语句的一个关键性改进,它可以大大简化代码,并且对于那些要在PL/SQL程序中更新很多行数据的程序来说,它可显著提高其性能。
用FORALL来增强DML的处理能力
Oracle为Oracle8i中的PL/SQL引入了两个新的数据操纵语言(DML)语句:BULK COLLECT和FORALL。这两个语句在PL/SQL内部进行一种数组处理;BULK COLLECT提供对数据的高速检索,FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。Oracle数据库使用这些语句大大减少了。
PL/SQL与SQL语句执行引擎的环境切换次数,从而使其性能有了显著提高。使用BULK COLLECT,你可以将多个行引入一个或多个集合中,而不是单独变量或记录中。下面这个BULK COLLECT的实例是将标题中包含有"PL/SQL"的所有书籍检索出来并置于记录的一个关联数组中,它们都位于通向该数据库的单一通道中。
类似地,FORALL将数据从一个PL/SQL集合传送给指定的使用集合的表。
BULK COLLECT和FORALL都非常有用,它们不仅提高了性能,而且还简化了为PL/SQL中的SQL操作所编写的代码。
create or replace function f_test_bluk_collection return number is
/*
--test bluk collection limit
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_tab prod_tab := prod_tab();
start_time number;
end_time number;
CURSOR products_data IS SELECT * FROM products;
BEGIN
Start_time := DBMS_UTILITY.get_time;
OPEN products_data;
LOOP
FETCH products_data BULK COLLECT
INTO products_tab LIMIT 10000;
EXIT WHEN products_data%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Processed ' || to_char(products_tab.count) ||
' rows');
END LOOP;
CLOSE products_data;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Bulk Collect: ' || to_char(end_time - start_time));
*/
--test forall
TYPE prod_tab IS TABLE OF products%ROWTYPE;
products_tab prod_tab := prod_tab();
start_time number;
end_time number;
BEGIN
-- Populate a collection - 100000 rows
SELECT * BULK COLLECT INTO products_tab FROM products;
EXECUTE IMMEDIATE 'TRUNCATE TABLE products';
Start_time := DBMS_UTILITY.get_time;
FOR i in products_tab.first .. products_tab.last LOOP
INSERT INTO products
(product_id, product_name, effective_date)
VALUES
(products_tab(i).product_id,
products_tab(i).product_name,
products_tab(i).effective_date);
END LOOP;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Conventional Insert:' || to_char(end_time - start_time));
EXECUTE IMMEDIATE 'TRUNCATE TABLE products';
Start_time := DBMS_UTILITY.get_time;
FORALL i in products_tab.first .. products_tab.last
INSERT INTO products VALUES products_tab (i);
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Bulk Insert :' || to_char(end_time - start_time));
commit;
return 0;
end f_test_bluk_collection;