SQL存储过程基础语法及实例
1、定义变量简单赋值
declare @a int //声明一个变量a 赋初值为5
set @a=5
print @a //输出变量a
2、创建临时表
if OBJECT_ID('tempdb.#FlightState') is not null //如果存在先删除此表
begin
drop table #FlightState
end
CREATE TABLE #t //新建表
(
[ID] [int] NOT NULL,
[Oid] [int] NOT NULL,
[Login] [nvarchar](50) NOT NULL,
[Rtx] [nvarchar](4) NOT NULL,
[Name] [nvarchar](5) NOT NULL,
[Password] [nvarchar](max) NULL,
[State] [nvarchar](8) NOT NULL,
)
create table #FlightState(Calsign nvarchar(50),[State] nvarchar(50),ProcessTime datetime)
3、将查询结果集(多条数据)插入临时表
insert into #t select * from ST_User
insert into #FlightState (Calsign,[State],ProcessTime) select p.Calsign,p.StripState,p.ProcessTime from #Table p where FlightId=@beofreTomsFlightId
select @beforeAtd=Atd,@beforeAta=Ata,@beforeCalsign=Calsign from #table where FlightId=@beforeFlightId
insert into #FlightState (Calsign,[State],ProcessTime) values (@beforeCalsign,'',@beforeAtd)
SELECT * FROM #FlightState
4、游标
declare user_cur cursor for //定义游标
open user_cur //打开游标
while @@fetch_status=0
begin
fetch next from user_cur into @ID,@Oid,@Login //读取下一条游标
print @ID
--print @Login
end
close user_cur
deallocate user_cur //摧毁游标
5、实例
例1:把多表查询结果放入临时表
USE [CDM]
GO
/****** Object: StoredProcedure [dbo].[p_GetFlightProcess] Script Date: 2018/4/27 14:32:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[p_GetFlightProcess]
@flightId int
as
begin
declare @beforeFlightId int
declare @beofreTomsFlightId int
declare @tomsFlightId int
declare @beforeAtd datetime
declare @beforeAta datetime
declare @beforeCalsign nvarchar(50)
declare @lastCalsign nvarchar(50)
declare @beforeState nvarchar(50)
declare @beforeStateTime datetime
declare @calsign nvarchar(50)
declare @borktime datetime
declare @pocktime datetime
declare @EIBT datetime
declare @EOBT datetime
declare @understart datetime
declare @underend datetime
declare @boardstart datetime
declare @boardend datetime
--新建一个表如果已存在删除此表
if OBJECT_ID('tempdb.#FlightState') is not null
begin
drop table #FlightState
end
create table #FlightState(Calsign nvarchar(50),[State] nvarchar(50),ProcessTime datetime)
--查询数据并插入到临时表中
select @beforeFlightId=BeforFlightId,@beofreTomsFlightId=BeforTomsFlightId,@tomsFlightId=TomsFlightId from [AKIS].[dbo].[ValidFlight] where FlightId=@flightId
select @beforeAtd=Atd,@beforeAta=Ata,@beforeCalsign=Calsign from [AKIS].[dbo].[ValidFlight] where FlightId=@beforeFlightId
insert into #FlightState (Calsign,[State],ProcessTime) values (@beforeCalsign,'前序起飞',@beforeAtd)
insert into #FlightState (Calsign,[State],ProcessTime) values (@beforeCalsign,'前序落地',@beforeAta)
--select @lastCalsign=Calsign,@beforeState=StripState,@beforeStateTime=ProcessTime from [CDM].[dbo].[StripStateChgLog] where FlightId=@beofreTomsFlightId
--insert into #FlightState (Calsign,[State],ProcessTime) select p.Calsign,p.StripState,p.ProcessTime from [CDM].[dbo].[StripStateChgLog] p where FlightId=@beofreTomsFlightId
select @understart=UnderStartTime,@underend=UnderEndTime,@EIBT=EIBT from [188.10.34.19].[DataBase].[dbo].[FlightStationTimeArrival] where AkisFlightId=@beforeFlightId
insert into #FlightState (Calsign,[State],ProcessTime) values (@beforeCalsign,'上轮档',@EIBT)
insert into #FlightState (Calsign,[State],ProcessTime) values (@beforeCalsign,'开始下客',@understart)
insert into #FlightState (Calsign,[State],ProcessTime) values (@beforeCalsign,'下客结束',@underend)
select @calsign=Calsign from [AKIS].[dbo].[ValidFlight] where FlightId=@flightId
select @boardstart=BoardStartTime,@boardend=BoardEndTime,@borktime=BortTime,@pocktime=PockTime,@EOBT=EOBT from [188.10.34.19].[DataBase].[dbo].[FlightStationTime] where AkisFlightId=@flightId
insert into #FlightState (Calsig n,[State],ProcessTime) values (@calsign,'开始登机',@borktime)
insert into #FlightState (Calsign,[State],ProcessTime) values (@calsign,'开始上客',@boardstart)
insert into #FlightState (Calsign,[State],ProcessTime) values (@calsign,'上客结束',@boardend)
insert into #FlightState (Calsign,[State],ProcessTime) values (@calsign,'登机结束',@pocktime)
insert into #FlightState (Calsign,[State],ProcessTime) values (@calsign,'撤轮档',@EOBT)
--insert into #FlightState (Calsign,[State],ProcessTime) select p.Calsign,p.StripState,p.ProcessTime from [CDM].[dbo].[StripStateChgLog] p where FlightId=@TomsFlightId
--print(@beforeFlightId)
--print(@beofreTomsFlightId)
--print(@tomsFlightId)
SELECT * FROM #FlightState
--SELECT @flightstate=(select FlightState from [188.10.34.19][DataBase].[dbo].[FlightStationTimeArrival] where FlighId = @flightId ),@borktime=ArrAddrName,@pocktime=Etd,@EIBT=Eta,
end
例2:循环匹配更新数据
USE [ZHCCdb]
GO
/****** Object: StoredProcedure [dbo].[P_FindFlightId] Script Date: 2018/4/27 14:14:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[P_FindFlightId]
@Calsign nvarchar(50),
@Depaddr nvarchar(50),
@Arraddr nvarchar(50),
@MsgType nvarchar(50),
--起飞报的时候传deptime,落地arrtime
@time datetime,
@isDate bit
AS
BEGIN
declare @timeDiff int
set @timeDiff=99999
declare @rtimeDiff int
declare @etd datetime
declare @eta datetime
declare @flightId int
declare @indexFlightId int
--定义一个游标查找FlightID
declare cursor_FindFlight cursor for
select top 3 Etd,Eta,FlightId,Calsign from [188.10.34.13].[AKIS].[dbo].[ValidFlight] where Calsign=@Calsign and DepAddrName=@Depaddr and ArrAddrName=@Arraddr order by Etd Desc
open cursor_FindFlight
set @indexFlightId=0
fetch next from cursor_FindFlight into @etd,@eta,@flightId,@calsign
while @@FETCH_STATUS=0
begin
if(@MsgType='ARR')
begin
set @rtimeDiff = abs(datediff(MINUTE,@eta,@time))
if @rtimeDiff < @timeDiff
begin
set @timeDiff = @rtimeDiff
set @indexFlightId = @flightId
end
fetch next from cursor_FindFlight into @etd,@eta,@flightId,@calsign
end
else
begin
set @rtimeDiff = abs(datediff(MINUTE,@etd,@time))
if @rtimeDiff < @timeDiff
begin
set @timeDiff = @rtimeDiff
set @indexFlightId = @flightId
end
fetch next from cursor_FindFlight into @etd,@eta,@flightId,@calsign
end
end
if @timeDiff > 600
begin
set @indexFlightId = 0
end
close cursor_FindFlight
deallocate cursor_FindFlight
--定义临时表用于存放需要返回的数据
if OBJECT_ID('tempdb.#Table') is not null
begin
drop table #Table
end
create table #Table (MsgType nvarchar(50),FlightId int,Calsign nvarchar(50),DepTime datetime)
--根据@indexFlightId更新数据库数据
if @indexFlightId !=0
begin
if @MsgType='FPL'
begin
update [188.10.34.13].[AKIS].[dbo].[ValidFlight] set EOBT=@time,FplTime=GETDATE(),FplFlag=1 where FlightId=@indexFlightId
end
if @MsgType='DEP'
begin
update [188.10.34.13].[AKIS].[dbo].[ValidFlight] set Atd=@time,DepCreateTime=GETDATE(),DepFlag=1 where FlightId=@indexFlightId
end
if @MsgType='ARR'
begin
update [188.10.34.13].[AKIS].[dbo].[ValidFlight] set Ata=@time,ArrCreateTime = GETDATE(),ArrFlag=1 where FlightId=@indexFlightId
end
if @MsgType='DLA'
begin
update [188.10.34.13].[AKIS].[dbo].[ValidFlight] set Eta=@time where FlightId=@indexFlightId
end
if @MsgType='CNL'
begin
update [188.10.34.13].[AKIS].[dbo].[ValidFlight] set EOBT=' ',FplFlag=0 where FlightId=@indexFlightId
end
--找到这条数据时放入临时表中
insert into #Table (MsgType,FlightId,Calsign,DepTime) values (@MsgType,@indexFlightId,@Calsign,@time)
end
else
begin
if @isDate=0
if @MsgType='ARR'
begin
insert into [ZHCCdb].[dbo].[DelayMsg](MsgType,Calsign,Depaddr,Arraddr,ArrTime,CreateTime) values (@MsgType,@Calsign,@Depaddr,@Arraddr,@time,GETDATE())
end
else
begin
insert into [ZHCCdb].[dbo].[DelayMsg](MsgType,Calsign,Depaddr,Arraddr,DepTime,CreateTime) values (@MsgType,@Calsign,@Depaddr,@Arraddr,@time,GETDATE())
end
end
select * from #Table
END
例3:定义游标循环表中数据
USE [ZHCCdb] GO /****** Object: StoredProcedure [dbo].[P_DelayMsg] Script Date: 2018/4/27 14:08:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[P_DelayMsg] as begin declare @MsgType nvarchar(50) declare @Calsign nvarchar(50) declare @Depaddr nvarchar(50) declare @Arraddr nvarchar(50) declare @Deptime datetime declare @ArrTime datetime declare @CreateTime datetime declare @DelayId int declare @flightId int if OBJECT_ID('tempdb.#DelayMsgTable') is not null begin drop table #DelayMsgTable end create table #DelayMsgTable (Id int,MsgType nvarchar(50),Calsign nvarchar(50),Depaddr nvarchar(50),Arraddr nvarchar(50),DepTime datetime,ArrTime datetime,CreateTime datetime) --insert into #DelayMsgTable select * from [ZHCCdb].[dbo].[DelayMsg] p where DATEDIFF(HOUR,CreateTime,getdate())< 24 order by CreateTime DESC insert into #DelayMsgTable (Id,MsgType,Calsign,Depaddr,Arraddr,DepTime,ArrTime,CreateTime) select top 500 p.Id,p.MsgType,p.Calsign,p.Depaddr,p.Arraddr,p.DepTime,p.ArrTime,p.CreateTime from [ZHCCdb].[dbo].[DelayMsg] p where DATEDIFF(HOUR,CreateTime,getdate())< 24 order by CreateTime DESC --定义游标循环 #DelayMsgTable 数据 declare cursor_DelayMsg cursor for select Id,MsgType,Calsign,Depaddr,Arraddr,Deptime,ArrTime,CreateTime from #DelayMsgTable open cursor_DelayMsg fetch next from cursor_DelayMsg into @DelayId,@MsgType,@Calsign,@Depaddr,@Arraddr,@Deptime,@ArrTime,@CreateTime while @@FETCH_STATUS=0 begin if @MsgType = 'ARR' begin exec [dbo].[P_FindFlightId] @Calsign,@Depaddr,@Arraddr,@MsgType,@ArrTime,'true' end else begin exec [dbo].[P_FindFlightId] @Calsign,@Depaddr,@Arraddr,@MsgType,@Deptime,'true' end fetch next from cursor_DelayMsg into @DelayId,@MsgType,@Calsign,@Depaddr,@Arraddr,@Deptime,@ArrTime,@CreateTime end close cursor_DelayMsg deallocate cursor_DelayMsg
delete from [ZHCCdb].[dbo].[DelayMsg] where DATEDIFF(HOUR,CreateTime,getdate())> 24
end