中南林业科技大学数据库实验七：存储过程和触发器

文章目录

一、目的与要求

掌握编写数据库存储过程的方法。
掌握建立数据库触发器的方法，通过实验观察触发器的作用和触发条件设置等相关操作。
完成老师上课的案列（选）

二、实验准备

了解编写存储过程和调用的T-SQL语法；
了解触发器的作用；
了解编写触发器的T-SQL语法。

三、实验内容

(一)存储过程

1.储备知识

1.1 创建存储过程

有 output 则是输出参数，没有 output 则是输入参数

createprocedure|proc 存储过程名
     @参数名1 {参数数据类型}[=默认值][output], 
     @参数名2 {参数数据类型}[=默认值][output],....as
    SQL_statements

1.2 执行存储过程

exec 存储过程名 参数值1[output]，参数值2[output]...

1.3 修改存储过程

alterprocedure 存储过程名
     参数名 {@参数数据类型}[=默认值][output],
     参数名 {@参数数据类型}[=默认值][output],....as
    SQL_statements

1.4 删除存储过程

dropprocedure 存储过程名

1.5 修改存储过程名

[exec] sp_rename '原存储过程名','新存储过程名'

1.6 其它

begin 和 end
声明变量：declare @变量名数据类型(int,varchar,datetime,char)
自定义变量名不要和字段名一样

📝 实例演示

-- 创建存储过程createprocedure cal
    @aint,@bint,@subint output,@sumint output
asset@sub=@a-@bset@sum=@a+@b
go

-- 调用存储过程declare@subintdeclare@numintexec cal 5,4,@sub output,@num output
select@sub,@sum

2.创建存储过程实例

在

studentdb数据库

中建立存储过程

getPractice

，查询指定院系(名称)(作为存储过程的输入参数)中参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称，若院系不存在，返回提示信息。

createprocedure getPractice
    -- 指定院系(名称)(作为存储过程的输入参数)@namevarchar(24)--mysql中表示24表示24个字符 sqlserver也不是utf-8as--1.查询指定院系的学生 --> D_ID--2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了--3.“实践”课程 --> 先看s_c_info学生与课程对应关系--4.若院系不存在，返回提示信息。if(selectcount(*)from D_Info where D_Name=@name)<>0--说明有院系存在beginselect st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name
            from st_info,s_c_info,C_Info
            whereleft(st_info.St_ID,2)=(select D_ID
                                           from D_Info
                                           where D_Name=@name)and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号and s_c_info.c_no=C_Info.C_No
            and C_Info.C_Type='实践'endelseprint('院系不存在')
go

3.存储过程处理

1️⃣ 分别执行存储过程getPractice，查询“法学院”和“材料科学与工程学院”的学生中参与“实践”课程的所有学生学号、姓名、所学课程编号和课程名称。

exec getPractice '法学院'

exec getPractice '材料科学与工程学院'

2️⃣ 利用系统存储过程sp_rename将getPractice更名为getPctStu

[exec] sp_rename 'getPractice','getPctStu'

3️⃣ 修改存储过程getPctStu，返回指定院系中参与实践课程的学生人次数，并利用该存储过程以“法学院”为输入参数验证执行的结果

alterprocedure getPctStu
    -- 指定院系(名称)(作为存储过程的输入参数)@namevarchar(24)-- mysql中24表示24个字符 sqlserver也不是utf-8as-- 1.查询指定院系的学生 --> D_ID-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系-- 4.若院系不存在，返回提示信息。if(selectcount(*)from D_Info where D_Name=@name)<>0-- 说明有院系存在beginselectcount(*)-- 查询记录数(相同学号也没有关系，算作多条)from st_info,s_c_info,C_Info
            whereleft(st_info.St_ID,2)=(select D_ID
                                           from D_Info
                                           where D_Name=@name)and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号and s_c_info.c_no=C_Info.C_No
            and C_Info.C_Type='实践'endelseprint('院系不存在')
go

验证：调用存储过程

exec getPctStu '法学院'exec getPctStu '法学院啊'

4️⃣ 再修改存储过程getPctStu，返回指定院系中参与实践课程的学生人数。

