当前位置：首页 > news >正文

数据库实验10 函数存储

news 2025/5/7 6:54:57

数据库实验10

一、实验目的

掌握函数和存储过程的定义方法，包括标量函数、表值函数、存储过程的语法结构。
理解函数和存储过程的作用及原理，区分标量函数与表值函数的应用场景，掌握存储过程的参数传递、逻辑控制和错误处理机制。
能够熟练运用函数和存储过程实现数据查询、统计分析、业务逻辑处理等操作，如成绩计算、选课管理等。

二、实验内容与设计思想

（一）验证性实验

1. 函数定义与调用

函数Fmax：定义标量函数，接收两个整数参数，返回较大值。调用时需指定架构名dbo，否则会因无法识别函数名称报错。

SELECT DBO.fMAX(1,2)

SELECT fMAX(1,2) 使用该语句会报错，如下图所示：

函数Fc：通过CASE语句实现四则运算，根据输入的运算符返回计算结果，处理整数运算逻辑。

go
CREATE FUNCTION FC  (@x int,@y int,@C CHAR)
RETURNS int
AS
BEGIN
DECLARE @Z INT
SET  @Z = CASE @CWHEN '+' THEN @X+@YWHEN '-' THEN @X-@YWHEN '*' THEN @X*@YWHEN '/' THEN @X/@YENDRETURN(@Z)
END
go
SELECT DBO.fC(1,2,'+')

在这里插入图片描述

函数Fs：基于多表连接（STUDENT、SCORE、COURSE），根据姓名和课程名查询成绩，假设姓名唯一，若存在重名需增加学号等唯一标识参数。

CREATE FUNCTION FS  (@xM NCHAR(4),@KCM VARCHAR(20))
RETURNS DECIMAL(4,1)
AS
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=@KCMRETURN(@Z)
ENDSELECT DBO.fS('李君帅',	'计算机导论')

在这里插入图片描述

函数Fsc：返回表值函数，查询指定姓名学生的所有课程成绩，直接返回结果集，简化多表连接查询。

CREATE FUNCTION Fsc(@XM Nchar(4))
RETURNS TABLE
AS
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('李君帅')

在这里插入图片描述

函数Flist：联合查询学生和教师名单，支持模糊查询，通过INSERT INTO @list分两次插入学生和教师数据，处理NULL参数时默认查询所有人员。

CREATE FUNCTION Flist (@xm nvarchar(5))   --nchar(5) ？？？
RETURNS @list TABLE (xh  char(4) ,xm nchar(4),lb nchar(2))asbeginif ( @xm is null)set @xm='%'  insert @list   select sno,sname,'学生' from student057 where sname like '%'+@xm+'%'insert @list   select tno,tname,'教师' from teacher057 where tname like '%'+@xm+'%'returnend
goselect * from flist('李')

在这里插入图片描述

select * from flist(null)

在这里插入图片描述

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

在这里插入图片描述

2. 存储过程定义与调用

存储过程printcourse：统计课程成绩分布，先创建Rank表存储分数段，通过UPDATE和SELECT语句更新各分数段人数，利用@@ROWCOUNT判断课程是否存在，处理异常输入。
存储过程printavg_course：计算课程平均成绩，使用输出参数返回平均值，通过PRINT语句输出结果，处理课程不存在的错误提示。
存储过程printclass_degree：将成绩转换为等级制，通过CASE语句实现百分制到等级的映射，直接返回分类结果。

（二）设计性实验（学号后三位057）

1. 函数Fsum057

功能：计算1到n的和。
代码：

go
CREATE FUNCTION Fsum057(@n INT)
RETURNS INT
AS
BEGINDECLARE @sum INT = 0, @i INT = 1WHILE @i <= @nBEGINSET @sum = @sum + @iSET @i = @i + 1ENDRETURN @sum
END
go
select DBO.Fsum057(10)

在这里插入图片描述

2. 函数FCJA057

功能：根据姓名或部分姓名，返回学生的课程成绩及等级。
代码：

go
CREATE FUNCTION FCJA057(@xm NVARCHAR(10))
RETURNS TABLE
AS
RETURN (SELECT S.SNO, S.SNAME, C.CNAME, SC.DEGREE,CASE WHEN SC.DEGREE >= 90 THEN 'A'WHEN SC.DEGREE >= 80 THEN 'B'WHEN SC.DEGREE >= 70 THEN 'C'WHEN SC.DEGREE >= 60 THEN 'D'ELSE 'E'END AS 成绩等级FROM STUDENT057 SJOIN SCORE057 SC ON S.SNO = SC.SNOJOIN COURSE057 C ON SC.CNO = C.CNOWHERE S.SNAME LIKE '%' + @xm + '%'
)
GOselect * from FCJA057('李')

在这里插入图片描述

3. 函数Fclass057

功能：返回指定班级男、女生平均分前二名学生的课程成绩及平均分。
设计思想：先计算每个学生的平均分，按性别和平均分排序，取前两名。
代码：

