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;