alterprocedure getPctStu
    -- 指定院系(名称)(作为存储过程的输入参数)@namevarchar(24)-- mysql中表示24表示24个字符 sqlserver也不是utf-8as-- 1.查询指定院系的学生 --> D_ID-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系-- 4.若院系不存在，返回提示信息。if(selectcount(*)from D_Info where D_Name=@name)<>0-- 说明有院系存在begin-- distinct st_info.St_ID --> 过滤掉相同学号的，合并为一条记录selectcount(distinct st_info.St_ID)from st_info,s_c_info,C_Info
            whereleft(st_info.St_ID,2)=(select D_ID
                                           from D_Info
                                           where D_Name=@name)and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号and s_c_info.c_no=C_Info.C_No
            and C_Info.C_Type='实践'endelseprint('院系不存在')
go

验证：调用存储过程调用

exec getPctStu '法学院'exec getPctStu '法学院啊'

⚠️注：“人数”和“人次数”是不同的，对某一学生而言，如果参与了多门实践课程，则“人次数”是指其参与的课程门数，而“人数”仍为1。

(二)触发器

1️⃣ 在studentdb数据库中建立一个具有审计功能的触发器：触发器名为tr_sc，功能要求：审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中，sc_log表中有如下字段：操作类型type，学号st_id，课程号c_no，旧成绩oldscore，新成绩newscore，操作员uname，操作时间udate，其中操作员设定默认值为user，操作时间默认值为系统时间。

创建

sc_log

表：

createtable sc_log(typechar(6),
      st_id char(10),
      c_no char(10),
      oldscore int,
      newscore int,
      uname varchar(20)default'user',
      udate datetimedefault getdate())

创建存储过程：

-- 触发器生效的时候会有两个临时表 deleted inserted-- 当删除或更新操作时候：deleted  对于删除，旧数据  对于更新，原来数据  --> 只有一条记录-- 当执行插入或者更新操作：inserted  新记录 -- 只有一条记录createtrigger tr_sc
on s_c_info
forupdate,insertas-- 审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中 --> 日志操作-- 如果是插入操作 --> deleted表为空-- 如果是更新操作 --> delete表有一条旧数据if(selectcount(*)from deleted)!=0-- 更新操作beginifupdate(score)-- 如果修改了score字段insertinto sc_log(type,st_id,c_no,oldscore,newscore)-- 有默认值我们不用写-- deleted.score 对应 oldscore-- inserted score 对应 newscoreselect'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score
            from deleted,inserted
        endelse-- 表示这是插入操作begin-- 没有 oldscore ，那就忽略不写，则生成的记录中默认该值为nullinsertinto sc_log(type,st_id,c_no,newscore)-- type是数据库的关键字，所以为蓝色select'insert',inserted.st_id,inserted.c_no,inserted.score
            from inserted
        end

测试：调用存储过程

insertinto s_c_info(st_id,c_no,score)values('0603060108','9720013',88)-- 查看结果select*from sc_log

update s_c_info
set score =99where st_id ='0603060108'and c_no ='9720013'-- 查看结果select*from sc_log

2️⃣ 在s_c_info表上建立一个触发器tr_updasc，用于监控对成绩的更新，要求更新后的成绩不能比更新前低，如果新成绩低则取消操作，给出提示信息，否则允许更新。

createtrigger tr_updasc
on s_c_info
forupdateasifupdate(score)begindeclare@oldscoreintdeclare@newscoreintset@oldscore=(select score from deleted)set@newscore=(select score from inserted)if@oldscore>@newscorebeginprint('新成绩不允许低于旧成绩，更新失败')rollbacktransactionendend

测试：调用存储过程

-- 更新记录 --sc_logupdate s_c_info
set score =100where st_id='0603060108'and c_no='9720013'select*from s_c_info
where st_id='0603060108'and c_no='9720013'-- 更新记录不生效 --sc_logupdate s_c_info
set score =98where st_id='0603060108'and c_no='9720013'select*from s_c_info
where st_id='0603060108'and c_no='9720013'

(三)查看存储

用sp_helptext查看存储过程和触发器的代码

sp_helptext '存储过程名'

🚩 补充：实验课考试题目全部代码（按顺序）

