首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql存储过程

Mysql存储过程

作者头像
用户7053485
发布2020-06-19 16:39:56
8.8K0
发布2020-06-19 16:39:56
举报
文章被收录于专栏:Kitty的日常笔记Kitty的日常笔记
1、添加存储过程
DELIMITER //    
CREATE PROCEDURE AddLLdata(
in L_Longitude double ,
in L_Latitude double,
IN L_Elevation double,
IN L_LaserHeight double ,
IN L_FollowHeight double ,
IN L_PlaneId varchar(255),
IN L_FlyDate longtext 
)
BEGIN
insert into lldata(Longitude,Latitude,Elevation,LaserHeight,FollowHeight,PlaneId,FlyDate)
values(L_Longitude,L_Latitude,L_Elevation,L_LaserHeight,L_FollowHeight,L_PlaneId,L_FlyDate);
END //
DELIMITER ;


2、查询存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetAllchkdata`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
select TaskId,TaskChkLgd,TaskChkLtd,TaskChkHeight,TaskChkAzimuth,TaskChkTNum,
TaskChkElevation,TaskChkEndwiseSpeed,TaskChkAbeamSpeed,TaskChkFixTime,TaskChkMaxFF
,TaskChkTaskCode,PlaneId,FlyDate from chkdata;
END
3、查询两个时间之差
select UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP('2018-04-23 14:18:12');

4、添加存储过程(两张表的添加和修改双重判断判断)
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddEnginedata`(
IN `E_SteeringEngineTotalDis` int(11),
IN `E_EngineThrottle` INT,
IN `E_ActualSpeed` INT,
IN `E_TheoreticalSpeed` int(11) ,
IN `E_RotorSpeed` int(11) ,
IN `E_PlaneStatus` int(11),
IN `E_EngineStatus` int(11),
IN `E_PlaneId` VARCHAR(255),
IN `E_FlyDate` DATETIME

)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
/*定义一个变量存储是否存在的值*/
declare num int DEFAULT 0; 
insert into Enginedata (SteeringEngineTotalDis,EngineThrottle,ActualSpeed,TheoreticalSpeed,RotorSpeed,PlaneStatus,EngineStatus,PlaneId,FlyDate) 
values(E_SteeringEngineTotalDis,E_EngineThrottle,E_ActualSpeed,E_TheoreticalSpeed,E_RotorSpeed,E_PlaneStatus,E_EngineStatus,E_PlaneId,E_FlyDate) ;    
/*如果存在编号相同的话num+1;不存在的话值为0*/
set num=num+exists(select * from Copter where PlaneId=E_PlaneId);
/*如果num大于0则代表存在*/
if (num>0) 
THEN
/*如果存在的话,判断时间,时间差小于5s的则在飞行,否则不在飞行*/
if (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(E_FlyDate)>5) 
THEN
/*不在飞行*/
UPDATE Copter SET FlyState=0 WHERE PlaneId=E_PlaneId; 
/*在飞行*/
else
UPDATE Copter SET FlyState=1 WHERE PlaneId=E_PlaneId;
end if; 
else
/*如果不存在的话添加一条新的飞机编号到飞机表中*/
insert into Copter(PlaneId) values(E_PlaneId);
end if; 
END

