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 ;