数据库实验——函数过程

最新推荐文章于 2025-12-30 19:07:24 发布

原创最新推荐文章于 2025-12-30 19:07:24 发布 · 1k 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #算法

一、目的（本次实验所涉及并要求掌握的知识点）

二、实验内容与设计思想（设计思路、主要数据结构、主要代码结构、主要代码段分析）

三、实验使用环境（本次实验所使用的平台和相关软件）

四、实验步骤和调试过程（实验步骤、测试数据设计、测试结果分析）

实验内容

验证性实验

设计性实验

五、实验小结（实验中遇到的问题及解决过程、实验中产生的错误及原因分析、实验体会和收获）

一、目的（本次实验所涉及并要求掌握的知识点）

1.掌握函数&存储过程定义；

2.理解函数&存储过程作用及原理

二、实验内容与设计思想（设计思路、主要数据结构、主要代码结构、主要代码段分析）

实验内容

1．定义函数；

2．调用函数

3. 定义存储过程

4.调用存储过程

三、实验使用环境（本次实验所使用的平台和相关软件）

Win11,Sql server 2022

四、实验步骤和调试过程（实验步骤、测试数据设计、测试结果分析）

实验内容

1．定义函数；

2．调用函数

3. 定义存储过程

4.调用存储过程

验证性实验

（1）.函数

1. 编写函数Fmax，求二数大者；

CREATE FUNCTION Fmax (@x int,@y int)

RETURNS int

BEGIN

DECLARE @Z INT

IF @X>@Y

SET @Z=@X

ELSE

SET @Z=@Y

RETURN(@Z)

END

SELECT DBO.fMAX(1,2)

SELECT fMAX(1,2)

2. 编写函数Fc，参数为两个整数和一个运算符（+，-，*，/），计算其结果。

CREATE FUNCTION FC039(@x int,@y int,@C CHAR)

RETURNS int

BEGIN

DECLARE @Z INT

SET @Z =

CASE @C

WHEN '+' THEN @X+@Y

WHEN '-' THEN @X-@Y

WHEN '*' THEN @X*@Y

WHEN '/' THEN @X/@Y

END

RETURN(@Z)

END

SELECT DBO.fC039(1,2,'+')

3. 编写函数Fs，参数为姓名和课程名，返回该生该课程的成绩；（前提姓名没有重名，若有？）

CREATE FUNCTION FS (@xM NCHAR(4),@KCM VARCHAR(20))

RETURNS DECIMAL(4,1)

BEGIN

DECLARE @Z DECIMAL(4,1)

SELECT @Z=DEGREE FROM STUDENT S JOIN SCORE SC ON SC.SNO=S.SNO

JOIN COURSE C ON C.CNO=SC.CNO WHERE SNAME=@XM AND CNAME=@KCM

RETURN(@Z)

END

SELECT DBO.fS('李君帅', '计算机导论')

CREATE FUNCTION FS039 (@xM NCHAR(4),@KCM VARCHAR(20))

RETURNS DECIMAL(4,1)

BEGIN

DECLARE @Z DECIMAL(4,1)

SELECT @Z=DEGREE FROM STUDENT039 S JOIN SCORE039 SC ON SC.SNO=S.SNO

JOIN COURSE039 C ON C.CNO=SC.CNO WHERE SNAME=@XM AND CNAME=@KCM

RETURN(@Z)

END

SELECT DBO.fS039('李君帅', '计算机导论')

4．编写函数Fsc，参数为姓名，返回该生的所有成绩（姓名，课程名，成绩）；

CREATE FUNCTION Fsc(@XM Nchar(4))

RETURNS TABLE

RETURN (SELECT SNAME,CNAME,DEGREE FROM STUDENT S JOIN SCORE SC ON SC.SNO=S.SNO

JOIN COURSE C ON C.CNO=SC.CNO WHERE SNAME=@XM )

SELECT * FROM DBO.Fsc('李君帅')

SELECT * FROM Fsc('李君帅')

USE STU039

CREATE FUNCTION Fsc039(@XM Nchar(4))

RETURNS TABLE

RETURN (SELECT SNAME,CNAME,DEGREE FROM STUDENT039 S JOIN SCORE039 SC ON SC.SNO=S.SNO

JOIN COURSE039 C ON C.CNO=SC.CNO WHERE SNAME=@XM )

SELECT * FROM DBO.Fsc039('李君帅')

