SQL学习笔记之游标

use bus
select * from citybus

--定义一个简单的游标
declare cursor1 cursor for
select bus_no,stop_name from citybus
where bus_no='221' order by stop_name
open cursor1
fetch next from cursor1
while @@fetch_status=0
begin
fetch next from cursor1
end
close cursor1
deallocate cursor1
go

--定义一个带参的游标
declare @first varchar(20),@second varchar(20)
declare cursor2 cursor for
select bus_no,stop_name from citybus where bus_no='305' order by bus_no
open cursor2
fetch next from cursor2 into @first,@second
while @@fetch_status=0
begin
print 'the bus name is :'+convert(varchar(20),@first)
print 'the stop name is:'+convert(varchar(20),@second)
print '----------------------'
fetch next from cursor2 into @first,@second
end
close cursor2
deallocate cursor2
go

--查找221的第一站：
declare dd cursor for
select stop_name from citybus where bus_no='221'
open dd

fetch next from dd
close dd
deallocate dd
go

--search the 221's last busstop
declare last1 scroll cursor for
select top 5 stop_name from citybus where bus_no='221'
open last1
fetch last from last1
close last1
deallocate last1--delete the cursor.
go

--游标修改数据
 declare cur1 scroll cursor for select * from citybus
open cur1
fetch absolute 6 from cur1
update citybus set stop_name='营门口公交站' where id=4
close cur1
deallocate cur1
go