存储过程、自定义函数和触发器

1. 实验环境：

操作系统：Windows 11 家庭中文版

数据库管理系统：SQL Server 2019

客户端：SQL Server Management Studio 19.3.4.0

数据库模式：MyDB

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

课程表：Course(Cno,Cname,Cpno,Ccredit)

选课表：SC(Sno,Cno,Grade)

数据库的值：如图2.4（教材52页）

1. 创建一个带输入参数的存储过程，输入参数n执行存储过程得到n的阶乘。测试5！。

实验代码：

CREATE PROCEDURE FACT (@n INT OUTPUT)

AS

BEGIN

    DECLARE @i INT = 1;

    DECLARE @f INT = 1;

    WHILE @i <= @n

    BEGIN

        SET @f = @f * @i;

        SET @i = @i + 1;

    END

    set @n = @f; -- 将计算结果赋值给输出参数

    print @n;

END;

然后调用存储过程计算5的阶乘

EXEC FACT 5

运行结果：

1. 创建一个简单的存储过程（SCInfo），要求结果输出选课信息，包括学号、姓名、课程号、课程名和成绩。

实验代码：

CREATE PROCEDURE SCInfo

AS

BEGIN

    SELECT

        Student.Sno,

        Student.Sname,

        SC.Cno,

        Course.Cname,

        SC.Grade

    FROM

        Student

    INNER JOIN SC ON Student.Sno = SC.Sno

    INNER JOIN Course ON SC.Cno = Course.Cno

END;

GO

然后调用存储过程

EXEC SCInfo

运行结果：

3、创建一个带输入参数的存储过程，输入分数参数执行存储过程得到平均分大于该分数的学生统计成绩信息（包括学号、姓名、平均分、课程门数字段）。

实验代码：

CREATE PROCEDURE StudentScore

    @averageScore FLOAT

AS

BEGIN

    SELECT

        Student.Sno AS 学号,

        Student.Sname AS 姓名,

        AVG(SC.Grade) AS 平均分,

        COUNT(SC.Cno) AS 课程门数

    FROM

        Student

    INNER JOIN SC ON Student.Sno = SC.Sno

    GROUP BY

        Student.Sno,

        Student.Sname

    HAVING

        AVG(SC.Grade) > @averageScore

END;

GO

然后执行存储过程

EXEC StudentScore 85

运行结果：

4、创建带两个输入参数和一个输出参数的存储过程，执行存储过程时，输入参数为分数段，输出参数为得到该分数段的人次数。

实验代码：

CREATE PROCEDURE RangeCnt

    @minScore INT,

    @maxScore INT,

    @count INT OUTPUT

AS

BEGIN

    -- 计算在给定分数段内的学生人数

    SELECT @count = COUNT(*)

    FROM SC

    WHERE Grade >= @minScore AND Grade < @maxScore;

END;

GO

然后执行存储过程查看

declare @SC INT

EXEC RangeCnt 80,90,@SC output

print '分数段人次数为：'+STR(@SC)

运行结果：

5、创建一个自定义函数，函数返回值为全体学生人数，并写出测试语句以查看结果。

实验代码：

create function dbo.num_of_students()

returns int

AS

begin

declare @n int;

select @n = count(*) from Student;

return @n;

end

go

测试语句以查看结果

select dbo.num_of_students();

运行结果：

6、创建一个自定义函数，函数返回值为某个学生的平均成绩，并写出测试语句以查看结果。

实验代码：

-- 创建一个名为 students_avg_grade 的函数，它接受一个学生ID作为参数，并返回该学生的平均成绩

CREATE FUNCTION dbo.students_avg_grade(@studentID INT)

RETURNS FLOAT

AS

BEGIN

    -- 声明变量来存储平均成绩

    DECLARE @AverageGrade FLOAT;

    -- 计算指定学生的平均成绩

    SELECT @AverageGrade = AVG(grade)

    FROM SC

    WHERE SC.sno = @studentID;

    -- 返回平均成绩

    RETURN @AverageGrade;

END;

GO

测试语句以查看结果

select dbo.students_avg_grade('201215122');

运行结果：

7、在选课表上建立更新触发器，当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：SC_U（Sno,Cno,Oldgrade,Newgrade），其中Oldgrade是修改前的分数，Newgrade是修改后的分数。写出测试语句以查看结果。

实验代码：

首先建立一个SC_U表

create table SC_U

(Sno char(9),

Cno char(4),

OldGrade smallint,

NewGrade smallint)

然后建立一个SC_T的触发器

CREATE TRIGGER SC_T

ON SC

AFTER UPDATE

AS

BEGIN

    -- 检查更新操作是否影响了 Grade 列

    IF UPDATE(Grade)

    BEGIN

        -- 插入更新前后的数据到 SC_U 表

        INSERT INTO SC_U (Sno, Cno, OldGrade, NewGrade)

        SELECT d.Sno, d.Cno, d.Grade AS OldGrade, i.Grade AS NewGrade

        FROM deleted d

        INNER JOIN inserted i ON d.Sno = i.Sno AND d.Cno = i.Cno

        WHERE i.Grade >= 1.1 * d.Grade;

    END

END

然后执行查询

select * from SC_U

update SC set Grade=98 where Sno ='201215121' and Cno ='1'

select * from SC_U

update SC set Grade=95 where Sno ='201215121' and Cno ='2'

select * from SC_U

运行结果：

8、使用触发器实现将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。

实验代码：

首选建表

create table StudentInsertLog

(Numbers smallint)

然后建立触发器

CREATE TRIGGER Student_Count

ON Student

AFTER INSERT

AS

BEGIN

    -- 记录新增的学生个数

    INSERT INTO StudentInsertLog (Numbers)

    SELECT COUNT(*) FROM inserted;

END

最后查询

insert into student values

('201215129','张民','男',18,'CS');

select * from StudentInsertLog;

insert into student values

('201215130','张名','男',18,'CS'),('201215131','张行','男',18,'CS');

select * from StudentInsertLog;

运行结果：

1. 实验总结：

本次实验主要是关于存储过程、自定义函数和触发器的实验，我深刻体会到了SQL sever中这些高级功能的强大和灵活，而且SQL Sever的一些语句与教材还是有不小的出入，比如教材在第五章最后的触发器部分的referencing在SQL Sever中就没有办法正常使用，还有就是在建立触发器的过程中，并不会直接对不存在的表报错，而是在查询时发现该对象不存在，这就需要细心；

本次实验可以说是实现了后几章课程的落地，说实话从理论部分开始就觉得后几张用处没这么大，但是现在发现确实很大，不仅是编程，通过看同学们的学期末项目答辩，就发现了自己的很多的新的问题，比如对于关系模型，逻辑模型的构建，都没有特别熟练