createprocedure getPractice
    -- 指定院系(名称)(作为存储过程的输入参数)@namevarchar(24)--mysql中表示24表示24个字符 sqlserver也不是utf-8as--1.查询指定院系的学生 --> D_ID--2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了--3.“实践”课程 --> 先看s_c_info学生与课程对应关系--4.若院系不存在，返回提示信息。if(selectcount(*)from D_Info where D_Name=@name)<>0--说明有院系存在beginselect st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name
            from st_info,s_c_info,C_Info
            whereleft(st_info.St_ID,2)=(select D_ID
                                           from D_Info
                                           where D_Name=@name)and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号and s_c_info.c_no=C_Info.C_No
            and C_Info.C_Type='实践'endelseprint('院系不存在')
go

exec getPractice '法学院'exec getPractice '法学院a'

sp_rename 'getPractice','getPctStu'alterprocedure getPctStu
    -- 指定院系(名称)(作为存储过程的输入参数)@namevarchar(24)-- mysql中24表示24个字符 sqlserver也不是utf-8as-- 1.查询指定院系的学生 --> D_ID-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系-- 4.若院系不存在，返回提示信息。if(selectcount(*)from D_Info where D_Name=@name)<>0-- 说明有院系存在beginselectcount(*)-- 查询记录数(相同学号也没有关系，算作多条)from st_info,s_c_info,C_Info
            whereleft(st_info.St_ID,2)=(select D_ID
                                           from D_Info
                                           where D_Name=@name)and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号and s_c_info.c_no=C_Info.C_No
            and C_Info.C_Type='实践'endelseprint('院系不存在')
go

exec getPctStu '法学院'exec getPctStu '法学院啊'alterprocedure getPctStu
    -- 指定院系(名称)(作为存储过程的输入参数)@namevarchar(24)-- mysql中表示24表示24个字符 sqlserver也不是utf-8as-- 1.查询指定院系的学生 --> D_ID-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系-- 4.若院系不存在，返回提示信息。if(selectcount(*)from D_Info where D_Name=@name)<>0-- 说明有院系存在begin-- distinct st_info.St_ID --> 过滤掉相同学号的，合并为一条记录selectcount(distinct st_info.St_ID)from st_info,s_c_info,C_Info
            whereleft(st_info.St_ID,2)=(select D_ID
                                           from D_Info
                                           where D_Name=@name)and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号and s_c_info.c_no=C_Info.C_No
            and C_Info.C_Type='实践'endelseprint('院系不存在') 
go

exec getPctStu '法学院'exec getPctStu '法学院啊'createtable sc_log(typechar(6),
      st_id char(10),
      c_no char(10),
      oldscore int,
      newscore int,
      uname varchar(20)default'user',
      udate datetimedefault getdate())createtrigger tr_sc
on s_c_info
forupdate,insertas-- 审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中 --> 日志操作-- 如果是插入操作 --> deleted表为空-- 如果是更新操作 --> delete表有一条旧数据if(selectcount(*)from deleted)!=0-- 更新操作beginifupdate(score)-- 如果修改了score字段insertinto sc_log(type,st_id,c_no,oldscore,newscore)-- 有默认值我们不用写-- deleted.score 对应 oldscore-- inserted score 对应 newscoreselect'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score
            from deleted,inserted
        endelse-- 表示这是插入操作begin-- 没有 oldscore ，那就忽略不写，则生成的记录中默认该值为nullinsertinto sc_log(type,st_id,c_no,newscore)-- type是数据库的关键字，所以为蓝色select'insert',inserted.st_id,inserted.c_no,inserted.score
            from inserted
        end-- 插入操作insertinto s_c_info(st_id,c_no,score)values('0603060108','9720013',88)-- 查看结果select*from sc_log

-- 更新记录update s_c_info
set score =99where st_id='0603060108'and c_no='9720013'-- 查看结果select*from sc_log

-- 在s_c_info表上建立一个触发器tr_updasc，用于监控对成绩的更新(update)
go -- 上一条语句结束createtrigger tr_updasc
on s_c_info
forupdateas-- 要求更新后的成绩不能比更新前低，如果新成绩低则取消操作，给出提示信息，否则允许更新。ifupdate(score)-- 如果更新成绩才生效begin-- 分开写declare@oldscoreintdeclare@newscoreint-- 实际变量set@oldscore=(select score from deleted)set@newscore=(select score from inserted)if@newscore<@oldscorebeginprint('成绩不对')rollbacktransaction-- 回滚，所有操作均不生效endend
go -- 结束本次触发器执行-- 更新记录 --sc_logupdate s_c_info
set score =100where st_id='0603060108'and c_no='9720013'--验证更新成功select*from s_c_info
where st_id='0603060108'and c_no='9720013'-- 更新记录不生效 --sc_logupdate s_c_info
set score =98where st_id='0603060108'and c_no='9720013'--验证更新失败select*from s_c_info
where st_id='0603060108'and c_no='9720013'

