1、实验六、游标的应用declare xuehao char(8),xingming char(10),yuanxi chardeclare kcbianhao char(20),chengji chardeclare haha cursor for select 学号,姓名,院系 from 学生 open hahafetch from haha into xuehao,xingming,yuanxi while fetch_status=0begin print 学号: +xuehao+的学生姓名为+xingming+其院系是+yuanxideclare xixi cursor for sele
2、ct 课程编号,成绩 from 选课 where 学号=xuehaoopen xixifetch from xixi into kcbianhao,chengjiwhile fetch_status=0beginprint 课程编号为 +kcbianhao+的成绩是+chengji fetch from xixi into kcbianhao,chengji endclose xixideallocate xixifetch from haha into xuehao,xingming,yuanxi endclose hahadeallocate haha 实验七、存储过程及应用1.查询平均分
3、数在x到y范围内的学生信息。create procedure stux int,y intasselect 学号,姓名,名称 as 院系名称,平均成绩 as 平均分数 from 学生 join 院系 on 院系.编号=学生.院系where 平均成绩 between x and yexecute stu 80,852、更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩。create procedure stu2学号 char(8),课程编号char(8),成绩 smallint,currencycursor1 cursor varying output
4、as declare avg smallintset currencycursor1 =CURSOR for select * from 选课open currencycursor1;declare 学号char(8),课程编号 char(8),成绩smallintfetch from currencycursor1 into 学号, 课程编号, 成绩while FETCH_STATUS=0beginif 学号=学号 and 课程编号 =课程编号 beginupdate 选课set 成绩=成绩where current of currencycursor1endfetch from curre
5、ncycursor1 into 学号,课程编号, 成绩endclose currencycursor1deallocate currencycursor1select avg=AVG(成绩)from 选课where 学号= 学号return avgdeclare avg1 smallint,currencycursor3 cursor execute avg1=stu2 001,001,80, currencycursor3print 学号为的平均成绩为+ str(avg1)3、自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求） 。create procedu
6、re stu3x smallint,y smallintasselect * from 教师where 工资between x and yexecute stu3 30.00,45.00实验六、游标的应用declare xuehao char(8),xingming char(10),yuanxi chardeclare kcbianhao char(20),chengji chardeclare haha cursor for select 学号,姓名,院系 from 学生 open hahafetch from haha into xuehao,xingming,yuanxi while
7、fetch_status=0begin print 学号: +xuehao+的学生姓名为+xingming+其院系是+yuanxideclare xixi cursor for select 课程编号,成绩 from 选课 where 学号=xuehaoopen xixifetch from xixi into kcbianhao,chengjiwhile fetch_status=0beginprint 课程编号为 +kcbianhao+的成绩是+chengji fetch from xixi into kcbianhao,chengji endclose xixideallocate xi
8、xifetch from haha into xuehao,xingming,yuanxi endclose hahadeallocate haha 实验七、存储过程及应用1.查询平均分数在x到y范围内的学生信息。create procedure stux int,y intasselect 学号,姓名,名称 as 院系名称,平均成绩 as 平均分数 from 学生 join 院系 on 院系.编号=学生.院系where 平均成绩 between x and yexecute stu 80,852、更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩。
9、create procedure stu2学号 char(8),课程编号char(8),成绩 smallint,currencycursor1 cursor varying outputas declare avg smallintset currencycursor1 =CURSOR for select * from 选课open currencycursor1;declare 学号char(8),课程编号 char(8),成绩smallintfetch from currencycursor1 into 学号, 课程编号, 成绩while FETCH_STATUS=0beginif 学号
10、=学号 and 课程编号 =课程编号 beginupdate 选课set 成绩=成绩where current of currencycursor1endfetch from currencycursor1 into 学号,课程编号, 成绩endclose currencycursor1deallocate currencycursor1select avg=AVG(成绩)from 选课where 学号= 学号return avgdeclare avg1 smallint,currencycursor3 cursor execute avg1=stu2 001,001,80, currencycursor3print 学号为的平均成绩为+ str(avg1)4、自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求） 。create procedure stu3x smallint,y smallintasselect * from 教师where 工资between x and yexecute stu3 30.00,45.00
