存储过程与用户自定义函数

实验报告
课程名称：数据库系统概论实验时间：2012.5.10
学号：姓名：班级：
一、实验题目：存储过程与用户自定义函数
二、实验目的：
1）掌握SQLServer中存储过程的使用方法。

2）掌握SQLServer中用户自定义函数的使用方法。

三、实验内容：（记录每个实验步骤内容、命令、截屏结果）
(一)存储过程
1、对学生课程数据库，编写2个存储过程，分别完成下面功能：
1）统计某一门课的成绩分布情况，即按照各分数段统计人数，要求使用游标。

create proc TotalByCnoNum
(
@cno varchar(6)
)
as
begin
declare @num1 int,@num2 int, @num3 int,@num4 int,@num5 int,@grade
int,@cname char(20)
select @num1=0,@num2=0,@num3=0,@num4=0,@num5=0
declare cur_cno cursor for select grade from sc where cno=@cno
open cur_cno
fetch next from cur_cno into @grade
while@@fetch_status=0
begin
if @grade between 90 and 100
set @num1=@num1+1
else if @grade between 80 and 89
set @num2=@num2+1
else if @grade between 70 and 79
set @num3=@num3+1
else if @grade between 60 and 69
set @num4=@num4+1
else
set @num5=@num5+1
fetch next from cur_cno into @grade
end
close cur_cno
deallocate cur_cno
select @cname=cname from course where cno=@cno
print'课程：'+@cname
print'分数段人数统计'
print'=========================='
print' 90-100 : '+convert(varchar(3),@num1)
print' 80-89 : '+convert(varchar(3),@num2)
print' 70-79 : '+convert(varchar(3),@num3)
print' 60-69 : '+convert(varchar(3),@num4)
print' 不及格: '+convert(varchar(3),@num5)
print'=========================='
end
执行以下语句，显示课程号为3的成绩情况：
exec TotalByCnoNum '3'
运行结果如下：
2）将学生选课成绩从百分制改为等级制（即A、B、C、D、E五级）。

create proc ChangeGrade
as
begin
declare @dj char(1),@cname char(20),@cno char(6),@sno char(9), @grade int
declare cur_cno cursor for select grade,cno,sno from sc
open cur_cno
fetch next from cur_cno into @grade,@cno,@sno
print'学号课程号等级'
print'=========================='
while@@fetch_status=0
begin
if @grade between 90 and 100
set @dj='A'
else if @grade between 80 and 89
set @dj='B'
else if @grade between 70 and 79
set @dj='C'
else if @grade between 60 and 69
set @dj='D'
else
set @dj='E'
print @sno+' '+@cno+' '+@dj
print'--------------------------'
fetch next from cur_cno into @grade,@cno,@sno
end
print'========================='
close cur_cno
deallocate cur_cno
end
执行：
exec ChangeGrade
运行结果为：
2、对SPJ数据库，
1）创建一个存储过程ins_s_count，功能为根据提供的供应商号，供应商名，供应商所在地等信息，往S表中插入数据，并返回插入该记录之后，S表中的记录数。

create proc ins_s_count
(
@sno char(6),
@sname char(20),
@status char(10),
@city char(20)
)
as
begin
declare @num int
i nsert into s (sno,sname,status,city)values(@sno,@sname,@status,@city)
print'你添加的记录是：'
print'=================================================='
print'供应商号供应商名状态供应商所在地'
print' '+@sno+@sname+@status+@city
select @num=count(*)from s
print''
print'=================================================='
print'共有'+convert(varchar,@num)+'条记录'
end
在s表中添加如下数据：
exec ins_s_count 'S7','一建','120','杭州'
结果为：
2）调用该存储过程实现往S表中插入一条记录(‘S6’,’天盛’,‘40’‘福州’)，并显示
插入该记录之后，S表中的记录数。

数据库脚本如第（1）题：
exec ins_s_count 'S6','天盛','40','福州'
(二)用户自定义函数
1.创建一个返回标量值的用户定义函数RectangleArea：输入矩形的长和宽就能计算矩形的面积。

调用该函数。

create function RectangleArea
(
@width int,
@length int
)
returns int
as
begin
return@width*@length
end
测试语句：
select dbo.RectangleArea (5,7)'面积'
2.创建一个用户自定义函数，功能为产生一张有关学生成绩统计的报表。

该报表
显示每一门课程的课程号、课程名、选修人数、本门最高分、最低分和平均分。

调用这个函数，生成相应的报表并给用户浏览。

create function totalC()
returns table
as
return
(
select o,ame,aa.num,aa.maxgrade,aa.mingrade,aa.avggrade from (
select cno,count(*)'num',max(grade)'maxgrade',min(grade)'mingrade',avg(grade) 'avggrade'
from sc group by cno
)aa,course
where o=o
)
执行：
select*from dbo.totalC()
四、实验报告书写要求
实验内容的脚本。

五、实验总结
本次实验主要巩固了存储过程的使用方法和用户自定义函数的使用方法，尤其是对游标的使用。