实验和.docVIP

1、游标的应用 单个标的游标 declare yx cursor for select * from 院系 open yx FETCH yx close yx deallocate yx 嵌套游标 declare @xuehao char(10),@name char(10),@yuanxi char(10) declare @kcbh char(8),@chengji char(10) declare xs cursor for select 学号,姓名,院系from 学生 open xs fetch from xs into @xuehao,@name,@yuanxi while @@fetch_status=0 begin print 学号：+@xuehao+姓名:+@name+所在院系为:+@yuanxi+所修课程 declare xk cursor for select 课程编号,成绩from 选课where 学号=@xuehao open xk fetch from xk into @kcbh,@chengji while @@fetch_status=0 begin print @kcbh+的成绩是:+@chengji fetch from xk into @kcbh,@chengji end close xk deallocate xk fetch from xs into @xuehao,@name,@yuanxi end close xs deallocate xs 存储过程 （1） 1、查询平均分数在x到y范围内的学生信息。 create procedure stu @x int,@y int as select 学号,姓名,名称,平均成绩 from 学生join 院系 on 院系.编号=学生.院系 where 平均成绩between @x and @y execute stu 60,70 2、更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩。 create procedure stu1 @xh char(8), @kcbh char(8), @cj int, @currencycursor1 cursor varying output as declare @avg smallint set @currencycursor1 =CURSOR for select * from 选课 open @currencycursor1; declare @xhh char(8),@kcbhh char(8),@cjj int fetch from @currencycursor1 into @xhh, @kcbhh,@cjj while @@FETCH_STATUS=0 begin if @xhh=@xh and @kcbhh=@kcbh begin update 选课 set 成绩=@cj where current of @currencycursor1 end fetch from @currencycursor1 into @xhh,@kcbhh,@cjj end close @currencycursor1 deallocate @currencycursor1 select @avg=AVG(成绩) from 选课 where 学号=@xh return @avg declare @avg smallint,@currencycursor cursor execute @avg=stu1 002,05,82,@currencycursor print 学号002的平均成绩为+str(@avg) （2） 自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求） 1、查询功能为：找出学时在一定范围内的课程的信息 create procedure stu2 @a int,@b int as select * from 课程 where 学时between @a and @b execute stu1 24,40 2、功能为：删除指定教师编号的教师的编号，并返回工资 create procedure stu3 @jsbh char(8), @currencycursor cursor varying output as declare @salary char(10) set @currencycursor =CURSOR for select 教师编号 from 教师 open @currencycursor; declare @js