sql server 游标示例

---游标更新删除当前数据

---1.声明游标

declare TicketCodeLot cursor scroll

for select TICKET_NUMBER,CODE,LOTNUMBER from [GBDB].[dbo].[LOCAL_TICKET_XFER]  where PROF_PROCESSED_DATE>DATEADD(MINUTE,-10,GETDATE())

--2.打开游标

open TicketCodeLot

--3.声明游标提取数据所要存放的变量

declare @TicketNO Varchar(30) ,@ProductCode varchar(15),@LotNo varchar(15)

--4.定位游标到哪一行

fetch First from TicketCodeLot into @TicketNO,@ProductCode,@LotNo  --into的变量数量必须与游标查询结果集的列数相同

while @@fetch_status=0  --提取成功，进行下一条数据的提取操作

begin

declare @InternalDes varchar(20)

SELECT  @InternalDes=a.Target

FROM Active_Specs a

WHERE Spec_ID =139 -- Internal Description

AND Char_ID in (SELECT Char_id

FROM characteristics

WHERE char_desc  =@ProductCode  )

and expiration_date is null

if @InternalDes='repack'

begin

update Events set PU_Id=83 where Event_Num=@TicketNO

declare @EventID varchar(20)

select  @EventID=Event_Id from [Events] where Event_Num in(@TicketNO)

update Tests set Var_Id=1047 where Event_Id in

(@EventID)and Result is not null and Var_Id>2

update Event_Details  set PU_Id=83

where Event_Id in (@EventID)

end

fetch next from TicketCodeLot into @OrderId ,@userId  --移动游标

end

--关闭游标

close TicketCodeLot

--释放游标

deallocate TicketCodeLot