用mycat写的函数
------------------------------函数------------------------------
//这是一个计算输入一个日期和当前日期之间相差月份的函数
BEGIN
DECLARE m INTEGER(11);if birthday is null then
set birthday=now();
end if;
if day(birthday) > day(curdate()) THEN
set m = ((year(curdate())-year(birthday))*12+(month(curdate())-month(birthday)) -1);
else
set m = ((year(curdate())-year(birthday))*12+(month(curdate())-month(birthday)));
end IF;
RETURN m;
END
右键点击运行函数:跳出输入框
输入:‘2016-04-05’//记住一定要加单引号
直接输出结果:
---------------------------------存储过程【只需要点击右键,运行存储过程,输入参数即可】-------------------------------------------------------
s_push_user_iso_day
其中day_param 是声明的入参,在下图的名中设置参数名
在创建存储过程的时候定义的
BEGIN
DELETE FROM s_user_client_bind_ios_day WHERE day=day_param;
INSERT INTO s_user_client_bind_ios_day (suc_uid,day,suc_cid,os_version,client_version,create_time,update_time,device_no,birthday)
#IOS 数据 插入表s_user_client_bind_ios_day
SELECT
sucb.suc_uid,
getBTDays(sucb.`birthday`) AS day,
sucb.suc_cid,
sucb.os_version,
sucb.client_version,
NOW() as create_time,
NOW() as update_time,
sucb.device_no,
sucb.birthday
from s_user_client_bind sucb
WHERE getBTDays(sucb.`birthday`) =day_param
AND sucb.suc_type= 1 ;#1 IOS 2 是Android
END
---------------------------------------------------
s_push_user_android_gedui_day
BEGIN
#Routine body goes here...
DELETE FROM s_user_client_bind_android_getui_day where day=day_param;
INSERT INTO s_user_client_bind_android_getui_day (suc_uid,day,suc_cid,os_version,client_version,create_time,update_time,device_no,birthday)
#安卓-个推 插入表s_user_client_bind_andorid_getui_day
SELECT
sucb.suc_uid,
getBTDays(sucb.`birthday`) AS day,
sucb.suc_cid,
sucb.os_version,
sucb.client_version,
NOW() as create_time,
NOW() as update_time,
sucb.device_no,
sucb.birthday
from s_user_client_bind sucb
WHERE getBTDays(sucb.`birthday`) =day_param
AND sucb.suc_type= 2; #1 IOS 2 是Android
END
----------------------------------
s_push_user_android_yumeng_day
BEGIN
#Routine body goes here...
#安卓-友盟 插入表s_user_client_bind_andorid_yumeng_day
DELETE FROM s_user_client_bind_android_yumeng_day where day=day_param;
INSERT INTO s_user_client_bind_android_yumeng_day (suc_uid,day,suc_cid,os_version,client_version,create_time,update_time,device_no,birthday)
SELECT
sucba.uid,
getBTDays(sucba.birthday) AS day,
sucba.cid,
sucba.os_version,
sucba.client_version,
NOW() as create_time,
NOW() as update_time,
sucba.device_no,
sucba.birthday
FROM
s_user_client_bind_android sucba
where getBTDays(sucba.birthday)=day_param;
END;
存储写好以后,设置计划:设置:开始时间+ 频率
发现存储过程不执行
=======================一个事件里面,同时调用多个存储过程====================
没有加begin end:.....多么痛的领悟
这样就ok了,以后一定注意啊,要养成这样的好习惯