mysql存储过程入门
https://dev.mysql.com/doc/refman/5.5/en/flow-control-statements.html
比如 if else 等
DROP PROCEDURE myJob; delimiter // CREATE PROCEDURE myJob() BEGIN DECLARE l_is_initialize INT(11); DECLARE l_yesterday VARCHAR(10); DECLARE l_yesterday_off_net_num INT(11); DECLARE l_yesterday_registered_num INT(11); DECLARE l_yesterday_con INT(11); DECLARE l_yesterday_active_user INT(11); DECLARE l_yesterday_per_con INT(11); DECLARE l_total_off_net_num INT(11); DECLARE l_total_registered_num INT(11); DECLARE l_total_con INT(11); DECLARE l_total_active_user INT(11); DECLARE l_total_per_con INT(11); DECLARE l_last_id INT(11); # 首次时 昨日统计数 means 截止昨日统计数 SELECT count(1) INTO l_is_initialize from stat_channel; # 获取昨天日期字符串 2016-01-01 SELECT CURRENT_DATE () - INTERVAL 1 DAY INTO l_yesterday ; IF l_is_initialize > 0 THEN # 昨日离网人数 SELECT count(1) INTO l_yesterday_off_net_num from patient where `status`= 2 and SUBSTR(unsubscribeTime, 1, 10) = l_yesterday; # 昨天新注册用户数 SELECT COUNT(1) INTO l_yesterday_registered_num from patient where SUBSTR(createtime, 1, 10) = l_yesterday; # 昨天咨询数 SELECT count(1) INTO l_yesterday_con from crm_order where `status` =2 and SUBSTR(updatetime, 1, 10) = l_yesterday; # 昨日活跃数 SELECT count(1) INTO l_yesterday_active_user from (SELECT count(1) from user_scan where SUBSTR(create_time, 1, 10) = l_yesterday GROUP BY PATIENT_ID ) t; # 昨日转换人数 SELECT COUNT(1) INTO l_yesterday_per_con from patient t1 where SUBSTR(t1.createtime, 1, 10) = l_yesterday and EXISTS (SELECT * from crm_consult t2 where t1.patientID = t2.patientid and SUBSTR(t2.modifytime, 1, 10) = l_yesterday and t2.STATUS = 2); ELSE # 首次:截止昨日 # 截止昨日离网人数 SELECT count(1) INTO l_yesterday_off_net_num from patient where `status`= 2 and SUBSTR(unsubscribeTime, 1, 10) <= l_yesterday; # 截止昨天新注册用户数 SELECT COUNT(1) INTO l_yesterday_registered_num from patient where SUBSTR(createtime, 1, 10) <= l_yesterday; # 截止昨天咨询数 SELECT count(1) INTO l_yesterday_con from crm_order where `status` =2 and SUBSTR(updatetime, 1, 10) <= l_yesterday; # 截止昨日活跃数 SELECT count(1) INTO l_yesterday_active_user from (SELECT count(1) from user_scan where SUBSTR(create_time, 1, 10) <= l_yesterday GROUP BY PATIENT_ID ) t; # 截止昨日转换人数 SELECT COUNT(1) INTO l_yesterday_per_con from patient t1 where SUBSTR(t1.createtime, 1, 10) <= l_yesterday and EXISTS (SELECT * from crm_consult t2 where t1.patientID = t2.patientid and SUBSTR(t2.modifytime, 1, 10) <= l_yesterday and t2.STATUS = 2); END IF; INSERT INTO stat_channel ( yesterday_off_net_num, yesterday_registered_num, yesterday_con, yesterday_active_user, yesterday_per_con, day ) VALUES( l_yesterday_off_net_num, l_yesterday_registered_num, l_yesterday_con, l_yesterday_active_user, l_yesterday_per_con, CURRENT_TIME() ); SELECT SUM(yesterday_off_net_num), SUM(yesterday_registered_num), SUM(yesterday_con), SUM(yesterday_active_user), SUM(yesterday_per_con) INTO l_total_off_net_num, l_total_registered_num, l_total_con, l_total_active_user, l_total_per_con FROM stat_channel; SET l_last_id = LAST_INSERT_ID(); UPDATE stat_channel SET total_off_net_num = l_total_off_net_num, total_registered_num = l_total_registered_num, total_con = l_total_con, total_active_user = l_total_active_user, total_per_con = l_total_per_con where id = l_last_id ; END// delimiter ;