T-SQL遊标的使用範例

--定義變量

declare @i INT,@j int

declare @Propertyid CHAR(50)

declare @Title CHAR(50)

declare @RoomPic1 CHAR(50)

declare @RoomPic2 CHAR(50)

declare @RoomPic3 CHAR(50)

declare @HouseTypeShow1 CHAR(50)

declare @HouseTypeShow2 CHAR(50)

declare @HouseTypeShow3 CHAR(50)

declare @ComunityShow1 CHAR(50)

declare @ComunityShow2 CHAR(50)

declare @ComunityShow3 CHAR(50)

declare @VidCity CHAR(50)

declare @str char(50)

--定義遊标把PropertyAds 的值賦給mycursor 遊标

declare mycursor cursor for select PropertyId,Title,RoomPic1,RoomPic2,RoomPic3,HouseTypeShow1,HouseTypeShow2,HouseTypeShow3,ComunityShow1,ComunityShow2,ComunityShow3,VidCity from PropertyAds where Title is not null 

--declare mycursor cursor for select img,UseAs from EstateImgs

open mycursor --打開遊标

select @i=count(*) from PropertyAds where Title is not null  --得到記錄數

-- fetch next from mycursor INTO 将遊标中值賦給你定義的變量

fetch next from mycursor INTO @Propertyid,@Title,@RoomPic1,@RoomPic2,@RoomPic3,@HouseTypeShow1,@HouseTypeShow2,@HouseTypeShow3,@ComunityShow1,@ComunityShow2,@ComunityShow3,@VidCity

--開始循環@@fetch_status=0

while @@fetch_status=0 and @i>1

----esnt 戶ehxt--小區ehjt

BEGIN

    set @[email protected] 

    --裡面是你想要進行字段的判斷和一系列的操作

if(LEFT(@RoomPic1,4)='2011')

begin

----PRINT @Propertyid+' '[email protected]+' '[email protected]+' '+'esnt'+convert(varchar,getdate(),108) +' '[email protected]

----set @[email protected]+1

insert into PropertyImgs values(@Propertyid,@Title,@RoomPic1,'esnt',getdate(),null,@VidCity)

end 

if(LEFT(@RoomPic2,4)='2011')

begin

----PRINT @Propertyid+' '[email protected]+' '[email protected]+' '+'esnt'+convert(varchar,getdate(),108) +' '[email protected]

insert into PropertyImgs values(@Propertyid,@Title,@RoomPic2,'esnt',getdate(),null,@VidCity)

-- --set @[email protected]+1

end 

if(LEFT(@RoomPic3,4)='2011')

begin

--PRINT @Propertyid+' '[email protected]+' '[email protected]+' '+'esnt'+convert(varchar,getdate(),108) +' '[email protected]

insert into PropertyImgs values(@Propertyid,@Title,@RoomPic1,'esnt',getdate(),null,@VidCity)

--set @[email protected]+1

end 

if(LEFT(@HouseTypeShow1,4)='2011')

begin

--PRINT @Propertyid+' '[email protected]+' '[email protected]+' '+'esnt'+convert(varchar,getdate(),108) +' '[email protected]

insert into PropertyImgs values(@Propertyid,@Title,@HouseTypeShow1,'ehxt',getdate(),null,@VidCity)

--set @[email protected]+1

end 

if(LEFT(@HouseTypeShow2,4)='2011')

begin

--PRINT @Propertyid+' '[email protected]+' '[email protected]+' '+'esnt'+convert(varchar,getdate(),108) +' '[email protected]

--set @[email protected]+1

insert into PropertyImgs values(@Propertyid,@Title,@HouseTypeShow2,'ehxt',getdate(),null,@VidCity)

end 

if(LEFT(@HouseTypeShow3,4)='2011')

begin

--PRINT @Propertyid+' '[email protected]+' '[email protected]+' '+'esnt'+convert(varchar,getdate(),108) +' '[email protected]

--set @[email protected]+1

insert into PropertyImgs values(@Propertyid,@Title,@RoomPic1,'ehxt',getdate(),null,@VidCity)

end 

if(LEFT(@ComunityShow1,4)='2011')

begin

--PRINT @Propertyid+' '[email protected]+' '[email protected]+' '+'esnt'+convert(varchar,getdate(),108) +' '[email protected]

--set @[email protected]+1

insert into PropertyImgs values(@Propertyid,@Title,@ComunityShow1,'ehjt',getdate(),null,@VidCity)

end 

if(LEFT(@ComunityShow2,4)='2011')

begin

--PRINT @Propertyid+' '[email protected]+' '[email protected]+' '+'esnt'+convert(varchar,getdate(),108) +' '[email protected]

--set @[email protected]+1

insert into PropertyImgs values(@Propertyid,@Title,@ComunityShow2,'ehjt',getdate(),null,@VidCity)

end 

if(LEFT(@ComunityShow3,4)='2011')

begin

--PRINT @Propertyid+' '[email protected]+' '[email protected]+' '+'esnt'+convert(varchar,getdate(),108) +' '[email protected]

--set @[email protected]+1

insert into PropertyImgs values(@Propertyid,@Title,@ComunityShow3,'ehjt',getdate(),null,@VidCity)

end 

PRINT @RoomPic1 + ' ' + @RoomPic2+ '  '[email protected]

print @j

--這下面一句必須要有的也下最重要的一句重新指派

fetch next from mycursor INTO @Propertyid,@Title,@RoomPic1,@RoomPic2,@RoomPic3,@HouseTypeShow1,@HouseTypeShow2,@HouseTypeShow3,@ComunityShow1,@ComunityShow2,@ComunityShow3,@VidCity

END

close mycursor --關閉遊标

deallocate mycursor