数据库实验10

设计性实验

1．实验要求

1.编写函数FsumXXX，1~n（参数）求和；
GO
CREATE FUNCTION Fsum065 (@n INT)
RETURNS INT
AS
BEGIN
DECLARE @sum INT = 0
WHILE @n > 0
BEGIN
SET @sum = @sum + @n
SET @n = @n - 1
END
RETURN @sum
END
GO

– 调用
SELECT DBO.Fsum065(100) AS TotalSum;

2.编写函数FRANKXXX,参数为学号，返回该生平均分班级排名；
– 调用
SELECT DBO.Fsum065(100) AS TotalSum;

GO
CREATE FUNCTION FRANK065 (@sno CHAR(3))
RETURNS INT
AS
BEGIN
DECLARE @rank INT

SELECT @rank = rnk
FROM (SELECT Sno, RANK() OVER (ORDER BY AVG(Degree) DESC) AS rnkFROM Score065GROUP BY Sno
) AS RankedStudents
WHERE Sno = @snoRETURN @rank

END
GO

– 调用
SELECT DBO.FRANK065(‘S001’) AS Rank;

3.编写函数FCJAXXX，参数为姓名或姓名一部分，返回该生的所有课程的成绩（学号，姓名，课程名，成绩等级）；
选修成绩等级 A:90～100 B:80~90 C: 70~80 D:60~70 E:<60
GO
CREATE FUNCTION FCJA065 (@name NVARCHAR(10))
RETURNS TABLE
AS
RETURN (
SELECT
S.Sno,
S.Sname,
C.Cname,
SC.Degree,
CASE
WHEN SC.Degree < 60 THEN ‘E’
WHEN SC.Degree BETWEEN 60 AND 69 THEN ‘D’
WHEN SC.Degree BETWEEN 70 AND 79 THEN ‘C’
WHEN SC.Degree BETWEEN 80 AND 89 THEN ‘B’
WHEN SC.Degree BETWEEN 90 AND 100 THEN ‘A’
END AS GradeLevel
FROM student065 S
JOIN Score065 SC ON S.Sno = SC.Sno
JOIN Course065 C ON SC.Cno = C.Cno
WHERE S.Sname LIKE ‘%’ + @name + ‘%’
)
GO

– 调用
SELECT * FROM DBO.FCJA065(‘曾华庆’);

4.编写函数FclassXXX,参数为班级，返回该班男、女生平均分前二名 （学号，姓名，课程名，成绩，平均）
GO
CREATE FUNCTION Fclass065 (@classname CHAR(5))
RETURNS TABLE
AS
RETURN (
SELECT *
FROM (
SELECT
S.Sno,
S.Sname,
C.Cname,
SC.Degree,
AVG(SC.Degree) OVER(PARTITION BY S.Sno) AS AvgScore,
ROW_NUMBER() OVER(PARTITION BY S.Ssex ORDER BY AVG(SC.Degree) DESC) AS rn
FROM student065 S
JOIN Score065 SC ON S.Sno = SC.Sno
JOIN Course065 C ON SC.Cno = C.Cno
WHERE S.Class = @classname
GROUP BY S.Sno, S.Sname, S.Ssex, C.Cname, SC.Degree
) AS Ranked
WHERE rn <= 2
)
GO

– 调用
SELECT * FROM DBO.Fclass065(‘软件工程1班’);

5. 修改表courseXXX ，增加两列，课程选修最大人数（mn ，默认50）和当前人数（cn ，默认0）；
   ALTER TABLE Course065 ADD mn INT DEFAULT 50, cn INT DEFAULT 0;
   6.选课存储过程csXXX：
   –某学生没有选过某课程，要选某课程时，当没有超过课程最大人数时，可以选择该课程，当前选课人数加1， 并在成绩score表中增加对应的该生该课程的成绩为0记录
   –否则，提示该课程选课人数已满！
   EXEC csXXX 学号 , 课程号
   – 调用
   SELECT * FROM DBO.Fclass065(‘软件工程1班’);

ALTER TABLE Course065
ADD mn INT DEFAULT 50,
cn INT DEFAULT 0;

GO
CREATE PROCEDURE cs065 @sno CHAR(3), @cno CHAR(5)
AS
BEGIN
IF EXISTS (SELECT 1 FROM Score065 WHERE Sno = @sno AND Cno = @cno)
BEGIN
PRINT ‘该学生已选修此课程’
RETURN
END

DECLARE @current INT
SELECT @current = cn FROM Course065 WHERE Cno = @cnoIF @current >= 50
BEGINPRINT '该课程人数已满'RETURN
ENDUPDATE Course065 SET cn = cn + 1 WHERE Cno = @cno
INSERT INTO Score065(Sno, Cno, Degree) VALUES (@sno, @cno, 0)
PRINT '选课成功'