🚩 补充：腾讯会议讲解时写的SQL代码

-- create procedure | proc 存储过程名--      @参数名1 {参数数据类型}[=默认值] [output], --      @参数名2 {参数数据类型}[=默认值] [output],--     ....--as--    SQL_statements-- 计算两个数的和createprocedure cal
    @aint,-- 入参@bint,-- 入参@sumint output -- 出参asset@sum=@a+@b
go

-- 定义变量declare@sum1int-- 调用存储过程 execute：执行-- 执行存储过程语法：exec 存储过程名 参数1，参数2 [output]exec cal 5,4,@sum1 output -- 如果是出参一定要写outputselect@sum1
go

-- 删除存储过程-- drop table 表名dropprocedure cal
go

alterprocedure cal  
    @aint,-- 入参@bint-- 入参asselect@a+@b
go

-- 修改存储过程名-- 语法：[exec] sp_rename '旧存储过程名','新存储过程名'
sp_rename 'cal','newCal'-- 会有警告exec sp_rename 'newCal','cal'-- 会有警告-- begin endalterprocedure cal  
    @aint,-- 入参@bint-- 入参asbeginselect@a+@bend-- 在`studentdb数据库`中建立存储过程`getPractice`，查询指定院系(名称)(作为存储过程的输入参数)中-- 参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称，若院系不存在，返回提示信息。dropprocedure getPractice
-- varchar 字符串类型createprocedure getPractice
    -- 指定院系(名称)(作为存储过程的输入参数)@namevarchar(24)-- mysql中表示24表示24个字符 sqlserver也不是utf-8as-- 1.查询指定院系的学生 --> D_ID-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系-- 4.若院系不存在，返回提示信息。if(selectcount(*)from D_Info where D_Name=@name)<>0-- 说明有院系存在beginselect st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name
            from st_info,s_c_info,C_Info
            whereleft(st_info.St_ID,2)=(select D_ID
                                           from D_Info
                                           where D_Name=@name)and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号and s_c_info.c_no=C_Info.C_No
            and C_Info.C_Type='实践'endelseprint('院系不存在')
go

-- 调用存储过程exec getPractice '法学院'exec getPractice '材料科学与工程学院'selectcount(*)from D_Info where D_Name='材料科学与工程学院'selectleft('wkx cool',5)-- 重命名：记得写单引号
sp_rename 'getPractice','getPctStu',-- 存储过程名：getPctStu-- 修改存储过程getPctStu，返回指定院系中参与实践课程的学生人次数
go
alterprocedure getPctStu
    -- 指定院系(名称)(作为存储过程的输入参数)@namevarchar(24)-- mysql中24表示24个字符 sqlserver也不是utf-8as-- 1.查询指定院系的学生 --> D_ID-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系-- 4.若院系不存在，返回提示信息。if(selectcount(*)from D_Info where D_Name=@name)<>0-- 说明有院系存在beginselectcount(*)-- 查询记录数(相同学号也没有关系，算作多条)from st_info,s_c_info,C_Info
            whereleft(st_info.St_ID,2)=(select D_ID
                                           from D_Info
                                           where D_Name=@name)and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号and s_c_info.c_no=C_Info.C_No
            and C_Info.C_Type='实践'endelseprint('院系不存在')
go

