mysql存储过程

#这个是定义一个分隔符，因为一般是;结束，则会执行，而在存储过程中我们需要的是命令输入完毕执行
DELIMITER $$
DROP PROCEDURE IF EXISTS `refresh_device_city`$$
CREATE PROCEDURE refresh_device_city(IN device_id BIGINT,IN table_num INT)
BEGIN
#定义变量
DECLARE locationTableName VARCHAR(255);
DECLARE delete_sql VARCHAR(200); 
DECLARE insert_sql VARCHAR(2000); 
DECLARE err INT DEFAULT 0;  
-- 如果出现异常，会自动处理并rollback  
DECLARE EXIT HANDLER FOR  SQLEXCEPTION ROLLBACK ;   
START TRANSACTION;
SET locationTableName= CONCAT('lbs_device_location_', table_num);
SET @delete_sql = CONCAT('DELETE FROM lbs_device_city WHERE device_id=',device_id);
SET @insert_sql = CONCAT('INSERT INTO LBS_DEVICE_CITY (device_id,city,into_city_time,create_time) 
                             SELECT ',device_id,',a.city city,a.ltime ltime,NOW() FROM( SELECT MIN(a.city) city,MIN(a.ltime) ltime FROM (
                     SELECT CASE WHEN a.city!=@city THEN @i:=@i+1 ELSE @i END i,
                 CASE WHEN a.city!=@city THEN @city:=a.city ELSE @city END tcity,
                 a.city city,a.ltime ltime
                     FROM ( SELECT a.CITY city,a.LOCATION_TIME ltime FROM ',locationTableName,' a 
                 WHERE a.DEVICE_ID = ',device_id,' AND a.city !=\'\' AND a.CITY != \'未知\' 
                 ORDER BY a.LOCATION_TIME ASC) a ,(SELECT @i:=-1,@city:=\'未知\') b 
                         )a WHERE a.i != -1 GROUP BY a.i ) a ORDER BY a.ltime ASC');
#执行SQL语句
PREPARE delstmt FROM @delete_sql;
EXECUTE delstmt;
DEALLOCATE PREPARE delstmt;
PREPARE insstmt FROM @insert_sql;
EXECUTE insstmt;
DEALLOCATE PREPARE insstmt;
COMMIT;
END$$
DELIMITER ;



DELIMITER $$
DROP PROCEDURE IF EXISTS `refresh_all_device_city`$$
CREATE PROCEDURE refresh_all_device_city()
BEGIN
#查询所有在设备城市表中有数据的设备和对应定位所在表的编号
#循环设备，删除设备所有城市记录，重新插入数据,调用另一个存储过程
/*这种写法也可以：DECLARE done INT DEFAULT FALSE;*/
DECLARE done INT DEFAULT 0;  /*用于判断是否结束循环*/
DECLARE deviceid BIGINT; /*用于存储结果集S_S的记录（因为我这里S_S的记录只有一列且为bigint类型）*/
DECLARE tablenum INT;
/*定义游标*/
DECLARE idCur CURSOR FOR SELECT a.device_id,b.table_num FROM lbs_device_city a,lbs_device b WHERE a.device_id=b.id GROUP BY device_id;
/*定义 设置循环结束标识done值怎么改变 的逻辑*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*done = true;亦可*/
OPEN idCur;  /*打开游标*/
REPEAT
FETCH idCur INTO deviceid,tablenum;  /*这部分可以看看书，还可以fetch多列（假设结果集S_S的记录不是单列的话）*/
IF NOT done THEN  /*数值为非0，MySQL认为是true*/
CALL refresh_device_city(deviceid,tablenum);
END IF;
UNTIL done END REPEAT;
CLOSE idCur;  /*关闭游标*/
SELECT deviceid;
END$$
DELIMITER ;

CALL refresh_all_device_city();

 

/**处理单个设备的数据插入到定位统计表中*/
DROP PROCEDURE IF EXISTS count_device_location;
DELIMITER $$ -- 分隔符 因为一般是;结束，则会执行，而在存储过程中我们需要的是命令输入完毕执行
CREATE PROCEDURE count_device_location(IN deviceid BIGINT,IN tablenum BIGINT)--  接收参数，根据设备ID，查询对应的设备编号,
BEGIN
--  多个列的情况下似乎只能用 into 方式
DECLARE insert_sql VARCHAR(4000); 
DECLARE err INT DEFAULT 0;  


-- 如果出现异常，会自动处理并rollback  
DECLARE EXIT HANDLER FOR  SQLEXCEPTION ROLLBACK ;   
START TRANSACTION;
SELECT `device_id`, `org_key` INTO @deviceno, @orgkey FROM lbs_device WHERE `id`=deviceid; -- 注意参数不能与表中任何列名相同，否则不会取到参数

/*执行插入SQL*/  
-- 如果出现异常，会自动处理并rollback  
SET @insert_sql = CONCAT('INSERT INTO  LBS_DEVICE_LOCATION_COUNT  (DEVICE_NO,ORG_KEY,START_TIME,END_TIME,LOCATION_COUNT) SELECT \'',@deviceno
                          ,'\',\'',@orgkey,'\',CONCAT(b.location_count_time,\':00:00\'),DATE_ADD(b.location_count_time,INTERVAL 1 HOUR),b.num FROM 
                          (SELECT COUNT(a.id) num,a.location_count_time FROM (SELECT DATE_FORMAT(location_time,\'%Y-%m-%d %H\') 
                          location_count_time,id  FROM lbs_device_location_',tablenum,' WHERE device_id=',deviceid,') a GROUP BY a.location_count_time) b');
#执行SQL语句
PREPARE insstmt FROM @insert_sql;
EXECUTE insstmt;
DEALLOCATE PREPARE insstmt;
COMMIT;
END$$
DELIMITER ;

CALL count_device_location(29632,2);


DELIMITER $$
DROP PROCEDURE IF EXISTS `query_location_device_table`$$
CREATE PROCEDURE query_location_device_table()
BEGIN
#查询所有在设备城市表中有数据的设备和对应定位所在表的编号
#循环设备，删除设备所有城市记录，重新插入数据,调用另一个存储过程
/*这种写法也可以：DECLARE done INT DEFAULT FALSE;*/
DECLARE done INT DEFAULT 0;  /*用于判断是否结束循环*/
DECLARE deviceid BIGINT; /*用于存储结果集*/
/*定义游标*/
DECLARE idCur CURSOR FOR SELECT device_id FROM lbs_device_location_1 GROUP BY device_id ;
/*定义 设置循环结束标识done值怎么改变 的逻辑*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*done = true;亦可*/
OPEN idCur;  /*打开游标*/
REPEAT
FETCH idCur INTO deviceid;  /*fetch多列*/
IF NOT done THEN  /*数值为非0，MySQL认为是true*/
CALL count_device_location(deviceid,1);
END IF;
UNTIL done END REPEAT;
CLOSE idCur;  /*关闭游标*/
END$$
DELIMITER ;

 查询预警表中所有的记录，然后循环每一条，查询它是否有未结束的跟踪号，如果没有，则将该预警的状态置为无效

DELIMITER $$
USE `lbs_test`$$
DROP PROCEDURE IF EXISTS `clean_invalid_one_warning`$$
CREATE DEFINER=`lbs_test`@`%` PROCEDURE `clean_invalid_one_warning`(IN warning_id BIGINT)
BEGIN
    #查询这个预警是否有未结束的跟踪号
    SET @select_stmt=CONCAT('SELECT COUNT(b.id) INTO @cnt1 FROM lbs_warning a 
     INNER JOIN  `lbs_track_register` b ON a.`ORG_KEY`=b.`ORG_APIKEY` AND a.`TRACK_NO`=b.`TRACK_NO` WHERE a.id=',warning_id 
     ,' AND (b.`TRACK_END_TIME`>NOW() OR  b.`TRACK_END_TIME` IS NULL) ');  
 
    PREPARE stmt FROM @select_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SELECT @cnt1;
    #如果查询到未结束的跟踪号，开启一个事物，修改它为结束
    IF (@cnt1=0) THEN 
       #开启事物修改
       START TRANSACTION;
       #执行SQL语句
       SET @update_stmt=CONCAT('UPDATE LBS_WARNING SET `STATUS`=0 WHERE ID=',warning_id);  
       PREPARE upstmt FROM @update_stmt;
       EXECUTE upstmt;
       DEALLOCATE PREPARE upstmt;
       COMMIT;
    END IF;
END$$
DELIMITER ;



DELIMITER $$
USE `lbs_test`$$
DROP PROCEDURE IF EXISTS `clean_invalid_warning`$$
CREATE DEFINER=`lbs_test`@`%` PROCEDURE `clean_invalid_warning`()
BEGIN
#查询所有未结束的预警
#循环每个预警，查询它是否有未结束的跟踪号
#如果没有，则修改该预警为无效

/*这种写法也可以：DECLARE done INT DEFAULT FALSE;*/
DECLARE done INT DEFAULT 0;  /*用于判断是否结束循环*/
DECLARE warningid BIGINT; /*用于存储结果集*/
/*定义游标*/
DECLARE idCur CURSOR FOR SELECT id FROM lbs_warning WHERE `status`='1' ;
/*定义 设置循环结束标识done值怎么改变 的逻辑*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*done = true;亦可*/
OPEN idCur;  /*打开游标*/
REPEAT
FETCH idCur INTO warningid;  /*fetch赋值*/
IF NOT done THEN  /*数值为非0，MySQL认为是true*/
/**根据预警ID查询它是否有未结束的跟踪号*/
    CALL clean_invalid_one_warning(warningid);
END IF;
UNTIL done END REPEAT;
CLOSE idCur;  /*关闭游标*/
END$$
DELIMITER ;