mysql 存储过程

命令行调用存储过程

存储过程中 字符串递增拼接 例如每次循环都向字符串添加内容 最后返回一个长字符串

mysql 批量修改某一列，例如往这一列 的字符串往后加添一个标记位(假设有statue这个字段 字段里边是字符串 然后批量往这个字符串添加一个字符串)

游标嵌套（循环失效）

DELIMITER $$


USE `idaplus`$$


DROP PROCEDURE IF EXISTS `client_migrate_2`$$


CREATE DEFINER=`root`@`localhost` PROCEDURE `client_migrate_2`()
BEGIN
    DECLARE  _done INT DEFAULT 0;
    DECLARE  _done1 INT DEFAULT 0; 
    DECLARE brokenClientNum INT DEFAULT 0;
    DECLARE brokenClientId VARCHAR(20);
    DECLARE availableClientId VARCHAR(20);
    DECLARE taskType VARCHAR(3);
    DECLARE avaiableTaskType VARCHAR(3);
    DECLARE unFinishBatchCount INT;
    DECLARE notDoBatchCount INT;
    DECLARE brokenUnfinishTaskNum INT;
    DECLARE brokenNotDoTaskNum INT;
    DECLARE avaiableClientCount  INT;
    DECLARE brokenClients VARCHAR(200);
    #DECLARE availableClientId VARCHAR(200);
    DECLARE SMS VARCHAR(300);
#    DROP TEMPORARY TABLE  IF EXISTS brokenClient;
#    DROP TEMPORARY TABLE  IF EXISTS avaiableClient;
#    CREATE TEMPORARY TABLE brokenClient(
# client_id  VARCHAR(20),
# tssk_type varchar(3)
#   ); 
#    CREATE TEMPORARY TABLE avaiableClient(
# client_id  VARCHAR(20),
# tssk_type VARCHAR(3)
#   ); 
#    insert into brokenClient(client_id,tssk_type) SELECT client_id,task_type FROM client_info WHERE shop_flag=0 AND (TIMESTAMPDIFF(MINUTE, NOW(), last_update_time) > -30 )=0 and delete_flag=0;
#    insert into avaiableClient(client_id,tssk_type) SELECT client_id,task_type FROM client_info WHERE shop_flag=0 AND (TIMESTAMPDIFF(MINUTE, NOW(), last_update_time) > -30 )=1 AND delete_flag=0;
    DECLARE curBrokenClient CURSOR FOR SELECT client_id,task_type FROM client_info WHERE shop_flag=0 AND (TIMESTAMPDIFF(MINUTE, NOW(), last_update_time) > -30 )=0 AND delete_flag=0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = 1;
    SET _done=0;
    SELECT COUNT(client_id) INTO brokenClientNum  FROM client_info WHERE shop_flag=0 AND (TIMESTAMPDIFF(MINUTE, NOW(), last_update_time) > -30 )=0 AND delete_flag=0;
    #INSERT INTO test(userfull_client_id) VALUES("调度二 重启");
    SET SMS="";
    IF brokenClientNum>0 THEN
#INSERT INTO test(userfull_client_id) VALUES("调度二 进入游标一"); 
OPEN curBrokenClient;
   #INSERT INTO test(userfull_client_id) VALUES("调度二 进入游标二");
brokenClientLoop :LOOP
FETCH curBrokenClient INTO brokenClientId,taskType;
BEGIN
DECLARE curavaiableClient CURSOR FOR SELECT client_id,task_type FROM client_info WHERE shop_flag=0 AND (TIMESTAMPDIFF(MINUTE, NOW(), last_update_time) > -30 )=1 AND delete_flag=0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done1 = 1;
SET _done1=0;
#INSERT INTO test(userfull_client_id) VALUES("失效客户端开始计算");
#INSERT INTO test(userfull_client_id) VALUES(_done);
IF   _done =1 THEN
LEAVE brokenClientLoop;
END IF;
INSERT INTO test(userfull_client_id) VALUES(taskType);
SELECT COUNT(*) INTO avaiableClientCount FROM client_info WHERE shop_flag=0 AND (TIMESTAMPDIFF(MINUTE, NOW(), last_update_time) > -30 )=1 AND delete_flag=0 AND task_type=taskType;
SELECT COUNT(*)  INTO brokenUnfinishTaskNum FROM task_manager  WHERE client_id = brokenClientId AND TASK_STATE!=5 AND dataDay=(SELECT DATE_FORMAT(NOW(),'%e'))AND UPDATE_DATE<NOW();
SELECT COUNT(*)  INTO brokenNotDoTaskNum FROM task_manager WHERE client_id =brokenClientId AND TASK_STATE!=5 AND dataDay=(SELECT DATE_FORMAT(NOW(),'%e'))AND UPDATE_DATE>NOW();
#INSERT INTO test(userfull_client_id) VALUES(avaiableClientCount);
#INSERT INTO test(userfull_client_id) VALUES(brokenUnfinishTaskNum);
#INSERT INTO test(userfull_client_id) VALUES(brokenNotDoTaskNum);
  # same taskTyp client