-- 并利用该存储过程以“法学院”为输入参数验证执行的结果
go
exec getPctStu '法学院'
go
--         再修改存储过程getPctStu，返回指定院系中参与实践课程的学生人数。--> 一个学生只能出现一次-- 原来题目：修改存储过程getPctStu，返回指定院系中参与实践课程的学生人次数 --> 可以有多次出现在查询出来的表中alterprocedure getPctStu
    -- 指定院系(名称)(作为存储过程的输入参数)@namevarchar(24)-- mysql中表示24表示24个字符 sqlserver也不是utf-8as-- 1.查询指定院系的学生 --> D_ID-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系-- 4.若院系不存在，返回提示信息。if(selectcount(*)from D_Info where D_Name=@name)<>0-- 说明有院系存在begin-- distinct st_info.St_ID --> 过滤掉相同学号的，合并为一条记录selectcount(distinct st_info.St_ID)from st_info,s_c_info,C_Info
            whereleft(st_info.St_ID,2)=(select D_ID
                                           from D_Info
                                           where D_Name=@name)and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号and s_c_info.c_no=C_Info.C_No
            and C_Info.C_Type='实践'endelseprint('院系不存在')
go

-- sc_log表中，sc_log表中有如下字段：-- 操作类型type，学号st_id，课程号c_no，旧成绩oldscore，新成绩newscore，操作员uname，操作时间udate，-- 其中操作员设定默认值为user，操作时间默认值为系统时间。createtable sc_log(typechar(10),
    st_id char(10),
    c_no char(10),
    oldscore int,
    newscore int,-- uname varchar(24) default user, --错的 --> 系统的关键字
    uname varchar(24)default'user',
    udate datetimedefault getdate()-- 在不同sql中函数名的差别巨大)-- 在studentdb数据库中建立一个具有审计功能的触发器：-- 触发器名为tr_sc，-- 功能要求：审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中-- create trigger 触发器名-- on 表名  -- 对那张表起作用-- for [update,delete,insert]-- as-- SQL_statement-- 触发器生效的时候会有两个临时表 deleted inserted-- 当删除或更新操作时候：deleted  对于删除，旧数据  对于更新，原来数据  --> 只有一条记录-- 当执行插入或者更新操作：inserted  新记录 -- 只有一条记录
  
go
createtrigger tr_sc
on s_c_info
forupdate,insertas-- 审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中 --> 日志操作-- 如果是插入操作 --> deleted表为空-- 如果是更新操作 --> delete表有一条旧数据if(selectcount(*)from deleted)!=0-- 更新操作beginifupdate(score)-- 如果修改了score字段insertinto sc_log(type,st_id,c_no,oldscore,newscore)-- 有默认值我们不用写-- deleted.score 对应 oldscore-- inserted score 对应 newscoreselect'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score
            from deleted,inserted
        endelse-- 表示这是插入操作begin-- 没有 oldscore ，那就忽略不写，则生成的记录中默认该值为nullinsertinto sc_log(type,st_id,c_no,newscore)-- type是数据库的关键字，所以为蓝色select'insert',inserted.st_id,inserted.c_no,inserted.score
            from inserted
        end-- 测试:插入操作insertinto s_c_info(st_id,c_no,score)values('0603060108','9720013',88)select*from sc_log;-- 更新记录update s_c_info
set score =99where st_id='0603060108'and c_no='9720013'select*from sc_log;-- 在s_c_info表上建立一个触发器tr_updasc，用于监控对成绩的更新(update)
go -- 上一条语句结束createtrigger tr_updasc
on s_c_info
forupdateas-- 要求更新后的成绩不能比更新前低，如果新成绩低则取消操作，给出提示信息，否则允许更新。ifupdate(score)-- 如果更新成绩才生效begin-- 分开写declare@oldscoreintdeclare@newscoreint-- 实际变量set@oldscore=(select score from deleted)set@newscore=(select score from inserted)if@newscore<@oldscorebeginprint('成绩不对')rollbacktransaction-- 回滚，所有操作均不生效endend
go -- 结束本次触发器执行-- 更新记录 --sc_logupdate s_c_info
set score =100where st_id='0603060108'and c_no='9720013'select*from s_c_info
where st_id='0603060108'and c_no='9720013'-- 更新记录不生效 --sc_logupdate s_c_info
set score =98where st_id='0603060108'and c_no='9720013'select*from s_c_info
where st_id='0603060108'and c_no='9720013'

go
sp_helptext tr_updasc -- 显示定义的SQL语句
go

标签：数据库科技 sql

本文转载自: https://blog.csdn.net/qq_62982856/article/details/127399634
版权归原作者 是谢添啊 所有，如有侵权，请联系我们删除。