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

<!---->