讲解FORALL与BULK COLLECT的使用方法_SQL技巧
1．使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。
2．使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。
使用例子：
(1)定义一个TABLE?? ----me add (type t_f1 is table of varchar2(20) type index by binary_integer)
CREATE OR REPLACE TYPE string_table AS TABLE OF VARCHAR2(100);
(2)在存储过程里面测试
DECLARE
v_table string_table;
BEGIN
SELECT cust_name
BULK COLLECT INTO v_table
FROM cust c
WHERE c.cust_id BETWEEN 64561 AND 64565;
FORALL idx IN 1..v_table.COUNT
INSERT INTO cust_test VALUES(v_table(idx));
COMMIT;
END;
(3)
?begin
?????
??????? forall indx in p_part_id.first .. p_part_id.last save exceptions
????????? insert into part_family
??????????? (part_id,
???????????? sub_part_id,
???????????? part_name,
???????????? sub_part_name,
???????????? owner,
???????????? project_name,
???????????? file_name,
???????????? v_id,
???????????? last_time)
????????? values
??????????? (p_parent_id(indx),
???????????? p_part_id(indx),
???????????? p_parent_name(indx),
???????????? p_part_name(indx),
???????????? p_owner(indx),
???????????? p_project_name,
???????????? p_file_name(indx),
???????????? p_version(indx),
???????????? sysdate);
????? -- forall 里好像只能有一个语句?????
????? exception
??????? when others then
????????? null;
????? end;
