mysql 存储过程例子

BEGIN

DECLARE ishave int DEFAULT 0;

/** current表字段 **/
DECLARE pro_id bigint DEFAULT 0;
DECLARE pro_gatewayid int DEFAULT 0;
DECLARE pro_sensorid int DEFAULT 0;
DECLARE pro_sensorhex varchar(20);
DECLARE pro_temperature NUMERIC(20,2);
DECLARE pro_humidity NUMERIC(20,2);
DECLARE pro_power NUMERIC(20,2);
DECLARE pro_gatewaytime datetime;
DECLARE pro_dyear int DEFAULT 0;
DECLARE pro_dmonth int DEFAULT 0;
DECLARE pro_dday int DEFAULT 0;
DECLARE pro_dhour int DEFAULT 0;
DECLARE pro_dmin int DEFAULT 0;
DECLARE pro_workingTime int DEFAULT 0;
DECLARE pro_hmax NUMERIC(20,2);
DECLARE pro_hmin NUMERIC(20,2);
DECLARE pro_tmax NUMERIC(20,2);
DECLARE pro_tmin NUMERIC(20,2);
DECLARE pro_type int DEFAULT 0;

/** 变量定义,传感器十进制编号**/
DECLARE pro_sensor_serial int;
DECLARE Done INT DEFAULT 0;
DECLARE no_more_record INT DEFAULT 0;

/* 声明游标，所有的传感器编号 */
DECLARE all_sensorserial CURSOR FOR select sensor_serial from dev_sensor_base GROUP BY sensor_serial;
/* 异常处理 */

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1;
/* 打开游标 */
OPEN all_sensorserial;

FETCH all_sensorserial into pro_sensor_serial; -- 取数据
WHILE no_more_record != 1 DO
-- current 表中是否有数据，如果current表中无数据就插入history表中最新的数据；如果有数据修改成history中最新的数据
select count(*) into ishave from data_current where sensorid=pro_sensor_serial;
if ishave=0 then
INSERT INTO data_current select * from data_history where sensorid=pro_sensor_serial ORDER BY gatewaytime desc LIMIT 1;
ELSE

select
id,
gatewayid,sensorid,sensorhex,temperature,humidity,power,gatewaytime,dyear,dmonth,dday,
dhour,dmin,workingTime,hmax,hmin,tmax,tmin,type
into
pro_id,
pro_gatewayid,
pro_sensorid,
pro_sensorhex,
pro_temperature,
pro_humidity,
pro_power,
pro_gatewaytime,
pro_dyear ,
pro_dmonth,
pro_dday,
pro_dhour,
pro_dmin,
pro_workingTime,
pro_hmax,
pro_hmin,
pro_tmax,
pro_tmin,
pro_type
from data_history where sensorid=pro_sensor_serial ORDER BY gatewaytime desc LIMIT 1;

update data_current set

gatewayid = pro_gatewayid,
sensorid = pro_sensorid,
sensorhex = pro_sensorhex,
temperature = pro_temperature,
humidity = pro_humidity,
power = pro_power,
gatewaytime = pro_gatewaytime,
dyear = pro_dyear ,
dmonth = pro_dmonth,
dday = pro_dday,
dhour = pro_dhour,
dmin = pro_dmin,
workingTime = pro_workingTime,
hmax = pro_hmax,
hmin = pro_hmin,
tmax = pro_tmax,
tmin = pro_tmin,
type = pro_type where sensorid=pro_sensor_serial;
end if;
FETCH all_sensorserial into pro_sensor_serial; -- 取数据
END WHILE;


/* 关闭游标 */
CLOSE all_sensorserial;
end