mysql 存储过程

留个记录 保存一下

CREATE DEFINER=`root`@`::1` PROCEDURE `p_generate_live`()
BEGIN
    DECLARE print_debug BOOLEAN DEFAULT TRUE;
    DECLARE debug_string TEXT;
    DECLARE tabix INT DEFAULT 0;
    DECLARE min_date DATE DEFAULT '20160109';                          -- 传入参数
    DECLARE max_date DATE DEFAULT '20160131';                         -- 传入参数
    DECLARE time_of_min_date VARCHAR(10) DEFAULT '100000';   -- 传入参数
    DECLARE time_of_max_date VARCHAR(10) DEFAULT '110000';  -- 传入参数
    DECLARE no_more_result INT DEFAULT 0;
    DECLARE no_more_prog_list INT DEFAULT 0;


    DECLARE v_prog_chan_cd VARCHAR(20);
    DECLARE v_prog_play_date DATE;
    DECLARE v_prog_play_time VARCHAR(10);
    DECLARE v_prog_subj_time VARCHAR(10);
    DECLARE v_prog_subj_cd VARCHAR(20);
    DECLARE v_prog_procode VARCHAR(20);
    DECLARE v_prog_pgname VARCHAR(255);


    DECLARE v_live_long_cd VARCHAR(20);


    DECLARE sub_userids VARCHAR(10000);
    DECLARE msg_content VARCHAR(4000);


    DECLARE has_live VARCHAR(1) DEFAULT '';
    DECLARE has_long_live VARCHAR(1) DEFAULT '';


    /*这个只能按日期搜索 不准确*/
#     DECLARE cur_prog_list CURSOR FOR
#       SELECT z_chanl_cd, z_play_date, z_play_time, z_subj_time,
#         z_play_subj_cd, z_procode, z_pgnam
#       FROM zcdm_t_proglist
#       WHERE z_play_date >= min_date AND z_play_date <= max_date
#             AND z_play_form = '1'
#             AND z_play_subj_cd IN (SELECT DISTINCT
#                                      (z_subj_cd)
#                                    FROM temp_subj_follower);
     /*开始日期 开始日期的开始时间  结束日期  结束日期的结束日期*/
    DECLARE cur_prog_list CURSOR FOR
      SELECT z_chanl_cd, z_play_date, z_play_time, z_subj_time,
        z_play_subj_cd, z_procode, z_pgnam
      FROM zcdm_t_proglist
      WHERE z_pro_item_cd IN (SELECT z_pro_item_cd FROM temp_proglist_pk);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_result = 1;


    CREATE TEMPORARY TABLE IF NOT EXISTS temp_proglist_pk (
      z_pro_item_cd VARCHAR(20) NOT NULL);
    TRUNCATE TABLE temp_proglist_pk;


    CREATE TEMPORARY TABLE IF NOT EXISTS temp_subj_follower (
      Id        INT(11)     NOT NULL AUTO_INCREMENT,
      f_user_id VARCHAR(50) NOT NULL,
      z_subj_cd VARCHAR(20) NOT NULL, PRIMARY KEY (Id));
    TRUNCATE TABLE temp_subj_follower;
/*获取要检查的栏目代码*/
    INSERT INTO temp_subj_follower (f_user_id, z_subj_cd)
      SELECT f_user_id, f_follow_cd AS z_subj_cd
      FROM t_user_subs_follow
      WHERE f_user_id IN (SELECT
                            f_user_id
                          FROM v_user_subs_in_useble
                          WHERE f_msg_sub_cat = 'NOT02')
            AND f_follow_type = 'SUB'
      ORDER BY f_user_id ASC;


    IF min_date = max_date THEN
/*最大日期和最小日期相同的*/
      INSERT INTO temp_proglist_pk (z_pro_item_cd)
        SELECT z_pro_item_cd FROM zcdm_t_proglist
        WHERE z_play_date = min_date
              AND z_play_time >= time_of_min_date
              AND z_play_time <= time_of_max_date
              AND z_play_form = '1'
              AND z_play_subj_cd IN (SELECT DISTINCT
                                       (z_subj_cd)
                                     FROM temp_subj_follower);
    ELSEIF min_date < max_date THEN
/*最大日期大于最小日期*/
-- 先找最小日期的
        INSERT INTO temp_proglist_pk (z_pro_item_cd)
          SELECT z_pro_item_cd FROM zcdm_t_proglist
          WHERE z_play_date = min_date
                AND z_play_time >= time_of_min_date
                AND z_play_form = '1'
                AND z_play_subj_cd IN (SELECT DISTINCT
                                         (z_subj_cd)
                                       FROM temp_subj_follower);
-- 再找最大日期的
        INSERT INTO temp_proglist_pk (z_pro_item_cd)
          SELECT z_pro_item_cd FROM zcdm_t_proglist
          WHERE z_play_date = max_date
                AND z_play_time <= time_of_max_date
                AND z_play_form = '1'
                AND z_play_subj_cd IN (SELECT DISTINCT
                                         (z_subj_cd)
                                       FROM temp_subj_follower);
