实验九 游标操作和自定义函数
一.实验目的:
- 掌握游标的声明、游标打开、标数据的提取、游标的关闭和游标的释放
- 掌握标量值函数的定义与调用
- 掌握内联表值函数的定义与调用
- 掌握多语句表值函数的定义与调用
二.实验内容:(所有题写到实验报告中)
- 使用游标打印OrderManagement库中各订单中的总金额,要求按总金额降序排,打印格式如下:
use OrderManagement
declare om_cursor cursor
for select 订单号, sum(单价 * 数量) as total from order_detail group by 订单号 order by total;
declare @no char(6), @total int
open om_cursor
fetch next from om_cursor into @no, @total
print '订单号 总金额'
print '----------------'
while @@fetch_status = 0
begin
print @no + ' ' + cast(@total as char(10))
fetch next from om_cursor into @no, @total
end
close om_coursor
deallocate st_cursor
2.使用游标提取学生课程库中3-105课的前三名学生的信息和后三名学生的信息,包括学号、姓名、课程名和成绩。(不考虑成绩相同的情况)
use stuinfo
declare st_cursor scroll cursor
for select student.sno, student.sname, course.cname, score.degree from student, course, score
where score.cno = '3-105' and student.sno = score.sno and score.cno = course.cno
order by degree desc
declare @sno char(5), @sname char(10), @cname char(16), @degree float
open st_cursor
print '学号 姓名 课程名 成绩'
print '------------------------------'
declare @i int
select @i = 1
fetch next from st_cursor into @sno, @sname, @cname, @degree
while @i <= 3
begin
print @sno + ' ' + @sname + ' ' + @cname + ' ' + cast(@degree as char(5))
fetch next from st_cursor into @sno, @sname, @cname, @degree
set @i += 1
end
fetch last from st_cursor into @sno, @sname, @cname, @degree
set @i = 1
while @i <= 3
begin
print @sno + ' ' + @sname + ' ' + @cname + ' ' + cast(@degree as char(5))
fetch prior from st_cursor into @sno, @sname, @cname, @degree
set @i += 1
end
close st_cursor
deallocate st_cursor
3.使用游标修改OrderManagement库的order_detail表中订单号为***的单价最高的器件的单价和单价最低的器件的单价。
use OrderManagement
declare cur scroll cursor for
select 单价, 订单号 from order_detail where 订单号 = '****' order by 单价 desc
for update
open cur
fetch first from cur
update order_detail set 单价=1 where current of cur
fetch last from cur
update order_detail set 单价=2 where current of cur
close cur
deallocate cur
4.创建标量函数,计算指定课程号的课程的平均成绩,然后调用该函数查询某门课的平均成绩。
use stuinfo
create function getavg(@cno char(6))
returns float
as
begin
declare @avgdegree float
select @avgdegree = AVG(degree) from score where cno = @cno
return @avgdegree
end
select dbo.getavg('3-105')
5.创建内联表值函数,查询指定学号的学生的选课情况(包括学号、姓名、课程号和成绩),然后调用该函数查询某位学生的选课情况,再调用该函数将该生某门课的成绩改为100,重新查询该生成绩检查一下改过来了吗。
create function cj(@no char(5))
returns table
as
return (select sname, cno, degree
from student, score
where student.sno = @no and student.sno = score.sno)
go
select * from cj('103') where cno = '3-105'
update cj('103') set degree = 100 where cno = '3-105'
select * from cj('103') where cno = '3-105'
6.创建多语句表值函数,查询指定课程号的学生的选课情况(包括学号、姓名和成绩),并将选了该课的学生成绩提高5分,然后调用该函数查询某门课的学生的选课及成绩修改情况。(之前先查询一下该课的成绩以便与调用函数后的成绩作对比。)
create function student2(@classno char(5))
returns @st table
(
sno char(5),
sname char(8),
cno char(5),
degree int
)
as
begin
insert @st
select student.sno, student.sname, score.cno, score.degree from student,score
where student.sno = score.sno and student.sclass = @classno
update @st set degree = degree + 5
return
end
go
select * from student2('95031')