Oracle中的Bulk Collect 和 ForAll 语句

1．使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。

2．使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。

使用例子：

(1)定义一个TABLE

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;

********************华丽的分割线**********************************

      CREATE OR REPLACE PROCEDURE sp_modifyENDTIME IS

         TYPE TabTaskInfo IS TABLE OF varchar2(16);
         vTaskid TabTaskInfo;
         TYPE DEnd_Time IS TABLE OF varchar2(64);
         dEndtime DEnd_Time;
         i Integer;
        --找出在Working_Status表中是小区以太网的订单，然后对里面的数据进行补录
        CURSOR GetEthernetINFO IS
            SELECT t.taskid,to_char(t.endtime,'yyyy-mm-dd hh24:mi:ss') AS TIME
                FROM WORKING_STATUS S,task T
                    WHERE s.taskid=t.taskid
                         AND EXISTS (SELECT *
                                                    FROM DES_SERVICE SE
                                                        WHERE SE.SERVICEID = S.SERVICEID
                                                            AND SE.SERVICEIDSEQ = S.SERVICEIDSEQ
                                                            AND se.servicetype='122030')
                                                            AND t.endtime IS NOT NULL;
           
       BEGIN
         OPEN GetEthernetINFO;
         FETCH GetEthernetINFO BULK COLLECT INTO vTaskid,dEndtime;
         CLOSE GetEthernetINFO;
         FORALL i IN vTaskid.FIRST..vtaskid.LAST
            UPDATE working_status SET end_time = to_date(dEndtime(i),'yyyy-mm-dd hh24:mi:ss'),working_status = 6 WHERE taskid=vtaskid(i);
       END sp_modifyENDTIME;