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

实验八：存储过程和用户自定义函数

实验内容与完成情况(记录所有的实验过程)： 1、对学生课程数据库，编写并执行三个存储过程，分别完成下面功能： 1）统计某一门课的成绩分布情况，即用户输入某一门课程的名称，就可统计出该课程各分数段分布的人数。还要求写出执行存储过程的语句和执行结果。 （答题参考：1. 先建立一个表Rank用来存放存储过程执行后的结果，其中第一列division显示成绩分段划分，第二列sub_sum显示的是成绩在该分数段的学生人数。                       2. 存储过程带有一个字符型参数@name，用于接受，即用户输入某一门课程的名称，就可统计出该课程各分数段分布的人数。                       3. 程序中应该判断是否存在用户输入的课程名称，如果不存在要向用户提示：'输入错误，没有该课程!'。建议利用@@ROWCOUNT来判断。） CREATE TABLE Rank( division CHAR(20), sub_sum INT);） select * from rank   --存储过程执行完后，rank表中存放的就是这门课程的成绩分布情况 division             sub_sum     -------------------- ----------- [0,60)               0 [60,70)              0 [70,80)              0 [80,90)              1 [90,100]             1                 CREATE TABLE Rank(division CHAR(20), sub_sum INT); insert Rank(division, sub_sum)Values('[0-60)',null) insert Rank(division, sub_sum)Values('[60-70)',null) insert Rank(division, sub_sum)Values('[70-80)',null) insert Rank(division, sub_sum)Values('[80-90)',null) insert Rank(division, sub_sum)Values('[90-100]',null) select * from rank      create procedure coursename @name char(20) as   declare @cno char(20),@count int select *from course where cname=@name if @@ROWCOUNT<0 begin         print('输入错误，没有该课程!')         return end select @cno = sc.cno  from course,sc  where course.cno = sc.cno and course.cname= @name select @count = count(*) from sc where sc.grade<60 and sc.cno=@cno update Rank set sub_sum =@count where division='[0-60)' select @count = count(*) from sc where sc.grade>=60 and sc.grade<70 and sc.cno=@cno update Rank set sub_sum =@count where division='[60-70)' select @count = count(*) from sc where sc.grade>=70 and sc.grade<80 and sc.cno=@cno update Rank set sub_sum =@count where division='[70-80)' select @count = count(*) from sc where sc.grade>=80 and sc.grade<90 and sc.cno=@cno update Rank set sub_sum =@count where division='[80-90)' select @count = count(*) from sc where sc.grade>=90and sc.grade<=100 and sc.cno=@cno update Rank set sub_sum =@count where division='[90-100]' go   2）统计某一门课的平均成绩，要求写出执行存储过程的语句和执行结果。(建议：存储过程可带有一个字符型参数值，接受用户输入的课程名称，一个输出参数[用output声明]用于存放返回给调用者的这门课程的平均成绩)。 执行结果形为： 数学课的平均成绩为：82 create procedure coursename2 @cname char(20) output as declare @avg float select @avg = avg(sc.grade)  from course,sc  where course.cno = sc.cno and course.cname=@cname  group by sc.cno,course.cno  print(RTRIM(@cname)+'的平均成绩为：'+CAST(@avg as varchar)) go exec coursename2 '数学 '   3）*附加题：将学生选课成绩从百分制改为等级制（即'优'、'良'、'中'、'及格'、'不及格'五级）。 执行结果形如： sno       cno  Grade Classified   grade --------- ---- ---------------- ---------- 200215121 4    成绩为空!            NULL 200215121 6    成绩为空!            NULL 200215121 7    成绩为空!            NULL 200215122 5    成绩为空!            NULL 200215125 1    成绩为空!            NULL 200215126 6    不及格               55 200215122 2    良                   80 200215121 2    良                   85 200215121 3    良                   88 200215121 1    优                   92 200215122 1    优                   92 create procedure coursename3 as   select sno,cno, case    when grade<60 then '不及格'    when grade>=60 and grade<70 then '及格'    when grade>=70 and grade<80 then '中'    when grade>=80 and grade<90 then '良'    when grade>=90and grade<=100 then '优'    else '成绩为空' end as 'Grade Classified',grade from sc go exec coursename3 2、对SPJ数据库，创建一个存储过程ins_s_count，功能为根据提供的供应商号，供应商名，供应商所在地等信息，往S表中插入数据，并返回插入该记录之后，S表中的记录数。调用该存储过程实现往S表中插入一条记录(‘S6’,’天盛’,‘福州’)，并显示插入该记录之后，S表中的记录数。 create procedure insert_allcount @sno char(10),@sname char(10), @status int , @city char ( 10 ), @count int output --设置@count为output，即为输出型的参数 as insert into s (sno,sname,status,city) values (@sno,@sname,@status ,@city) select @count = count(*)from s go --执行完这个存储过程之后，@count将作为该存储过程的返回值被返回。 declare @count int exec insert_allcount 'S6','天盛 ',20,'福州',@count output print ( 'S表中的记录数为：'+CAST(@count as varchar)) 3、查看存储过程的定义和内容。 SELECT name, definition FROM sys.sql_modules AS m INNER JOIN sys.all_objects AS o ON m.object_id = o.object_id WHERE o.[type] = 'P' 4、创建一个返回标量值的用户定义函数 RectangleArea输入矩形的长和宽就能计算矩形的面积。自选2种实例调用该函数。 create function RectangleArea(@length float,@width float) returns float begin         return @length*@width end go ---1. select dbo.RectangleArea(3.2,4) '面积' ---2. declare @area float execute @area=RectangleArea 3.3,4.5 print '矩形面积为：'+CAST(@area as varchar) 5、创建一个用户自定义函数（内嵌表值函数），功能为产生某个系的学生选修信息，内容为学号，姓名，课程名，成绩。调用这个函数，显示信息系有选课学生的信息。 create function stu_course_choose(@sdept varchar(20)) returns table return ( select student.Sno 学号,student.sname 姓名,course.cname 课程名,sc.grade 成绩 from student,sc,course where student.sdept=@sdept and student.sno=sc.sno and course.cno=sc.cno ) go select * from stu_course_choose('cs'); 6、创建一个用户自定义函数(多语句表值函数)，功能为产生一张有关学生成绩统计的报表。该报表显示每一门课程的课程号、课程名、选修人数、本门最高分、最低分和平均分。调用这个函数，生成相应的报表并给用户浏览。 create function stu_grade() returns table return ( select sc.cno 课程号,course.cname 课程名,count(*) 选修人数,max(grade) 最高分,min(grade) 最低分,avg(grade) 平均分 from sc,course where sc.cno=course.cno group by sc.cno,course.cname ) go select * from stu_grade();

自定义函数 1. 标量值函数 create function 函数名（参数）  returns 返回值数据类型           [with {Encryption | Schemabinding }]           [as]           begin                   SQL语句(必须有return 变量或值)           End 2. 内嵌表值函数 create function 函数名（参数）          returns table           [with{ Encryption | Schemabinding }]           as           return(一条SQL语句) 3.group by group by语句中select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中（计算平均值，总和等等） 4.输出函数执行结果 ---1. select dbo.函数名 (参数,参数…..)  'xxx ' ---2. declare @xxx 类型 execute @xxx=函数名 参数，参数….. print 'xxxxx '+CAST(@xxx as varchar) ---3. select *from 函数名 (参数,参数…..);