mysql5.6.0 的存储过程 与 定时器 实例

一、写mysql存储过程应注意的几点：

1、声明变量（declare）时要注意字符集，用变量存储表字段时，表字段与变量的字符编码要一致。

2、mysql的字符合并不能用‘+’号，必须用concat函数。

3、每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束。

实例：

delimiter //		-- 将语句结束标志设为双斜杠，默认以逗号作为语句结束标志
CREATE  PROCEDURE `proxy_infreeze`()
BEGIN 
DECLARE done INT DEFAULT 0; 
DECLARE proxy_infreeze_amount DECIMAL(10,2) ; -- 注意位长也要声明，会有进位或舍弃
DECLARE customerId BIGINT ; 
DECLARE cursor_avgScore CURSOR FOR (
	SELECT SUM(update_amount) proxy_infreeze_amount,b.customer_id
	FROM customer_bill b
	WHERE bill_type =4 AND statu = 1 AND create_at > DATE_ADD(NOW(), INTERVAL -9 DAY) AND create_at < DATE_ADD(NOW(), INTERVAL -7 DAY)
	GROUP BY b.customer_id
); 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 控制游标的结束。
OPEN cursor_avgScore; 
FETCH  cursor_avgScore INTO proxy_infreeze_amount,customerId; 
out_loop:
	LOOP
	UPDATE customer_account SET proxy_freeze_amount =proxy_freeze_amount-proxy_infreeze_amount WHERE customer_id = customerId;  
		FETCH  cursor_avgScore INTO proxy_infreeze_amount,customerId;  
	IF done =1 THEN
				LEAVE out_loop;
	END IF;
END LOOP out_loop;
CLOSE cursor_avgScore; 
	update customer_bill set statu = 2 WHERE bill_type =4 AND statu = 1 AND create_at > DATE_ADD(NOW(), INTERVAL -9 DAY) AND create_at < DATE_ADD(NOW(), INTERVAL -7 DAY);
END // 
delimiter ;

　　

二 、 定时器

--查看定时策略是否开启
show variables like '%event_sche%';
-- 开启定时策略
set global event_scheduler=1;

--	创建定时任务event(事件)
create event batchDel_overdue_order_event
on schedule every 1 day starts '2016-10-01 23:50:00'
on completion preserve disable
do call batchDel_overdue_order();

--查看定时任务event(事件)，可以查看本机所有的事件
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;

alter event batchDel_overdue_order_event on completion preserve enable;--开启定时任务
alter event second_event on completion preserve disable;--关闭定时任务