-- 再找日期范围中间的
        INSERT INTO temp_proglist_pk (z_pro_item_cd)
          SELECT z_pro_item_cd FROM zcdm_t_proglist
          WHERE z_play_date > min_date
                AND z_play_date < max_date
                AND z_play_form = '1'
                AND z_play_subj_cd IN (SELECT DISTINCT
                                         (z_subj_cd)
                                       FROM temp_subj_follower);
    END IF;


    OPEN cur_prog_list;
    FETCH cur_prog_list
    INTO v_prog_chan_cd, v_prog_play_date, v_prog_play_time, v_prog_subj_time,
      v_prog_subj_cd, v_prog_procode, v_prog_pgname;
    SET no_more_prog_list = no_more_result;
-- prog_list的not found 用no_more_prog_list 进行记录


    repeat_prog_list: REPEAT
      SET tabix = tabix + 1;
      SET debug_string = concat(tabix, '^', 'no_more_prog_list:', '^', no_more_prog_list);
      IF no_more_prog_list = 1 THEN
/* 游标刚一打开 就没有记录时，会执行这里 */
        CALL debug_msg(print_debug, debug_string);
        CALL debug_msg(print_debug, 'no prog list');
        LEAVE repeat_prog_list;
      END IF;
/* 判断是否有临时直送 */
      SET has_live = '';
      SELECT 1 INTO has_live FROM zcdm_t_live
      WHERE z_live_date = v_prog_play_date
            AND z_chanl_cd = v_prog_chan_cd
            AND z_live_betim = v_prog_play_time
            AND z_pro_rtmlen = v_prog_subj_time
            AND z_procode = v_prog_procode
            AND z_subj_cd = v_prog_subj_cd
      LIMIT 1;
      SET debug_string = concat(debug_string, '^', 'has_live:', has_live);
      SET no_more_result = 0;
-- 查询后重置no_more_result
      IF NOT has_live = '1' THEN
/*没有临时的再检查是否有长效的*/
/*先找长效直播协调单主表的主键*/
        SET has_long_live = '';
        SELECT 1 INTO has_long_live
        FROM zcdm_t_longlist
        WHERE z_live_long_cd =
              (SELECT z_live_long_cd
               FROM zcdm_t_longlive
               WHERE z_subj_cd = v_prog_subj_cd
                     AND z_beg_date <= v_prog_play_date
                     AND z_end_date >= v_prog_play_date
                     AND z_is_valid IS NULL)
              AND z_chanl_cd = v_prog_chan_cd
              AND z_live_betim = v_prog_play_time
              AND z_subj_length = v_prog_subj_time
              AND z_week LIKE (CONCAT('%', func_day_of_week(v_prog_play_date), '%'))
        LIMIT 1;
        SET no_more_result = 0;
-- 查询后重置no_more_result
        IF NOT has_long_live = 1 THEN
/* ===============需要发送=================*/
          SET debug_string = concat(debug_string, '^', 'no_live_need_send_msage');
-- 先获取要发送的人
          SELECT group_concat(f_user_id) INTO sub_userids
          FROM temp_subj_follower
          WHERE z_subj_cd = v_prog_subj_cd;
          IF ISNULL(sub_userids) || LENGTH(trim(sub_userids)) < 1 THEN
-- 发送
            SET msg_content = CONCAT(v_prog_chan_cd, '频道，在', v_prog_play_date, '播出的节目:"', v_prog_pgname, '(',
                                     v_prog_procode, ')"临近播出，但是还未填写直播协调单。');
            CALL debug_msg(print_debug, msg_content);
#             INSERT INTO t_msg_req(Z_SRC_SYS_ID,Z_REG_ID,Z_WF_ID,Z_MSG_TYPE,Z_FORM_ID,Z_MSG_CONTENT,Z_MSG_RECEIVER,
#         Z_MSG_STATUS,Z_CRT_USR,CREATE_TIMESTAMP)
#         VALUES('EF08','EF08003','','2',@procode,@msg_content,@sub_userids,'2','SYSTEM',NOW());
          END IF;
        ELSE
          SET debug_string = concat(debug_string, '^', 'has_long_live:', '^', has_long_live);
        END IF;
      END IF;
/*   CALL debug_msg(print_debug,concat(v_prog_chan_cd,",", v_prog_play_date,",", v_prog_play_time,",", v_prog_subj_time,",",
     v_prog_subj_cd,",", v_prog_procode,",", v_prog_pgname));  */
      FETCH cur_prog_list
      INTO v_prog_chan_cd, v_prog_play_date, v_prog_play_time, v_prog_subj_time,
        v_prog_subj_cd, v_prog_procode, v_prog_pgname;
      SET no_more_prog_list = no_more_result;
-- prog_list的not found 用no_more_prog_list 进行记录


      SET debug_string = concat(debug_string, '^|END|');
      CALL debug_msg(print_debug, debug_string);
    UNTIL no_more_prog_list = 1 END REPEAT;
    CLOSE cur_prog_list;


  END