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