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