go
CREATE FUNCTION Fclass057(@class NVARCHAR(20))
RETURNS TABLE
AS
RETURN (WITH StudentAvg AS (SELECT S.SNO, S.SNAME, S.SEX, AVG(SC.DEGREE) AS 平均分FROM STUDENT057 SJOIN SCORE057 SC ON S.SNO = SC.SNOWHERE S.CLASS = @classGROUP BY S.SNO, S.SNAME, S.SEX)SELECT sa.SNO, sa.SNAME, sa.SEX, c.CNAME, sc.DEGREE, sa.平均分FROM StudentAvg saJOIN SCORE057 sc ON sa.SNO = sc.SNOJOIN COURSE057 c ON sc.CNO = c.CNOWHERE (SELECT COUNT(DISTINCT sa2.SNO) FROM StudentAvg sa2 WHERE sa2.SEX = sa.SEX AND sa2.平均分 > sa.平均分) < 2
)
GO

4. 修改表course057

-- 添加列并设置默认值
ALTER TABLE COURSE057
ADD mn int DEFAULT 50,cn int DEFAULT 0;-- 更新已有行的 mn 列值为默认值 50
UPDATE COURSE057
SET mn = 50
WHERE mn IS NULL;-- 更新已有行的 cn 列值为默认值 0
UPDATE COURSE057
SET cn = 0
WHERE cn IS NULL;

在这里插入图片描述

三、实验环境

操作系统：Windows 11
数据库管理系统：SQL Server 2022
开发工具：SQL Server Management Studio (SSMS)

四、实验步骤与调试过程

（一）验证性实验

函数测试
- Fmax：调用SELECT DBO.Fmax(1,2)返回2，验证条件判断逻辑正确；直接调用Fmax(1,2)报错，确认需指定架构名dbo。
- Fc：测试不同运算符，如SELECT DBO.Fc(5,2,'/')返回2（整数除法），验证CASE语句分支正确。
- Fs：输入存在的姓名和课程名，如SELECT DBO.Fs('李君帅','计算机导论')返回64.0，验证多表连接查询正确。
存储过程测试
- printcourse：执行EXEC printcourse '计算机导论'，查询Rank表，确认各分数段人数统计正确，测试不存在的课程时提示错误。
- printavg_course：声明输出参数DECLARE @pavg INT，执行后通过SELECT @pavg查看平均值，验证输出参数传递正确。

（二）设计性实验

函数Fsum057：调用SELECT DBO.Fsum057(10)返回55，验证循环求和逻辑正确。
选课存储过程cs057：插入测试数据，模拟选课人数满员、已选课程等场景，验证事务处理和错误提示机制。

（三）调试问题与解决

错误1：调用用户定义函数时未加dbo.，报错“不是可以识别的内置函数名称”。
解决：明确用户定义函数需指定架构名，或通过SET SCHEMA设置默认架构。
错误2：存储过程中表名拼写错误（如Course写成Cource），导致语法错误。
解决：通过SSMS的语法检查和调试功能，逐行排查表名和列名拼写。

五、思考题

（一）标量函数和表值函数

标量函数：返回单个数据值（如INT、CHAR），通过RETURNS指定单一类型，使用BEGIN...END包含逻辑，适合简单计算或单行查询。
表值函数：返回表结果集，分为内联表值函数（直接RETURN SELECT语句，无BEGIN...END）和多语句表值函数（使用@table变量构建结果集），适合复杂查询或替代视图。

（二）函数定义和调用注意事项

定义
- 标量函数必须包含RETURN语句返回单个值，表值函数返回表变量或查询结果。
- 避免在函数中修改数据库状态（如UPDATE、INSERT），函数设计为只读操作。
- 参数需指定数据类型，默认不允许NULL，除非显式声明NULL允许。
调用
- 标量函数在SELECT语句中调用，需指定架构名（如dbo.函数名）。
- 表值函数调用时需像表一样使用，如SELECT * FROM 函数名(参数)。

（三）存储过程选项

SET NOCOUNT ON：禁止返回“受影响的行数”消息，减少网络传输开销，提高性能。
WITH RECOMPILE：每次执行时重新编译存储过程，适用于参数分布变化较大的场景，但会增加编译开销。
OUTPUT参数：用于返回多个值或计算结果，需在调用时声明变量并使用OUTPUT关键字。
错误处理：通过TRY...CATCH块或@@ERROR、RAISERROR捕获和处理异常，增强存储过程的健壮性。

六、实验小结

（一）遇到的问题及解决

中英文符号混淆：编写代码时误用中文逗号、括号，导致语法错误。通过IDE的语法高亮和错误提示，逐行检查符号格式。
多表连接逻辑错误：在函数Fs中，因未正确关联表字段（如SNO拼写错误），导致查询结果为空。通过分步调试，先验证单表查询，再逐步添加连接条件。
存储过程事务处理：选课存储过程中未考虑并发选课场景，可能导致人数统计不一致。后续可添加事务（BEGIN TRANSACTION）和锁机制（如UPDLOCK）确保数据一致性。