mysql游标嵌入式_mysql存储过程外层游标内嵌一个子游标

CREATE DEFINER=`mysql`@`%` EVENT `event_settle_order_30day` ON SCHEDULE EVERY 24 HOUR STARTS '2018-12-07 02:00:00' ON COMPLETION NOT PRESERVE ENABLE DO

/*

此定时任务每天2点执行一次

*/

BEGIN

declare done int default 0;

declare day30 datetime;

-- 定义游标的取值字段

declare _driverId int(10);

declare _amount double;

declare _startTime datetime;

declare _endTime datetime;

declare _orderNo varchar(50);

declare _settleId int(10);

declare _count1 int(10);

declare driverSettleList cursor for

select

td.driver_id as driverId,

sum(tor.driver_settle_amount) as amount,

min(tor.finish_time) as startTime,

max(tor.finish_time) as endTime ,

count(1) as count1

from t_order tor

inner join t_order_vehicle_rel tovr on tovr.order_no=tor.order_no and tor.`status`=1

inner join t_driver td on td.driver_id=tovr.driver_id and td.`status`=1

where tor.finish_time

and tor.order_status=90

and (tor.invoice is null or tor.invoice=0)

and tor.driver_settle_id is null

group by td.driver_id;

-- 定义 设置循环结束标识done值怎么改变 的逻辑

declare continue handler for not FOUND set done=1;

set day30=date_add(NOW(), interval -30 day);

-- 打开游标

open driverSettleList;

label1:LOOP

fetch driverSettleList into _driverId,_amount,_startTime,_endTime,_count1;

if done then leave label1;

end if;

-- log

-- insert into t_test(content)value(CONCAT(_driverId,'-',_count1));

set _settleId=(select max(id)+1 from t_driver_settle);

insert into t_driver_settle (id,driver_id,amount,`status`,start_time,end_time,update_time,create_time,type,remark)

value(_settleId,_driverId,_amount,1,_startTime,_endTime,UTC_TIMESTAMP(),UTC_TIMESTAMP(),'cash',null);

begin

declare _inner tinyint(1) default 0;

declare settleOrderList cursor for

select tor.order_no as orderNo

from t_order tor

inner join t_order_vehicle_rel tovr on tovr.order_no=tor.order_no and tor.`status`=1

inner join t_driver td on td.driver_id=tovr.driver_id and td.`status`=1

where tor.finish_time

and tor.order_status=90

and (tor.invoice is null or tor.invoice=0)

and tor.driver_settle_id is null

and td.driver_id=_driverId;

declare continue handler for not found set _inner = 1;

open settleOrderList;

label2:loop

fetch settleOrderList into _orderNo;

if _inner=1 then leave label2;

end if;

-- insert into t_test(content)value(CONCAT(_driverId,'-',_orderNo,'-',_settleId));

update t_order set driver_settle_id=_settleId where order_no=_orderNo;

end loop label2;

close settleOrderList;

end;

end LOOP label1;

close driverSettleList;

END

分享到：

2018-12-07 16:34

浏览 18

分类:数据库

评论