实验试验.docVIP

实验六、游标的应用 declare @xuehao char(8),@xingming char(10),@yuanxi char declare @kcbianhao char(20),@chengji char declare haha cursor for select 学号,姓名,院系 from 学生 open haha fetch from haha into @xuehao,@xingming,@yuanxi while @@fetch_status=0 begin print 学号: +@xuehao+的学生姓名为+@xingming+其院系是+@yuanxi declare xixi cursor for select 课程编号,成绩 from 选课 where 学号=@xuehao open xixi fetch from xixi into @kcbianhao,@chengji while @@fetch_status=0 begin print 课程编号为 +@kcbianhao+的成绩是+@chengji fetch from xixi into @kcbianhao,@chengji end close xixi deallocate xixi fetch from haha into @xuehao,@xingming,@yuanxi end close haha deallocate haha 实验七、存储过程及应用 1.查询平均分数在x到y范围内的学生信息。 create procedure stu @x int,@y int as select 学号,姓名,名称 as 院系名称,平均成绩 as 平均分数 from 学生 join 院系 on 院系.编号=学生.院系 where 平均成绩 between @x and @y execute stu 80,85 2、更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩。 create procedure stu2 @学号char(8), @课程编号char(8), @成绩smallint, @currencycursor1 cursor varying output as declare @avg smallint set @currencycursor1 =CURSOR for select * from 选课 open @currencycursor1; declare @学号char(8),@课程编号char(8),@成绩smallint fetch from @currencycursor1 into @学号, @课程编号,@成绩 while @@FETCH_STATUS=0 begin if @学号=@学号and @课程编号=@课程编号 begin update 选课set 成绩=@成绩where current of @currencycursor1 end fetch from @currencycursor1 into @学号,@课程编号,@成绩 end close @currencycursor1 deallocate @currencycursor1 select @avg=AVG(成绩) from 选课 where 学号=@学号 return @avg declare @avg1 smallint,@currencycursor3 cursor execute @avg1=stu2 001,001,80, @currencycursor3 print 学号为的平均成绩为+str(@avg1) 自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求）。 create procedure stu3 @x smallint,@y smallint as select * from 教师 where 工资between @x and @y execute stu3 30.00,45.00 实验六、游标的应用 declare @xuehao char(8),@xingming char(10),@yuanxi char declare @kcbianhao char(20),@chengji char declare haha cursor for select 学号,姓名,院系 from 学生 open haha fetch from haha into @xuehao,@xingming,@yuanxi while @@fetch_status=0 begin