mysql函数+存储过程

来源：互联网发布：数据库更新数据语句编辑：程序博客网时间：2024/05/03 01:42

用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了，以后一定注意啊，要养成这样的好习惯

0 0