我想从一个表中提取大约6百万行数据，并将它们全部插入到另一个表中。
我该如何使用BULK COLLECT和FORALL来实现？
如何在Pl/SQL中使用批量集合（bulk collect）和插入（insert）？
4
- user2295715
2
4个回答
11
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;
希望这有所帮助。
- Mohsen Heydari
5
1好的。
COMMIT 将会在 fetch_size 的批量中执行。另外，你能把 EXIT WHEN 立即移到 FETCH 吗？ - Maheswaran Ravisankar是的，EXIT WHEN 可以在 FETCH 之后立即执行（当前语法只是我的习惯 :)，我在 FORALL 后设置 COMMIT，以避免创建大的重做日志。感谢您的评论。 - Mohsen Heydari
注意，应该使用
EXIT WHEN new_table_array_object.COUNT = 0 而不是 EXIT WHEN old_table_cursor%NOTFOUND，这样才不会错过任何行（请参见 http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html）。 - SimonH与仅使用“循环”和“插入”相比，它是否提供了显著的性能改进？ - kaushalpranav
@kaushalpranav。正如智者所说：只有在测试时，你才会知道它！ - Mohsen Heydari
1
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
- XXX
1
使用bulk collect获取大量记录且未设置限制可能导致内存不足问题。 - Sathyajith Bhat
0
CREATE OR REPLACE PROCEDURE APPS.XXPPL_xxhil_wrmtd_bulk
AS
CURSOR cur_postship_line
IS
SELECT ab.process, ab.machine, ab.batch_no, ab.sales_ord_no, ab.spec_no,
ab.fg_item_desc brand_job_name, ab.OPERATOR, ab.rundate, ab.shift,
ab.in_qty1 input_kg, ab.out_qty1 output_kg, ab.by_qty1 waste_kg,
-- null,
xxppl_reports_pkg.cf_waste_per (ab.org_id,ab.process,ab.out_qty1,ab.by_qty1,ab.batch_no,ab.shift,ab.rundate) waste_percentage,
ab.reason_desc reasons, ab.cause_desc cause,
to_char(to_date(ab.rundate),'MON')month,
to_char(to_date(ab.rundate),'yyyy')year,
xxppl_org_name (ab.org_id) plant
FROM (SELECT a.org_id,
(SELECT so_line_no
FROM xx_ppl_logbook_h x
WHERE x.batch_no = a.batch_no
AND x.org_id = a.org_id) so_line_no,
(SELECT spec_no
FROM xx_ppl_logbook_h x
WHERE x.batch_no = a.batch_no
AND x.org_id = a.org_id
and x.SALES_ORD_NO=a.sales_ord_no
and x.OPRN_ID =a.OPRN_ID
and x.RESOURCES = a.RESOURCES) SPEC_NO,
(SELECT OPERATOR
FROM xx_ppl_logbook_f y, xx_ppl_logbook_h z
WHERE y.org_id = a.org_id
AND y.batch_no = a.batch_no
AND y.rundate = a.rundate
AND a.process = y.process
AND a.resources = y.resources
AND a.oprn_id = y.oprn_id
AND z.org_id = y.org_id
AND z.batch_no = y.batch_no
AND z.batch_id = z.batch_id
AND z.rundate = y.rundate
AND z.process = y.process) OPERATOR,
a.batch_no, a.oprn_id, a.rundate, a.fg_item_desc,
a.resources, a.machine, a.sales_ord_no, a.process,
a.process_desc, a.tech_desc, a.sub_invt, a.by_qty1,
a.by_qty2, a.um1, a.um2, a.shift,
DECODE (shift, 'I', 1, 'II', 2, 'III', 3) shift_rank,
a.reason_desc, a.in_qty1, a.out_qty1, a.cause_desc
FROM xxppl_byproduct_waste_v a
WHERE 1 = 1
--AND a.org_id = (CASE WHEN :p_orgid IS NULL THEN a.org_id ELSE :p_orgid END)
AND a.rundate BETWEEN to_date('01-'||to_char(add_months(TRUNC(TO_DATE(sysdate,'DD-MON-RRRR')) +1, -2),'MON-RRRR'),'DD-MON-RRRR') AND SYSDATE
---AND a.process = (CASE WHEN :p_process IS NULL THEN a.process ELSE :p_process END)
) ab;
TYPE postship_list IS TABLE OF xxhil_wrmtd_tab%ROWTYPE;
postship_trns postship_list;
v_err_count NUMBER;
BEGIN
delete from xxhil_wrmtd_tab;
OPEN cur_postship_line;
FETCH cur_postship_line
BULK COLLECT INTO postship_trns;
FORALL i IN postship_trns.FIRST .. postship_trns.LAST SAVE EXCEPTIONS
INSERT INTO xxhil_wrmtd_tab
VALUES postship_trns (i);
CLOSE cur_postship_line;
COMMIT;
END;
/
---- INSERING DATA INTO TABLE WITH THE HELP OF CUROSR
- abhijit kharde
1
0
SQL引擎解析并执行SQL语句，但在某些情况下，将数据返回给PL/SQL引擎。
在执行PL/SQL语句时，每个SQL语句都会导致两个引擎之间的上下文切换。当PL/SQL引擎找到SQL语句时，它会停止并将控制权传递给SQL引擎。SQL引擎执行语句并返回数据到PL/SQL引擎。这种控制转移称为上下文切换。通常，在PL/SQL引擎之间切换非常快，但是大量的上下文切换会影响性能。 SQL引擎检索所有行并将它们加载到集合中，然后切换回PL/SQL引擎。使用批量收集多行可以通过单个上下文切换获取。
示例：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;
- Bharat Negi
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
CREATE TABLE new_tbl_with_6m_rec AS SELECT * FROM old_tbl_with_6m_rec WHERE whatever_conditions;- Rachcha