END
GO

– 示例调用
EXEC cs065 ‘S002’, ‘C001’;

7．退选课程CSDXXX
某学生选了某课程后，要退选，该课程的当前选课人数减1，并删除其对应的成绩。
EXEC csdXXX 学号 , 课程号
– 示例调用
EXEC cs065 ‘S002’, ‘C001’;

GO
CREATE PROCEDURE csd065 @sno CHAR(3), @cno CHAR(5)
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM Score065 WHERE Sno = @sno AND Cno = @cno)
BEGIN
PRINT ‘该学生未选修此课程’
RETURN
END

DELETE FROM Score065 WHERE Sno = @sno AND Cno = @cno
UPDATE Course065 SET cn = cn - 1 WHERE Cno = @cno
PRINT '退选成功'

END
GO

– 示例调用
EXEC csd065 ‘S002’, ‘C001’;

8… 查询学生成绩cxXXX
某学生选了某课程后, 可以查询其成绩（out）
EXEC cxXXX 学号 , 课程号
GO
CREATE PROCEDURE cx065 @sno CHAR(3), @cno CHAR(5), @degree DECIMAL(5,1) OUTPUT
AS
BEGIN
SELECT @degree = Degree FROM Score065 WHERE Sno = @sno AND Cno = @cno
IF @@ROWCOUNT = 0
BEGIN
PRINT ‘未找到该学生的成绩记录’
RETURN
END
END
GO

– 调用
DECLARE @score DECIMAL(5,1)
EXEC cx065 ‘S001’, ‘C001’, @score OUTPUT
SELECT @score AS Score;

思考题

（1）什么标量函数和表值函数？
标量函数返回的是单个值，即返回一个数字或者字符串
表值函数返回的是表的结构，一般是多行数据
（2）函数定义和调用应注意哪些事项？
必须使用 DBO. 前缀调用用户自定义函数；
函数不能修改数据库状态（如插入、更新）；
参数类型要匹配；
避免在函数中使用 PRINT，应尽量只返回值。
（3）定义存储过程有哪些选项？分别表示什么意思
@parameter OUTPUT：用于返回值；
SET NOCOUNT ON：禁止显示影响行数的消息；
RAISERROR()：抛出错误信息；
IF @@ROWCOUNT = 0：判断是否影响了行；
支持事务控制（BEGIN TRANSACTION, COMMIT, ROLLBACK）；

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

通过本次数据库实验——函数与存储过程的设计与实现，我对 SQL Server 中程序化对象的使用有了更加深入的理解和实践。
在实验过程中，我从最基础的函数编写开始，逐步掌握了如何将一些重复性的业务逻辑封装成函数。比如，在编写 FMax065 和 Fc065 这样的标量函数时，我学会了如何处理输入参数、进行条件判断，并返回一个确定的值；而在编写如 Fsc065 和 FCJA065 这类表值函数时，我理解了函数不仅可以返回单个值，还可以返回整个结果集。
随后，在存储过程部分，我尝试将更复杂的业务逻辑（如成绩统计、选课控制）封装到存储过程中。例如，printcourse065 要求我对成绩进行分段统计并更新临时表，这个过程中我学习到了如何使用变量、条件分支以及动态更新数据。而 cs065 和 csd065 两个选课/退选的存储过程则让我意识到，实际系统中需要考虑并发控制、状态一致性等问题，比如当前课程是否已满、学生是否已经选过该课程等边界情况，这些都需要在设计时充分考虑。
在完成设计性实验的过程中，我也遇到了不少挑战。甚至还有一些没有解决的问题，如在设计性实验中返回该生平均分和班级排名时返回的是一个空值，这肯定是不对的，还有就是当创建函数后发现函数内部的值有问题但是又不能直接重新再创建一遍时该怎么处理会比较合适？对于 cs065 存储过程，如果多个用户同时尝试选同一门课，会不会出现并发冲突？有没有更好的方式来保证选课人数的准确性？这让我明白了：SQL 不只是写出来就能运行，更重要的是逻辑正确、效率合理。数据库不仅仅是建表和查询，更是工程化的系统设计。它涉及事务、锁、索引、并发控制等多个方面，这些内容虽然我现在还不能完全掌握，但已经激发了我进一步学习的兴趣。
总的来说，这次实验让我认识到自己在数据库编程方面的不足。我希望未来能继续深入学习 T-SQL 高级特性，比如游标、触发器、事务控制等，争取写出更专业、更健壮的数据库程序。同时，也希望自己能在今后的学习和项目中，把今天学到的知识应用得更好，真正做到学以致用。