mysql 函数 存储过程 事件(event) job 模板

-- ----------------------------
-- Function structure for f_get_childs
-- ----------------------------
DROP FUNCTION IF EXISTS `f_get_childs`;
DELIMITER ;;
CREATE FUNCTION `f_get_childs`(root_id INT) RETURNS varchar(1000) CHARSET utf8
-- 有些工具如Navicat，只需要BEGIN到END这一段，SQLyog需要从DELIMITER到最后
BEGIN
    DECLARE ptemp VARCHAR(1000);
    DECLARE ctemp VARCHAR(1000);
    SET ptemp = '#';
    SET ctemp =CAST(root_id AS CHAR);
    WHILE ctemp IS NOT NULL DO
        SET ptemp = CONCAT(ptemp,',',ctemp);
        SELECT GROUP_CONCAT(canton_id) 
          INTO ctemp 
          FROM sys_canton_info   
         WHERE FIND_IN_SET(parent_id,ctemp)>0; 
    END WHILE;  
    RETURN ptemp;  
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for p_write_log
-- ----------------------------
DROP PROCEDURE IF EXISTS `p_write_log`;
DELIMITER ;;
CREATE PROCEDURE `p_write_log`(IN p_obj_name varchar(64), IN p_err_desc varchar(255), IN p_sql_err varchar(1000))
BEGIN
  DECLARE v_error BOOL DEFAULT FALSE;
  DECLARE CONTINUE HANDLER FOR SQLWARNING,NOT FOUND, SQLEXCEPTION SET v_error = TRUE;
  START TRANSACTION;
  INSERT INTO sys_log(object_name, error_code, error_desc, trace)
  VALUES (p_obj_name, 'DB-FATAL', LEFT(p_err_desc, 255), LEFT(p_sql_err, 255));
  IF v_error = TRUE THEN
    ROLLBACK;
  ELSE
    COMMIT;
  END IF;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for p_coupon_cdk_generate
-- ----------------------------
DROP PROCEDURE IF EXISTS `p_coupon_cdk_generate`;
DELIMITER ;;
CREATE PROCEDURE `p_coupon_cdk_generate`(IN r_num bigint)
BEGIN
  DECLARE r_code varchar(32) charset utf8 default '';
  declare r_msg varchar(255) charset utf8 default 'success';
  declare l_count bigint default 0;
  declare l_code varchar(32) charset utf8 default '';
  declare l_counter bigint default 0;
  declare exit handler for sqlexception
    begin
      -- 先把错误取出来
      get diagnostics condition 1 r_code = returned_sqlstate, r_msg = message_text;
      rollback;
      call p_write_log('p_coupon_cdk_generate', concat('存储过程执行异常'),
                       concat('errcode:', ifnull(r_code, 'no-err-code'), ',errmsg:', ifnull(r_msg, 'no-err-msg')));
      select concat(ifnull(r_code, 'no-err-code'), '|', ifnull(r_msg, 'no-err-msg'));
      commit;
    end;
  start transaction;
  label_pro:
  begin
    set l_counter = r_num;
    while l_counter > 0 do
    set l_code = upper(right(MD5(uuid()), 6));
    if length(l_code + 0) = length(l_code) > 0 then --  通过随机的uu_id的MD5来取（纯数字的不要[遇到纯数字，跳出循环，等待下次执行]）
      set r_msg = '纯数字，退出';
      leave label_pro;
    end if;
    select count(0) into l_count
    from xy_coupon_cdk t
    where t.cdk = l_code;
    if l_count > 0 then
      set r_msg = '重复的邀请码，退出';
      leave label_pro;
    end if;

    insert into xy_coupon_cdk(cdk)
    values (l_code);

    set l_counter = l_counter - 1;
    end while;
  end;
  commit;
  select concat('流程结束!', r_msg, '，条数:', r_num - l_counter);
END
;;
DELIMITER ;

CREATE PROCEDURE `auto_fans_notify_handler`() 
BEGIN
  DECLARE v_result VARCHAR(100) default "";
  DECLARE v_total_count INT DEFAULT 100;
  declare v_notify_limit int default 3;
  DECLARE v_queue_name VARCHAR(64) default "wx.fans.sync.notify";
  declare l_notify_id varchar(32);
  declare done int default false;
  DECLARE err_code CHAR(5) DEFAULT '00000';
  DECLARE err_msg TEXT;
  
  DECLARE items CURSOR FOR (SELECT t.notify_id
            FROM wx_fans_notify t
           WHERE t.notify_status in (20,30)
             and t.notify_times <= v_notify_limit
             AND t.next_notify_time < NOW()
             AND t.next_notify_time > subdate(NOW(),INTERVAL 1 DAY)
             LIMIT v_total_count);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- 先回滚,再保存错误日志
    GET DIAGNOSTICS CONDITION 1 err_code = RETURNED_SQLSTATE, err_msg = MESSAGE_TEXT;
    
    ROLLBACK; -- RollBack之后 error信息就取不到了(FUCK)(所以得先把错误取出来再回滚!!)
    
    CALL wx_p_write_log('auto_fans_notify_handler','执行异常',CONCAT(IFNULL(err_code,'null1'), IFNULL(err_msg,'null2')));
    SELECT CONCAT(IFNULL(err_code,'null1'), IFNULL(err_msg,'null2'));
    COMMIT;    
  END;  
  open items;
    xloop: Loop
      fetch items into l_notify_id;
      if done then
        leave xloop;
      end if;
      
  update wx_fans_notify t
     set t.notify_status    = 30,
         t.notify_times     = t.notify_times + 1,
         t.next_notify_time = adddate(NOW(),interval 5 minute)
   where t.notify_id = l_notify_id
     and t.notify_status in (20,30);
      
  if row_count() > 0 then
     set v_result = wx_f_message_task_create(v_queue_name,concat('{"notify_id":"',l_notify_id,'"}'));
      if (v_result = "success") then
        commit;
      else
        rollback;
      end if;  
  end if;
  end loop xloop;
  close items;
END