大型数据库期末试题以及答案(4)

修改：

alter function 名称

([{@参数名称 参数类型[=默认值]}[,n]]) returns @局部变量 table [with encryption] [as] begin 函数体

return 函数返回值 end

删除：drop function 函数名

一、创建标量用户定义函数

1、在学生信息管理系统中，学生信息表包含列“学号”、列“姓名”；课程信息表包含列“课程代号”、“课程名称”；学生成绩表包含列“学号”、列“课程代号”、列“成绩”，已用约束保证成绩的范围为0~100分。

编写标量函数fn_StudentScore，要求：输入一个学号和一个课程号，返回这个学生这门课的成绩，如果此学生没有选这门课，返回-1。

CREATE FUNCTION fn_StudentScore (@stu_id char(8) ,@course_id char(3)) returns int as begin

declare @a int

set @a=(select score from score

where stu_id=@stu_id and course_id=@course_id)

if @a is null begin

set @a=-1

end return @a end

2、测试函数fn_StudentScore（选两个例子，一个显示成绩，一个显示-1）

declare @score int

exec @score = fn_StudentScore @stu_id='0601009' ,@course_id='009' select @score as 成绩

declare @score int

exec @score = fn_StudentScore @stu_id='0601002' ,@course_id='009' select @score as成绩

3、在学生信息管理系统中，学生信息表包含列“学号”、列“姓名”；课程信

息表包含列“课程代号”、“课程名称”；学生成绩表包含列“学号”、列“课程代号”、列“成绩”，已用约束保证成绩的范围为0~100分。

编写用户定义函数fn_EvaluateOneStudent，要求：每次输入一个学号，计算该学生的所有课程的平均分，如果是85~100分，返回“优”；如果是75~84分，返回“良”；如果是65~74分，返回“中”；如果是0~64分，返回“差”。

CREATE FUNCTION fn_EvaluateOneStudent (@学号 char(8))

RETURNS varchar(10) AS

BEGIN

DECLARE @平均分 integer, @等级 varchar(10)

SELECT @平均分=AVG(score)FROM score WHERE stu_id=@学号 IF @平均分 BETWEEN 85 AND 100 SET @等级='优' ELSE

IF @平均分 BETWEEN 75 AND 84 SET @等级='良'

ELSE IF @平均分 BETWEEN 65 AND 74 SET @等级='中'

ELSE IF @平均分 BETWEEN 0 AND 64 SET @等级='差' RETURN @等级

END

4、测试函数fn_EvaluateOneStudent

例如：判断学号为0601002、0601006、0601009的同学的等级。

declare @等级 varchar(10),@学号 char(8) set @学号= '0601002'

exec @等级= fn_EvaluateOneStudent @学号=@学号 select @学号 as 学号,@等级 as 等级

二、创建多语句表值函数

1、在学生信息管理系统中，学生信息表包含列“学号”、列“姓名”；课程信息表包含列“课程代号”、“课程名称”；学生成绩表包含列“学号”、列“课程代号”、列“成绩”，已用约束保证成绩的范围为0~100分。

编写多语句表值函数fn_SexStudent，要求：当性别是“男”，返回男生表，性别是“女”，返回女生表，否则显示所有学生信息。 CREATE FUNCTION fn_SexStudent (@性别 varchar(8))

RETURNS @sexstudent table

( stu_id char(8) primary key,

stu_name char(10), stu_sex char(2),

stu_birthday smalldatetime

)

AS begin

if @性别='男'

insert @sexstudent select * from student where stu_sex='男'

else if @性别='女'

insert @sexstudent select * from student where stu_sex='女'

else

insert @sexstudent select * from student

return end

2、测试函数fn_SexStudent

select * from dbo.fn_SexStudent('女') select * from dbo.fn_SexStudent('男') select * from dbo.fn_SexStudent('全部')

三、创建内嵌表值函数

1、在学生信息管理系统中，学生信息表包含列“学号”、列“姓名”；课程信息表包含列“课程代号”、“课程名称”；学生成绩表包含列“学号”、列“课程代号”、列“成绩”，已用约束保证成绩的范围为0~100分。

编写内嵌表值函数fn_PassStudent，要求：每次输入一个课程号，返回及格（大于等于60分）的学生学号，姓名，本课程的名称和分数。 CREATE FUNCTION fn_PassStudent (@课程号 varchar(8))

RETURNS TABLE AS

RETURN (

SELECT a. stu_id,stu_name ,course_name, score FROM student AS a

INNER JOIN score AS b

ON a. stu_id =b. stu_id

INNER JOIN course AS c

ON b. course_id =c. course_id

WHERE b. course_id= @课程号 and score>=60 )

2、测试函数fn_PassStudent

select * from fn_PassStudent('005')

控制流：http://www.cnblogs.com/kissdodog/archive/2013/03/10/2953232.html 1、