请选择 进入手机版 | 继续访问电脑版
搜索
房产
装修
汽车
婚嫁
健康
理财
旅游
美食
跳蚤
二手房
租房
招聘
二手车
教育
茶座
我要买房
买东西
装修家居
交友
职场
生活
网购
亲子
情感
龙城车友
找美食
谈婚论嫁
美女
兴趣
八卦
宠物
手机

Mysql存储过程

[复制链接]
查看: 92|回复: 0

2万

主题

2万

帖子

7万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
74933
发表于 2020-9-15 22:39 | 显示全部楼层 |阅读模式
  1. 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 )BEGINinsert 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 SQLNOT DETERMINISTICCONTAINS SQLSQL SECURITY DEFINERCOMMENT ''BEGINselect TaskId,TaskChkLgd,TaskChkLtd,TaskChkHeight,TaskChkAzimuth,TaskChkTNum,TaskChkElevation,TaskChkEndwiseSpeed,TaskChkAbeamSpeed,TaskChkFixTime,TaskChkMaxFF,TaskChkTaskCode,PlaneId,FlyDate from chkdata;END3、查询两个时间之差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 SQLNOT DETERMINISTICCONTAINS SQLSQL SECURITY DEFINERCOMMENT ''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; /*在飞行*/elseUPDATE Copter SET FlyState=1 WHERE PlaneId=E_PlaneId;end if; else/*如果不存在的话添加一条新的飞机编号到飞机表中*/insert into Copter(PlaneId) values(E_PlaneId);end if; END5、关于排序,取多少条用limitCREATE 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 SQLNOT DETERMINISTICCONTAINS SQLSQL SECURITY DEFINERCOMMENT ''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;END6、查出表中的值加上新的值更新原来的值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 SQLNOT DETERMINISTICCONTAINS SQLSQL SECURITY DEFINERCOMMENT ''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-8select * 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))BEGINselect 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 ;
复制代码


免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

技术支持:迪恩网络科技公司  Powered by Discuz! X3.2
快速回复 返回顶部 返回列表