mysql 存储过程

需求：从mysql轨迹库中查询历史轨迹，

生产环境说明：一天一个表如：gps_2013_09_22, gps_2013_09_23,......

CREATE DEFINER=`root`@`%` PROCEDURE `P_Query_Track`(IN `_CarId` varchar(10), IN `_StartTime` varchar(20), IN `_EndTime` varchar(20), IN `_IsMergeStop` TINYINT, IN `_IsMergeSpeed` TINYINT, IN `_IsAccurate` TINYINT)
BEGIN
/*
create date: 2013-09-22 17:22:00
modify date: 2013-09-22 17:22:00
authors: by yunnet
comment:
	_CarId varchar(10),      车辆序号
	_StartTime varchar(20),  开始时间
	_EndTime varchar(20),    结束时间
	_IsMergeStop TINYINT,    过滤速度等于0
	_IsMergeSpeed TINYINT,   过滤速度小于5
	_IsAccurate TINYINT      一分钟一条数据
for example: 
CALL P_Query_Track('1140', '2013-09-18 00:00:01', '2013-09-22 00:00:01', '1', '1', '1')

*/

declare Sql2 varchar(1000);
declare SqlText varchar(5000);
declare days int;
declare cur int;
declare tablename varchar(20);

SET days = DATEDIFF(_EndTime, _StartTime) + 1;
SET cur = 0;

WHILE days > 0 DO
	SET tablename = concat('gps_', DATE_FORMAT(DATE_ADD(_StartTime, INTERVAL cur DAY),'%Y_%m_%d'));	 
  if _IsAccurate = 1 THEN
    SET Sql2 = concat('SELECT car_id,gps_time,longitude,latitude,speed1,speed2,head,mileage1,mileage2,oil,status FROM
					            (SELECT car_id,gps_time,longitude,latitude,speed1,speed2,head,mileage1,mileage2,oil,status,  
									        		DATE_FORMAT(gps_time,\'%H\') as h,        
								              DATE_FORMAT(gps_time,\'%i\') as n, 
									        		DATE_FORMAT(gps_time,\'%s\') as s
				  		        	 FROM ', tablename,  
						       ' WHERE car_id = ', _CarId, 
						       '	and ((speed2 <> 0) or (1 <> ', _IsMergeStop, ') )',
                   '	and ((speed2 >= 5) or (1 <> ', _IsMergeSpeed, ') )',							     
									 '	and gps_time BETWEEN \'', _StartTime, '\' and \'', _EndTime, '\'', 
									' ORDER BY car_id, h, n, s DESC
													 ) tab 
													GROUP BY car_id, h, n');
  ELSE
    SET Sql2 = concat('SELECT car_id,gps_time,longitude,latitude,speed1,speed2,head,mileage1,mileage2,oil,status
											FROM ', tablename, 
											' WHERE car_id = ', _CarId, 
							        '	and ((speed2 <> 0) or (1 <> ', _IsMergeStop, ') )',
                      '	and ((speed2 >= 5) or (1 <> ', _IsMergeSpeed, ') )',							     
									    '	and gps_time BETWEEN \'', _StartTime, '\' and \'', _EndTime, '\''
                      );
  END IF;

  IF LENGTH(SqlText) > 0 THEN
    set SqlText = concat(SqlText,' union ', Sql2);  
  ELSE 
    set SqlText = Sql2;  
	end IF;
       

	SET cur = cur + 1;
	SET days = days - 1;
END WHILE; 

if (_IsAccurate <> 1) and (LENGTH(SqlText) > 0) THEN  
  SET SqlText = concat(SqlText, '	ORDER BY car_id, gps_time');
end IF;
  
SET @sql=SqlText;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END