游标 数据库表记录通常按组来使用,如想在库表中顺序提取每个记录来使用,就要使用游标
例1:显示男女性别之一 declare stuCh cursor static for select name,sex from stu
declare @num nvarchar(8) declare @sex int
open stuCh fetch first from stuCh into @num,@sex while (@@fetch_status=0) begin if @sex=1 print @num+'男' else print @num+'女' fetch next from stuCh into @num,@sex end close stuCh deallocate stuCh
例2:显示男女性别之二 declare @t table(num nvarchar(8),sex nvarchar(1))
declare stuCh cursor static for select name,sex from stu
declare @num nvarchar(8) declare @sex int
open stuCh fetch first from stuCh into @num,@sex while (@@fetch_status=0) begin if @sex=1 insert into @t values(@num,'男') else insert into @t values(@num,'女') fetch next from stuCh into @num,@sex end close stuCh deallocate stuCh
select * from @t
例3:显示每个学生的优良水平 declare stugrade cursor static for select number,avg(grade) from grade group by number
declare @num char(4) declare @gra int
open stugrade fetch first from stugrade into @num,@gra while (@@fetch_status=0) begin if @gra>85 print @num+'优秀' else print @num+'中等' fetch next from stugrade into @num,@gra end close stugrade deallocate stugrade