mysql5.6存储过程实例,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;--关闭定时任务

标签：customer,定时器,infreeze,mysql5.6,DECLARE,amount,实例,proxy,event

来源： https://www.cnblogs.com/mobaids/p/10853020.html