mysql 存储过程 forall_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;