老爹寫程式

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

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;
---------------------------------------------------------------------------------------------------------------