- 浏览: 218586 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
hl174:
写的不错,赞一个
在Java中字节与十六进制的相互转换主要思想有两点 -
l540151663:
也是个好方法
String为空判断
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
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
发表评论
-
Access denied for user 'root'@'localhost' (using password:YES)
2018-11-07 14:26 550这两天在MyEclipse中开发Web项目时,连接MYSQL数 ... -
PowerDesigner导入sql脚本生成物理模型
2017-12-05 11:17 761(1)首先从sqlserver中生成脚本 (2)打开Power ... -
mysql 2006
2017-10-12 11:30 493当查询的结果集超过 max_allowed_packet 也会 ... -
修改myslq自动增长的ID数值
2017-10-10 10:30 415ALTER TABLE basic_object AUTO_ ... -
mysql 创建用户付权限及导出
2017-03-25 22:34 5501:创建用户,付权限 C:\soft\mysql\MySQL ... -
mysql 修改data的位置目录
2017-03-21 09:52 404具体操作: 一、停止MySQL 开始-cmd ne ... -
sql效率问题
2017-03-07 14:14 4531.关于SQL查询效率,100w数据,查询只要1秒,与您分享: ... -
mysql 创建用户付权限
2016-12-13 17:07 540连接数据库 mysql -u root -p 回车 ro ... -
mysql 触发器
2016-11-28 22:32 413直接在navicat.exe中设计表中的,触发器中添加 1: ... -
CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause问题
2016-11-24 11:44 393修改的最简单的办法,都把timestamp 设置为 可以为nu ... -
mysql 同行求最大最小值
2016-11-13 20:43 547SELECT GREATEST(3,5,1,8,33,99,3 ... -
redis总结一
2016-11-05 16:30 431http://edu.51cto.com/lesson/id- ... -
mysql合理配置连接池数量
2016-10-28 17:40 1131http://www.th7.cn/db/mysql/2015 ... -
mysql用命令备份数据库
2016-08-08 22:33 5751:导出数据库,主要用管理员身份运行cmd c:\Progra ... -
mysql 日期函数
2016-06-30 16:05 3851:查询年月日 select year(now()); sel ... -
mongodb总结1
2016-06-19 11:39 5681:登陆进入bin目录下( ... -
mysql保留2位小数查询
2016-06-15 18:54 1479SELECT T1. NAME, ... -
in8 win10 安装msi 提示2502、2503的错误代码
2016-05-02 17:09 659版权声明:本文为博主原创文章,未经博主允许不得转载。 ... -
mysql总结1
2016-04-24 21:41 4461:连接数据库 mysql -u root -p 回车 ... -
oracle 执行计划详解
2016-04-10 19:21 507简介: 本文全面详细介绍oracle执行 ...
相关推荐
mysql存储过程实例详细介绍了mysql存储过程的开发步骤
MySQL存储过程实例教程,MySQL存储过程实例教程
java 调用 mysql存储过程实例
PHP和MySQL存储过程实例 concat联合多例: select concat(user,":",jtsr) from jtsr;
MYSQL 存储过程 实例,要的自己下,分有点贵哦。
mysql存储过程实例详解
MySQL存储过程实例教程
MySQL存储过程实例教程详解.docxMySQL存储过程实例教程详解.docx
本文实例讲述了mysql存储过程之返回多个值的方法。分享给大家供大家参考,具体如下: mysql存储函数只返回一个值。要开发返回多个值的存储过程,需要使用带有INOUT或OUT参数的存储过程。咱们先来看一个orders表它的...
本文实例讲述了MySQL存储过程的异常处理方法。分享给大家供大家参考。具体如下: mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc -> (p_first_name VARCHAR(30), -> p_last_name VARCHAR(30)...
--1、创建存储过程-- if exists (select * from sysobjects where name='Sum_wage') drop procedure Sum_wage GO create procedure Sum_wage @PWage int, @AWage int, @total int as while (1=1) begin if (select...
给了一个小的mysql 存储过程的例子。同时给出了一个在pb9中调用的例子.本例子是给出的mysql8.0的存储过程。对于5.7一下的现在odbc很难有支持
本资源结合实例实现一个复杂的存储过程,存储过程中有用到游标、临时表、循环、递归等知识,sql文件附有实例数据表创建的sql语句。
带游标的mysql存储过程例子。