5、关于排序,取多少条用limit

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddLLdata`(
IN `L_Longitude` double ,
IN `L_Latitude` double,
IN `L_Elevation` double,
IN `L_LaserHeight` double ,
IN `L_FollowHeight` double ,
IN `L_PlaneId` varchar(255),
IN `L_FlyDate` DATETIME

)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
/*定义一个变量存储是否存在的值*/
declare num int DEFAULT 0;
declare a double default 0.0;
declare b double default 0.0;
insert into lldata(Longitude,Latitude,Elevation,LaserHeight,FollowHeight,PlaneId,FlyDate)
values(L_Longitude,L_Latitude,L_Elevation,L_LaserHeight,L_FollowHeight,L_PlaneId,L_FlyDate);
/*如果存在编号相同的话num+1;不存在的话值为0*/
set num=num+exists(select * from Copter where PlaneId=L_PlaneId);
/*如果num大于0则代表存在*/
if (num>0) 
THEN
/*如果存在的话,那么根据时间和编号,去最新一条,然后就编号相同的经纬度做修改*/
select Longitude,Latitude from LLData order by FlyDate desc ,LId desc limit 1;    
update Copter set Longitude=a,Latitude=b where PlaneId=L_PlaneId ;
else
/*如果不存在的话添加一条新的飞机编号,经纬度到飞机表中*/
insert into Copter(PlaneId,Longitude,Latitude) values(L_PlaneId,L_Longitude,L_Latitude);
end if;

END

6、查出表中的值加上新的值更新原来的值
CREATE DEFINER=`root`@`localhost` PROCEDURE `Addstaticdata`(
IN `S_EngineRunTime` int(11) ,
IN `S_EngineRunUpTime` int(11),
IN `S_SelfDriveTime` int(11),
IN `S_FlyTime` int(11),
IN `S_PlaneId` varchar(255),
IN `S_FlyDate` DATETIME

)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
/*定义一个变量存储是否存在的值*/
declare num int DEFAULT 0; 
declare atotalFlyTime int default 0;/*总的飞行时长*/
declare atotalEngineRunTime int default 0;/*发动机总的运行时长*/
declare atotalEngineRunUpTime int default 0;/*发动机高速运转总时长*/
declare atotalSelfDriveTime int default 0;/*自驾总时长*/
insert into Staticdata (EngineRunTime,EngineRunUpTime,SelfDriveTime,FlyTime,PlaneId,FlyDate)
values(S_EngineRunTime,S_EngineRunUpTime,S_SelfDriveTime,S_FlyTime,S_PlaneId,S_FlyDate);
/*如果存在编号相同的话num+1;不存在的话值为0*/
set num=num+exists(select * from Copter where PlaneId=S_PlaneId);
/*如果num大于0则代表存在*/
if (num>0) 
THEN
/*先把相同飞机编号的数据存在变量中*/
select totalFlyTime,totalEngineRunTime,totalEngineRunUpTime,totalSelfDriveTime 
into atotalFlyTime,atotalEngineRunTime,atotalEngineRunUpTime,atotalSelfDriveTime from Copter where PlaneId=S_PlaneId;
/*然后变量再加上新添加的值*/
set atotalFlyTime=atotalFlyTime+S_FlyTime;
set atotalEngineRunTime=atotalEngineRunTime+S_EngineRunTime;
set atotalEngineRunUpTime=atotalEngineRunUpTime+S_EngineRunUpTime;
set atotalSelfDriveTime=atotalSelfDriveTime+S_SelfDriveTime;
/*修改相同飞机编号的飞机数据*/
update Copter set totalFlyTime=atotalFlyTime,totalEngineRunTime=atotalEngineRunTime,
totalEngineRunUpTime=atotalEngineRunUpTime,totalSelfDriveTime=atotalSelfDriveTime where PlaneId=S_PlaneId;
else
/*如果不存在的话添加一条新的飞机编号到飞机表中*/
insert into Copter(PlaneId,totalFlyTime,totalEngineRunTime,totalEngineRunUpTime,totalSelfDriveTime) values(S_PlaneId,S_FlyTime,S_EngineRunTime,S_EngineRunUpTime,S_SelfDriveTime);
end if; 
END


**字符串转时间格式,精确到毫秒
select DATE_FORMAT('2010-12-01 07:03:16.233','%Y-%m-%d %T:%f')

Content-Type:Application/json;charset=utf-8

select * from Chkdata;
select * from comdata;
select * from commdata;
select * from copter;
select *from Enginedata;
select * from heightdata;
select * from lldata;
select * from othdata;
select * from paramdata;
select* from speeddata;
select * from staticdata;
select * from steerdata;
select * from targetdata;
select * from yawdata;

 

DELIMITER //    
CREATE PROCEDURE GetFPDBy_PId_Date(
in StartTime    varchar(50),
in OverTime    varchar(50),
in PId varchar(255)
)
BEGIN

select FId,Lgd,Ltd,EH,CopAzimuth,CUH,PlaneId,FlyDate from fpd where PlaneId=PId && 
DATE_FORMAT(CONCAT(SUBSTRING_INDEX(FlyDate,' ',2),'.',substring_index(FlyDate,' ',-1)),'%Y-%m-%d %T:%f')
between DATE_FORMAT(CONCAT(SUBSTRING_INDEX(StartTime,' ',2),'.',substring_index(StartTime,' ',-1)),'%Y-%m-%d %T:%f')
and DATE_FORMAT(CONCAT(SUBSTRING_INDEX(OverTime,' ',2),'.',substring_index(OverTime,' ',-1)),'%Y-%m-%d %T:%f');    
END //
DELIMITER ;

 
本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2020-04-21 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档