SELECT * FROM Fsc039('李君帅')

5.人员名单(包括学生和教师)

CREATE FUNCTION Flist (@xm nvarchar(5)) --nchar(5) ？？？

RETURNS @list TABLE (xh char(4) ,

xm nchar(4),

lb nchar(2))

begin

if ( @xm is null)

set @xm='%'

insert @list select sno,sname,'学生' from student where sname like '%'+@xm+'%'

insert @list select tno,tname,'教师' from teacher where tname like '%'+@xm+'%'

return

end

select * from flist('李')

select * from flist(null)

Select * from Score sc join flist(null) f on sc.Sno=f.xh

此函数接收一个 nvarchar(5) 类型的参数 @xm，代表要查询的姓名。

若 @xm 为 NULL，则将其设置为 '%'，意味着查询所有姓名。

函数会把符合条件的学生和教师信息插入到 @list 表变量中，最终返回该表变量。

第一条语句查询姓名中包含'李'的学生和教师信息。

第二条语句查询所有学生和教师信息。

第三条语句将 Score039 表和 flist039 函数的结果进行连接查询。

6.统计某一门课的成绩分布情况，即用户输入某一门课程的名称，就可统计出该课程各分数段分布的人数。还要求写出执行存储过程的语句和执行结果。

思路：

1. 先建立一个表Rank用来存放存储过程执行后的结果，其中第一列division显示成绩分段划分，第二列sub_sum显示的是成绩在该分数段的学生人数。

2. 存储过程带有一个字符型参数@name，用于接受，即用户输入某一门课程的，就可统计出该课程各分数段分布的人数。还要求写出执行存储过程的语句和执行结果。用户输入课程名称，便于统计不同课程的分数分布情况。

3. 程序中应该判断是否存在用户输入的课程名称，如果不存在要向用户提示：'输入错误，没有该课程!'。建议利用@@ROWCOUNT来判断。）

CREATE TABLE Rank(

division char(20),

sub_sum int

);

INSERT INTO Rank(division) VALUES('[0，60)'),('[60,70)'),('[70,80)'),('[80,90)'),('[90,100]');

CREATE PROCEDURE printcourse @pcname char(20)

DECLARE @pcno char(20),@pcount int

set nocount on

UPDATE Rank SET sub_sum=0

UPDATE Course SET Cname=@pcname WHERE Cname=@pcname

IF(@@ROWCOUNT=0)

BEGIN

RAISERROR('您输入的课程不存在，请重新输入！', 16, 1)

RETURN

END

SELECT @pcno=score.cno FROM Course,score WHERE Course.Cno=score.Cno AND Course.Cname=@pcname

SELECT @pcount=COUNT(*) FROM score WHERE degree <60 AND Cno=@pcno

UPDATE Rank SET sub_sum=@pcount WHERE division='[0，60)'

SELECT @pcount=COUNT(*) FROM score WHERE degree >=60 AND degree <70 AND Cno=@pcno

UPDATE Rank SET sub_sum=@pcount WHERE division='[60,70)'

SELECT @pcount=COUNT(*) FROM score WHERE degree >=70 AND degree <80 AND Cno=@pcno

UPDATE Rank SET sub_sum=@pcount WHERE division='[70,80)'

SELECT @pcount=COUNT(*) FROM score WHERE degree >=80 AND degree <90 AND Cno=@pcno

UPDATE Rank SET sub_sum=@pcount WHERE division='[80,90)'

SELECT @pcount=COUNT(*) FROM score WHERE degree >=90 AND degree <=100 AND Cno=@pcno

UPDATE Rank SET sub_sum=@pcount WHERE division='[90,100]'

EXEC printcourse '计算机导论'

SELECT * FROM Rank

7.统计某一门课的平均成绩。(建议：存储过程可带有一个字符型参数值，接受用户输入的课程名称，一个输出参数[用output声明]用于存放返回给调用者的这门课程的平均成绩)。还要求写出执行存储过程的语句和执行结果。

执行结果形为：

操作系统的平均成绩为：76.0

CREATE PROCEDURE printavg_course @pcname char(20),@pavg int output

DECLARE @pcno char(20)

SET NOCOUNT ON

UPDATE Course SET Cname=@pcname WHERE Cname=@pcname

IF(@@ROWCOUNT=0)

BEGIN

RAISERROR('您输入的课程不存在，请重新输入！', 16, 1)

RETURN

END