IF avaiableClientCount>0 THEN
INSERT INTO test(userfull_client_id) VALUES("调度二 开始对口调度");
SET unFinishBatchCount=CEILING(brokenUnfinishTaskNum/avaiableClientCount);
SET notDoBatchCount=CEILING(brokenNotDoTaskNum/avaiableClientCount);
#INSERT INTO test(userfull_client_id) VALUES(unFinishBatchCount);
#INSERT INTO test(userfull_client_id) VALUES(notDoBatchCount);
OPEN curavaiableClient;
avaiableCLient:LOOP
FETCH curavaiableClient INTO availableClientId,avaiableTaskType;
#INSERT INTO test(userfull_client_id) VALUES("计算");
#INSERT INTO test(userfull_client_id) VALUES(_done1);
IF  _done1=1 THEN  
LEAVE avaiableCLient;
END IF;
IF taskType=avaiableTaskType THEN
#INSERT INTO test(userfull_client_id) VALUES("调度二 update");
#INSERT INTO test(userfull_client_id) VALUES(_done1);
UPDATE task_manager SET client_id=availableClientId,UPDATE_DATE=(SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')),TASK_STATE_RECORD=CONCAT(TASK_STATE_RECORD,",D") WHERE client_id = brokenClientId AND TASK_STATE!=5 AND dataDay=(SELECT DATE_FORMAT(NOW(),'%e'))AND UPDATE_DATE<NOW() LIMIT unFinishBatchCount;
UPDATE task_manager SET client_id=availableClientId,UPDATE_DATE=(SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')),TASK_STATE_RECORD=CONCAT(TASK_STATE_RECORD,",D") WHERE client_id = brokenClientId AND TASK_STATE!=5 AND dataDay=(SELECT DATE_FORMAT(NOW(),'%e'))AND UPDATE_DATE>NOW() LIMIT notDoBatchCount;
SELECT CONCAT(SMS,"任务从",brokenClientId,"更新了",unFinishBatchCount,"到",availableClientId,",") INTO SMS;
END IF;  
#UNTIL _done1 
#END REPEAT avaiableCLient;
END LOOP;
CLOSE curavaiableClient;
ELSE
# mix taskTyp client
#INSERT INTO test(userfull_client_id) VALUES("调度二 开始混合调度");
SELECT COUNT(*) INTO avaiableClientCount FROM client_info WHERE shop_flag=0 AND (TIMESTAMPDIFF(MINUTE, NOW(), last_update_time) > -30 )=1 AND delete_flag=0 AND task_type=0;
SELECT COUNT(*)  INTO brokenUnfinishTaskNum FROM task_manager  WHERE client_id = brokenClientId AND TASK_STATE!=5 AND dataDay=(SELECT DATE_FORMAT(NOW(),'%e'))AND UPDATE_DATE<NOW();
SELECT COUNT(*)  INTO brokenNotDoTaskNum FROM task_manager WHERE client_id =brokenClientId AND TASK_STATE!=5 AND dataDay=(SELECT DATE_FORMAT(NOW(),'%e'))AND UPDATE_DATE>NOW();
IF avaiableClientCount>0 THEN
SET unFinishBatchCount=CEILING(brokenTaskNum/avaiableClientCount);
OPEN curavaiableClient;
avaiableCLient:LOOP
FETCH curavaiableClient INTO availableClientId;
IF  _done1=1 THEN  
LEAVE avaiableCLient;
END IF;
UPDATE task_manager SET client_id=availableClientId,UPDATE_DATE=(SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')),TASK_STATE_RECORD=CONCAT(TASK_STATE_RECORD,",D") WHERE client_id = brokenClientId AND TASK_STATE!=5 AND dataDay=(SELECT DATE_FORMAT(NOW(),'%e'))AND UPDATE_DATE<NOW() LIMIT unFinishBatchCount;
UPDATE task_manager SET client_id=availableClientId,UPDATE_DATE=(SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')),TASK_STATE_RECORD=CONCAT(TASK_STATE_RECORD,",D") WHERE client_id =brokenClientId AND TASK_STATE!=5 AND dataDay=(SELECT DATE_FORMAT(NOW(),'%e'))AND UPDATE_DATE>NOW() LIMIT notDoBatchCount;
SELECT CONCAT(SMS,"任务从",brokenClientId,"更新了",unFinishBatchCount,"到",availableClientId,",") INTO SMS;
#INSERT INTO test(userfull_client_id) VALUES(SMS);
#END IF;  
#UNTIL _done1 END REPEAT;
END LOOP;
CLOSE curavaiableClient;
ELSE
#to default client
#INSERT INTO test(userfull_client_id) VALUES("调度二 进入default调度");
UPDATE task_manager SET client_id="default",UPDATE_DATE=(SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')),TASK_STATE_RECORD=CONCAT(TASK_STATE_RECORD,",D") WHERE client_id = brokenClientId AND TASK_STATE!=5 AND dataDay=(SELECT DATE_FORMAT(NOW(),'%e'))AND UPDATE_DATE<NOW();
UPDATE task_manager SET client_id="default",UPDATE_DATE=(SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')),TASK_STATE_RECORD=CONCAT(TASK_STATE_RECORD,",D") WHERE client_id =brokenClientId AND TASK_STATE!=5 AND dataDay=(SELECT DATE_FORMAT(NOW(),'%e'))AND UPDATE_DATE>NOW(); 
SELECT CONCAT(SMS,"任务从",brokenClientId,"到","default",",") INTO SMS;
END IF;
END IF;
#END IF;
#until _done 
#end repeat brokenClient;
#INSERT INTO test(userfull_client_id) VALUES("调度二 大圈循环结束1");
#INSERT INTO test(userfull_client_id) VALUES(_done);
END;
END LOOP;
#INSERT INTO test(userfull_client_id) VALUES("调度二 大圈循环结束2");
CLOSE curBrokenClient;
#INSERT INTO test(userfull_client_id) VALUES("调度二 大圈循环结束2");
    INSERT INTO notice_info (recipient,content,notice_type,notice_statue,create_time) SELECT u_phoneNum,CONCAT("系统异常",SMS),'1','1',NOW() FROM user_table WHERE u_userType='1' AND u_phoneNum IS NOT NULL;
    SET SMS="";   
    END IF;
    #INSERT INTO test(userfull_client_id) VALUES("调度二 圆满完成");
    #INSERT INTO test(userfull_client_id) VALUES(SMS);
    END$$


DELIMITER ;