如何在 Pl/SQl 中使用批量收集和插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21085897/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I use bulk collect and insert in Pl/SQl
提问by user2295715
I want to fetch around 6 millions rows from one table and insert them all into another table.
How do I do it using BULK COLLECTand FORALL?
我想从一个表中提取大约 600 万行并将它们全部插入到另一个表中。我如何使用BULK COLLECTand做到这一点FORALL？
回答by Mohsen Heydari
declare
-- define array type of the new table
TYPE new_table_array_type IS TABLE OF NEW_TABLE%ROWTYPE INDEX BY BINARY_INTEGER;
-- define array object of new table
new_table_array_object new_table_array_type;
-- fetch size on bulk operation, scale the value to tweak
-- performance optimization over IO and memory usage
fetch_size NUMBER := 5000;
-- define select statment of old table
-- select desiered columns of OLD_TABLE to be filled in NEW_TABLE
CURSOR old_table_cursor IS
select * from OLD_TABLE;
BEGIN
OPEN old_table_cursor;
loop
-- bulk fetch(read) operation
FETCH old_table_cursor BULK COLLECT
INTO new_table_array_object LIMIT fetch_size;
EXIT WHEN old_table_cursor%NOTFOUND;
-- do your business logic here (if any)
-- FOR i IN 1 .. new_table_array_object.COUNT LOOP
-- new_table_array_object(i).some_column := 'HELLO PLSQL';
-- END LOOP;
-- bulk Insert operation
FORALL i IN INDICES OF new_table_array_object SAVE EXCEPTIONS
INSERT INTO NEW_TABLE VALUES new_table_array_object(i);
COMMIT;
END LOOP;
CLOSE old_table_cursor;
End;
Hope this helps.
希望这可以帮助。
回答by XXX
CREATE OR REPLACE PROCEDURE fast_way IS
TYPE PartNum IS TABLE OF parent.part_num%TYPE
INDEX BY BINARY_INTEGER;
pnum_t PartNum;
TYPE PartName IS TABLE OF parent.part_name%TYPE
INDEX BY BINARY_INTEGER;
pnam_t PartName;
BEGIN
SELECT part_num, part_name
BULK COLLECT INTO pnum_t, pnam_t
FROM parent;
FOR i IN pnum_t.FIRST .. pnum_t.LAST
LOOP
pnum_t(i) := pnum_t(i) * 10;
END LOOP;
FORALL i IN pnum_t.FIRST .. pnum_t.LAST
INSERT INTO child
(part_num, part_name)
VALUES
(pnum_t(i), pnam_t(i));
COMMIT;
END
回答by Bharat Negi
The SQL engine parse and executes the SQL Statements but in some cases ,returns data to the PL/SQL engine.
SQL 引擎解析并执行 SQL 语句，但在某些情况下，将数据返回给 PL/SQL 引擎。
During execution a PL/SQL statement, every SQL statement cause a context switch between the two engine. When the PL/SQL engine find the SQL statement, it stop and pass the control to SQL engine. The SQL engine execute the statement and returns back to the data in to PL/SQL engine. This transfer of control is call Context switch. Generally switching is very fast between PL/SQL engine but the context switch performed large no of time hurt performance . SQL engine retrieves all the rows and load them into the collection and switch back to PL/SQL engine. Using bulk collect multiple row can be fetched with single context switch.
在执行 PL/SQL 语句期间，每个 SQL 语句都会导致两个引擎之间的上下文切换。当 PL/SQL 引擎找到 SQL 语句时，它会停止并将控制权交给 SQL 引擎。SQL 引擎执行语句并将数据返回到 PL/SQL 引擎。这种控制转移就是调用上下文切换。通常，PL/SQL 引擎之间的切换速度非常快，但执行上下文切换的时间不会影响性能。SQL 引擎检索所有行并将它们加载到集合中，然后切换回 PL/SQL 引擎。使用批量收集可以通过单个上下文切换获取多行。
Example : 1
示例：1
DECLARE
Type stcode_Tab IS TABLE OF demo_bulk_collect.storycode%TYPE;
Type category_Tab IS TABLE OF demo_bulk_collect.category%TYPE;
s_code stcode_Tab;
cat_tab category_Tab;
Start_Time NUMBER;
End_Time NUMBER;
CURSOR c1 IS
select storycode,category from DEMO_BULK_COLLECT;
BEGIN
Start_Time:= DBMS_UTILITY.GET_TIME;
FOR rec in c1
LOOP
NULL;
--insert into bulk_collect_a values(rec.storycode,rec.category);
END LOOP;
End_Time:= DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE('Time for Standard Fetch :-' ||(End_Time-Start_Time) ||' Sec');
Start_Time:= DBMS_UTILITY.GET_TIME;
Open c1;
FETCH c1 BULK COLLECT INTO s_code,cat_tab;
Close c1;
FOR x in s_code.FIRST..s_code.LAST
LOOP
null;
END LOOP;
End_Time:= DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE('Using Bulk collect fetch time :-' ||(End_Time-Start_Time) ||' Sec');
END;
