SQL存储过程基础语法及实例

1、定义变量简单赋值

        declare @a int  //声明一个变量a 赋初值为5

         set @a=

         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]() NOT NULL,

      [Rtx] [nvarchar]() NOT NULL,

      [Name] [nvarchar]() NOT NULL,

      [Password] [nvarchar](max) NULL,

      [State] [nvarchar]() NOT NULL,

     )

   create table #FlightState(Calsign nvarchar(),[State] nvarchar(),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=

  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()

declare @lastCalsign nvarchar()

declare @beforeState nvarchar()

declare @beforeStateTime datetime

declare @calsign nvarchar()

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(),[State] nvarchar(),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(),

@Depaddr nvarchar(),

@Arraddr nvarchar(),

@MsgType nvarchar(),

--起飞报的时候传deptime，落地arrtime

@time datetime,

@isDate  bit

AS

BEGIN

declare @timeDiff int

set @timeDiff=

declare @rtimeDiff int

declare @etd datetime

declare @eta datetime

declare @flightId int

declare @indexFlightId int

--定义一个游标查找FlightID

      declare cursor_FindFlight cursor for

      select top  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=

      fetch next from cursor_FindFlight into @etd,@eta,@flightId,@calsign

      while @@FETCH_STATUS=

          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 >

                 begin

                   set @indexFlightId =

                 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(),FlightId int,Calsign nvarchar(),DepTime datetime)

          --根据@indexFlightId更新数据库数据

          if @indexFlightId !=

              begin

                if @MsgType='FPL'

                 begin

                  update [188.10.34.13].[AKIS].[dbo].[ValidFlight] set EOBT=@time,FplTime=GETDATE(),FplFlag= where FlightId=@indexFlightId

                 end

                 if @MsgType='DEP'

                 begin

                  update [188.10.34.13].[AKIS].[dbo].[ValidFlight] set Atd=@time,DepCreateTime=GETDATE(),DepFlag= where FlightId=@indexFlightId

                 end

                 if @MsgType='ARR'

                 begin

                  update [188.10.34.13].[AKIS].[dbo].[ValidFlight] set Ata=@time,ArrCreateTime = GETDATE(),ArrFlag= 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= where FlightId=@indexFlightId

                 end

                --找到这条数据时放入临时表中

               insert into #Table (MsgType,FlightId,Calsign,DepTime) values (@MsgType,@indexFlightId,@Calsign,@time)

             end

         else

          begin

          if @isDate=

          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()

declare @Calsign nvarchar()

declare @Depaddr nvarchar()

declare @Arraddr nvarchar()

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(),Calsign nvarchar(),Depaddr nvarchar(),Arraddr nvarchar(),DepTime datetime,ArrTime datetime,CreateTime datetime)

  --insert into #DelayMsgTable select * from [ZHCCdb].[dbo].[DelayMsg] p where DATEDIFF(HOUR,CreateTime,getdate())<  order by CreateTime DESC

  insert into #DelayMsgTable (Id,MsgType,Calsign,Depaddr,Arraddr,DepTime,ArrTime,CreateTime)

  select top  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())<  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=

      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

巴特西

SQL存储过程基础语法及实例

最新文章

热门文章