SELECT @pcno=score.cno FROM Course,score WHERE Course.Cno=score.Cno AND Course.Cname=@pcname

SELECT @pavg=AVG(score. degree ) FROM score WHERE Cno=@pcno

PRINT RTrim(@pcname)+'的平均成绩为：'+CAST(@pavg AS char(5))

DECLARE @pavg int

EXEC printavg_course '操作系统',@pavg output

Select @pavg int

CREATE PROCEDURE printavg_course @pcname char(20),@pavg int output

DECLARE @pcno char(20)

SET NOCOUNT ON

UPDATE Course039 SET Cname=@pcname WHERE Cname=@pcname

IF(@@ROWCOUNT=0)

BEGIN

RAISERROR('您输入的课程不存在，请重新输入！', 16, 1)

RETURN

END

SELECT @pcno=score039.cno FROM Course039,score039 WHERE Course039.Cno=score039.Cno AND Course039.Cname=@pcname

SELECT @pavg=AVG(score039. degree ) FROM score039 WHERE Cno=@pcno

PRINT RTrim(@pcname)+'的平均成绩为：'+CAST(@pavg AS char(5))

DECLARE @pavg int

EXEC printavg_course '操作系统',@pavg output

Select @pavg int

8.将学生选课成绩从百分制改为等级制（即A、B、C、D、E五级）。

CREATE PROCEDURE printclass_degree

SELECT Sno,Cno, degree ,

CASE

WHEN degree <60 THEN '不及格'

WHEN degree >=60 AND degree <70 THEN '及格'

WHEN degree >=70 AND degree <80 THEN '中'

WHEN degree >=80 AND degree <90 THEN '良'

WHEN degree >=90 AND degree <=100 THEN '优'

ELSE '成绩为空！'

END AS ' degree Classified'

FROM score

EXEC printclass_degree

设计性实验

1．实验要求

1.编写函数FsumXXX，1~n（参数）求和；

CREATE FUNCTION FSUM039 (@X INT,@Y INT)

RETURNS INT

BEGIN

DECLARE @S INT

SET @S=0

WHILE @X<=@Y

BEGIN

SET @S=@S+@X

SET @X=@X+1

END

RETURN @S

END

