游标实例答案.docVIP

1、创建一游标返回学生表中年龄小于20岁的结果集然后通过游标的定位语句，实现数据的更新将学生的年龄改为20 declare pp cursor for select * from s where age20 for update open pp fetch next from pp while(@@fetch_status=0) begin update s set age=20 where current of pp fetch next from pp end 2、创建一游标stu_cusor 返回成绩不及格的结果集，利用游标定位语句，删除成绩表中不及格的记录集中的第一条和第三条记录。 declare sc_cursor cursor for select * from sc where grade60 for update open sc_cursor fetch next from sc_cursor delete from sc where current of sc_cursor fetch next from sc_cursor fetch next from sc_cursor delete from sc where current of sc_cursor 3：创建一游标依次显示表中的信息 declare @sno char(8),@sn char(8),@i int set @i=1 declare mycursor scroll cursor for select sno,sname from s open mycursor fetch next from mycursor into @sno,@sn while(@@fetch_status=0) begin print 第+convert(varchar(20),@i)+条记录的学生学号和姓名是：+@sno+@sn fetch next from mycursor into @sno,@sn set @i=@i+1 end close mycursor deallocate mycursor 4、创建一游标查询各门课程前三名的学生成绩表 declare @cno char(8) declare mycursor scroll cursor for select cno from course open mycursor fetch next from mycursor into @cno while(@@fetch_status=0) begin print以下是+@cno+的前三名同学的成绩表 select top 3* from sc where cno=@cno order by grade desc fetch next from mycursor into @cno end close mycursor deallocate mycursor 5 、依次将学生表中的男生用M表示,女生用f表示 declare x cursor for select sno,sex from s open x declare @sno varchar(20),@sex char(2) fetch next from x into @sno,@sex while(@@fetch_status=0) begin if(@sex=女) select sno,sname,sex=ffrom s where sno=@sno else select sno,sname,sex=mfrom s where sno=@sno fetch next from x into @sno,@sex end close x deallocate x