Oracle 提供 bluk collect 語法可以一次取出多筆紀錄, 比起原先 loop fetch 一筆效能增加不少.
範例一：
(1)定義 type
CREATE OR REPLACE TYPE string_array AS TABLE OF VARCHAR2(100);
(2)在存儲過程裏面測試
DECLARE
v_array string_array;
BEGIN
---------------------------------------------------------------------------------------------------------------
PROCEDURE BULK_INSERT
IS
TYPE t_record IS TABLE OF all_talbes%ROWTYPE;
l_table t_record;
CURSOR curTab
IS
SELECT *
FROM all_tables a;
BEGIN
OPEN curOriTab;
LOOP
FETCH curTab BULK COLLECT INTO l_table LIMIT 5000;
FORALL i IN 1..l_table.COUNT
INSERT INTO cust_test VALUES l_table(i);
EXIT
WHEN curTab%NOTFOUND;
END LOOP;
CLOSE curTab;
END;
---------------------------------------------------------------------------------------------------------------
範例一：
(1)定義 type
CREATE OR REPLACE TYPE string_array AS TABLE OF VARCHAR2(100);
(2)在存儲過程裏面測試
DECLARE
v_array string_array;
BEGIN
SELECT cust_name BULK COLLECT INTO v_array
FROM all_tables c;
FORALL idx IN 1..v_table.COUNT
INSERT INTO cust_test VALUES(v_array(idx));
COMMIT;
END;---------------------------------------------------------------------------------------------------------------
PROCEDURE BULK_INSERT
IS
TYPE t_record IS TABLE OF all_talbes%ROWTYPE;
l_table t_record;
CURSOR curTab
IS
SELECT *
FROM all_tables a;
BEGIN
OPEN curOriTab;
LOOP
FETCH curTab BULK COLLECT INTO l_table LIMIT 5000;
FORALL i IN 1..l_table.COUNT
INSERT INTO cust_test VALUES l_table(i);
EXIT
WHEN curTab%NOTFOUND;
END LOOP;
CLOSE curTab;
END;
---------------------------------------------------------------------------------------------------------------
全站熱搜
留言列表