SELECT DBO.FSUM039(1,100）

2.编写函数FRANKXXX,参数为学号，返回该生平均分班级排名；

CREATE FUNCTION FRANK039 (@sno CHAR(4))

RETURNS INT

BEGIN

DECLARE @rank INT;

-- 使用 CTE 计算每个学生的平均分和班级排名

WITH ClassAverage AS (

SELECT

s.CLASS,

sc.SNO,

AVG(sc.DEGREE) AS AvgDegree,

RANK() OVER (PARTITION BY s.CLASS ORDER BY AVG(sc.DEGREE) DESC) AS ClassRank

FROM

STUDENT039 s

JOIN SCORE039 sc ON s.SNO = sc.SNO

GROUP BY

s.CLASS, sc.SNO

)

-- 从 CTE 中获取指定学号学生的班级排名

SELECT @rank = ClassRank

FROM

ClassAverage

WHERE

SNO = @sno;

RETURN @rank;

END;

SELECT dbo.FRANK039('109');

3.编写函数FCJAXXX，参数为姓名或姓名一部分，返回该生的所有课程的成绩（学号，姓名，课程名，成绩等级）；

选修成绩等级 A:90～100 B:80~90 C: 70~80 D:60~70 E:<60

CREATE FUNCTION FCJA039 (@SNAME NCHAR(4))

RETURNS TABLE

RETURN

(SELECT S.SNO,SNAME,CNAME,

CASE

WHEN DEGREE>=90 AND DEGREE<100 THEN 'A'

WHEN DEGREE>=80 AND DEGREE<90 THEN 'B'

WHEN DEGREE>=70 AND DEGREE<80 THEN 'C'

WHEN DEGREE>=60 AND DEGREE<70 THEN 'D'

WHEN DEGREE<60 THEN 'E'

ELSE '无成绩'

END AS GRADE_RANK

FROM STUDENT039 S JOIN SCORE039 SC ON SC.SNO=S.SNO JOIN COURSE039 C ON C.CNO=SC.CNO

WHERE SNAME LIKE '%' + @SNAME + '%' )

4.编写函数FclassXXX,参数为班级，返回该班男、女生平均分前二名（学号，姓名，课程名，成绩，平均）

CREATE FUNCTION Fclass039 (@class NVARCHAR(50))

RETURNS TABLE

RETURN

(

-- 使用 CTE 计算每个学生的平均分和排名

WITH StudentAverages AS (

SELECT

s.SNO,

s.SNAME,

s.SSEX,

c.CNAME,

sc.DEGREE,

AVG(sc.DEGREE) OVER (PARTITION BY s.SNO) AS AvgDegree,

ROW_NUMBER() OVER (PARTITION BY s.SEX ORDER BY AVG(sc.DEGREE) DESC) AS Rank

FROM

STUDENT039 s JOIN SCORE039 sc ON s.SNO = sc.SNO JOIN COURSE039 c ON sc.CNO = c.CNO

WHERE

s.CLASS = @class

)

-- 从 CTE 中筛选出男、女生排名前二的学生

SELECT

SNO,

SNAME,

CNAME,

DEGREE,

AvgDegree

FROM

StudentAverages

WHERE

Rank <= 2

);

2..思考题

（1）什么标量函数和表值函数？

标量函数返回一个标量（单值）结果，表值函数返回一个用SQL语句建立的表，类似于存储过程。

（2）函数定义和调用应注意哪些事项？

函数定义的注意事项：

权限：用户需要有创建函数的权限，通常数据库管理员可以授予相应的权限。

语法规范：要遵循所使用数据库系统的语法规则。例如在 SQL Server 中，函数的参数需要指定数据类型，返回值也需要明确数据类型。

函数体逻辑：确保函数体中的逻辑正确，特别是涉及到复杂的计算或查询时，需要进行测试以验证结果的准确性。

避免副作用：一般情况下，函数应该是确定性的，即对于相同的输入参数，每次调用函数都应返回相同的结果。避免在函数中执行会产生副作用的操作，如修改数据库中的数据（特殊情况如自定义函数用于计算并更新某些统计数据除外，但这种情况需要谨慎处理）。

错误处理：可以在函数中添加适当的错误处理代码，例如使用 TRY...CATCH 块捕获并处理可能出现的错误，提高函数的健壮性。

函数调用的注意事项：

架构引用：如果函数属于特定的架构，在调用时需要正确引用架构名，例如在 SQL Server 中 dbo.FunctionName 。

参数传递：确保传递给函数的参数数据类型与函数定义中的参数数据类型匹配，并且参数的数量和顺序正确。

函数返回值处理：根据函数的返回类型正确处理返回值。对于标量函数，可以将其结果用于表达式中；对于表值函数，可以将其作为 FROM 子句的数据源进行查询。

（3）定义存储过程有哪些选项？分别表示什么意思

参数：存储过程可以接受输入参数、输出参数或输入输出参数。

输入参数：用于向存储过程传递数据，存储过程可以使用这些参数进行处理。例如：CREATE PROCEDURE GetEmployeeByID @employeeID int AS... ，@employeeID 就是一个输入参数。

输出参数：用于从存储过程返回数据给调用者。需要在定义存储过程时指定参数为 OUTPUT 类型。例如：

输入输出参数：既可以作为输入传递数据给存储过程，又可以在存储过程中被修改并作为输出返回数据。

WITH 子句：

WITH ENCRYPTION：对存储过程的定义进行加密，防止他人查看存储过程的源代码。例如：CREATE PROCEDURE MyProcedure WITH ENCRYPTION AS... 。

WITH RECOMPILE：每次执行存储过程时都重新编译存储过程，适用于存储过程中使用的参数值具有较大差异，希望每次都能根据最新的统计信息生成最优执行计划的情况。

AS 子句：用于指定存储过程的主体部分，即存储过程执行的 SQL 语句。例如：CREATE PROCEDURE MyProcedure AS SELECT * FROM Employees; ，SELECT * FROM Employees 就是存储过程的主体。

五、实验小结（实验中遇到的问题及解决过程、实验中产生的错误及原因分析、实验体会和收获）

在进行CREATE FUNCTION FS039 (@xM NCHAR(4),@KCM VARCHAR(20))这个语句时，出现了以下提示：

这个错误提示意味着在 SQL Server 里，CREATE FUNCTION 语句要求单独处于一个批处理中，不能和其他语句在同一个批处理里混合使用。解决办法如下：在 CREATE FUNCTION 语句前后加上 GO 关键字，GO 用于将 SQL 语句划分成不同的批处理，让数据库知晓这是一个独立的代码块。