一、目的与要求
- 掌握编写数据库存储过程的方法。
- 掌握建立数据库触发器的方法,通过实验观察触发器的作用和触发条件设置等相关操作。
- 完成老师上课的案列(选)
二、实验准备
- 了解编写存储过程和调用的T-SQL语法;
- 了解触发器的作用;
- 了解编写触发器的T-SQL语法。
三、实验内容
(一)存储过程
1.储备知识
1.1 创建存储过程
有 output 则是输出参数,没有 output 则是输入参数
create procedure | proc 存储过程名 @参数名1 {参数数据类型}[=默认值] [output], @参数名2 {参数数据类型}[=默认值] [output], .... as SQL_statements
1.2 执行存储过程
exec 存储过程名 参数值1 [output],参数值2 [output]...
1.3 修改存储过程
alter procedure 存储过程名 参数名 {@参数数据类型}[=默认值] [output], 参数名 {@参数数据类型}[=默认值] [output], .... as SQL_statements
1.4 删除存储过程
drop procedure 存储过程名
1.5 修改存储过程名
[exec] sp_rename '原存储过程名','新存储过程名'
1.6 其它
begin
和end
- 声明变量:declare @变量名 数据类型(int,varchar,datetime,char)
- 自定义变量名不要和字段名一样
📝 实例演示
-- 创建存储过程 create procedure cal @a int, @b int, @sub int output, @sum int output as set @sub = @a - @b set @sum = @a + @b go -- 调用存储过程 declare @sub int declare @num int exec cal 5,4,@sub output,@num output select @sub,@sum
2.创建存储过程实例
在studentdb数据库
中建立存储过程getPractice
,查询指定院系(名称)(作为存储过程的输入参数)中参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称,若院系不存在,返回提示信息。
create procedure getPractice -- 指定院系(名称)(作为存储过程的输入参数) @name varchar(24) --mysql中表示24表示24个字符 sqlserver也不是utf-8 as --1.查询指定院系的学生 --> D_ID --2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了 --3.“实践”课程 --> 先看s_c_info学生与课程对应关系 --4.若院系不存在,返回提示信息。 if (select count(*) from D_Info where D_Name=@name) <> 0 --说明有院系存在 begin select 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 where left(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='实践' end else print('院系不存在') go
3.存储过程处理
1️⃣ 分别执行存储过程getPractice,查询“法学院”和“材料科学与工程学院”的学生中参与“实践”课程的所有学生学号、姓名、所学课程编号和课程名称。
exec getPractice '法学院'
exec getPractice '材料科学与工程学院'
2️⃣ 利用系统存储过程sp_rename将getPractice更名为getPctStu
[exec] sp_rename 'getPractice','getPctStu'
3️⃣ 修改存储过程getPctStu,返回指定院系中参与实践课程的学生人次数,并利用该存储过程以“法学院”为输入参数验证执行的结果
alter procedure getPctStu -- 指定院系(名称)(作为存储过程的输入参数) @name varchar(24) -- mysql中24表示24个字符 sqlserver也不是utf-8 as -- 1.查询指定院系的学生 --> D_ID -- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了 -- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系 -- 4.若院系不存在,返回提示信息。 if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在 begin select count(*) -- 查询记录数(相同学号也没有关系,算作多条) from st_info,s_c_info,C_Info where left(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='实践' end else print('院系不存在') go
验证:调用存储过程
exec getPctStu '法学院' exec getPctStu '法学院啊'
4️⃣ 再修改存储过程getPctStu,返回指定院系中参与实践课程的学生人数。
alter procedure getPctStu -- 指定院系(名称)(作为存储过程的输入参数) @name varchar(24) -- mysql中表示24表示24个字符 sqlserver也不是utf-8 as -- 1.查询指定院系的学生 --> D_ID -- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了 -- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系 -- 4.若院系不存在,返回提示信息。 if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在 begin -- distinct st_info.St_ID --> 过滤掉相同学号的,合并为一条记录 select count(distinct st_info.St_ID) from st_info,s_c_info,C_Info where left(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='实践' end else print('院系不存在') 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
表:
create table sc_log( type char(6), st_id char(10), c_no char(10), oldscore int, newscore int, uname varchar(20) default 'user', udate datetime default getdate() )
创建存储过程:
-- 触发器生效的时候会有两个临时表 deleted inserted -- 当删除或更新操作时候:deleted 对于删除,旧数据 对于更新,原来数据 --> 只有一条记录 -- 当执行插入或者更新操作:inserted 新记录 -- 只有一条记录 create trigger tr_sc on s_c_info for update,insert as -- 审计在s_c_info表中对score字段的更新和插入操作,将这些操作记录到sc_log表中 --> 日志操作 -- 如果是插入操作 --> deleted表为空 -- 如果是更新操作 --> delete表有一条旧数据 if (select count(*) from deleted) != 0 -- 更新操作 begin if update(score) -- 如果修改了score字段 insert into sc_log(type,st_id,c_no,oldscore,newscore) -- 有默认值我们不用写 -- deleted.score 对应 oldscore -- inserted score 对应 newscore select 'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score from deleted,inserted end else -- 表示这是插入操作 begin -- 没有 oldscore ,那就忽略不写,则生成的记录中默认该值为null insert into sc_log(type,st_id,c_no,newscore) -- type是数据库的关键字,所以为蓝色 select 'insert',inserted.st_id,inserted.c_no,inserted.score from inserted end
测试:调用存储过程
insert into s_c_info(st_id,c_no,score) values('0603060108','9720013',88) -- 查看结果 select * from sc_log
update s_c_info set score = 99 where st_id = '0603060108' and c_no = '9720013' -- 查看结果 select * from sc_log
2️⃣ 在s_c_info表上建立一个触发器tr_updasc,用于监控对成绩的更新,要求更新后的成绩不能比更新前低,如果新成绩低则取消操作,给出提示信息,否则允许更新。
create trigger tr_updasc on s_c_info for update as if update(score) begin declare @oldscore int declare @newscore int set @oldscore = (select score from deleted) set @newscore = (select score from inserted) if @oldscore > @newscore begin print('新成绩不允许低于旧成绩,更新失败') rollback transaction end end
测试:调用存储过程
-- 更新记录 --sc_log update s_c_info set score = 100 where st_id='0603060108' and c_no='9720013' select * from s_c_info where st_id='0603060108' and c_no='9720013' -- 更新记录不生效 --sc_log update s_c_info set score = 98 where st_id='0603060108' and c_no='9720013' select * from s_c_info where st_id='0603060108' and c_no='9720013'
(三)查看存储
用sp_helptext查看存储过程和触发器的代码
sp_helptext '存储过程名'
🚩 补充:实验课考试题目全部代码(按顺序)
create procedure getPractice -- 指定院系(名称)(作为存储过程的输入参数) @name varchar(24) --mysql中表示24表示24个字符 sqlserver也不是utf-8 as --1.查询指定院系的学生 --> D_ID --2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了 --3.“实践”课程 --> 先看s_c_info学生与课程对应关系 --4.若院系不存在,返回提示信息。 if (select count(*) from D_Info where D_Name=@name) <> 0 --说明有院系存在 begin select 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 where left(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='实践' end else print('院系不存在') go exec getPractice '法学院' exec getPractice '法学院a' sp_rename 'getPractice','getPctStu' alter procedure getPctStu -- 指定院系(名称)(作为存储过程的输入参数) @name varchar(24) -- mysql中24表示24个字符 sqlserver也不是utf-8 as -- 1.查询指定院系的学生 --> D_ID -- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了 -- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系 -- 4.若院系不存在,返回提示信息。 if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在 begin select count(*) -- 查询记录数(相同学号也没有关系,算作多条) from st_info,s_c_info,C_Info where left(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='实践' end else print('院系不存在') go exec getPctStu '法学院' exec getPctStu '法学院啊' alter procedure getPctStu -- 指定院系(名称)(作为存储过程的输入参数) @name varchar(24) -- mysql中表示24表示24个字符 sqlserver也不是utf-8 as -- 1.查询指定院系的学生 --> D_ID -- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了 -- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系 -- 4.若院系不存在,返回提示信息。 if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在 begin -- distinct st_info.St_ID --> 过滤掉相同学号的,合并为一条记录 select count(distinct st_info.St_ID) from st_info,s_c_info,C_Info where left(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='实践' end else print('院系不存在') go exec getPctStu '法学院' exec getPctStu '法学院啊' create table sc_log( type char(6), st_id char(10), c_no char(10), oldscore int, newscore int, uname varchar(20) default 'user', udate datetime default getdate() ) create trigger tr_sc on s_c_info for update,insert as -- 审计在s_c_info表中对score字段的更新和插入操作,将这些操作记录到sc_log表中 --> 日志操作 -- 如果是插入操作 --> deleted表为空 -- 如果是更新操作 --> delete表有一条旧数据 if (select count(*) from deleted) != 0 -- 更新操作 begin if update(score) -- 如果修改了score字段 insert into sc_log(type,st_id,c_no,oldscore,newscore) -- 有默认值我们不用写 -- deleted.score 对应 oldscore -- inserted score 对应 newscore select 'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score from deleted,inserted end else -- 表示这是插入操作 begin -- 没有 oldscore ,那就忽略不写,则生成的记录中默认该值为null insert into sc_log(type,st_id,c_no,newscore) -- type是数据库的关键字,所以为蓝色 select 'insert',inserted.st_id,inserted.c_no,inserted.score from inserted end -- 插入操作 insert into s_c_info(st_id,c_no,score) values('0603060108','9720013',88) -- 查看结果 select * from sc_log -- 更新记录 update s_c_info set score = 99 where st_id='0603060108' and c_no='9720013' -- 查看结果 select * from sc_log -- 在s_c_info表上建立一个触发器tr_updasc,用于监控对成绩的更新(update) go -- 上一条语句结束 create trigger tr_updasc on s_c_info for update as -- 要求更新后的成绩不能比更新前低,如果新成绩低则取消操作,给出提示信息,否则允许更新。 if update(score) -- 如果更新成绩才生效 begin -- 分开写 declare @oldscore int declare @newscore int -- 实际变量 set @oldscore = (select score from deleted) set @newscore = (select score from inserted) if @newscore < @oldscore begin print('成绩不对') rollback transaction -- 回滚,所有操作均不生效 end end go -- 结束本次触发器执行 -- 更新记录 --sc_log update s_c_info set score = 100 where st_id='0603060108' and c_no='9720013' --验证更新成功 select * from s_c_info where st_id='0603060108' and c_no='9720013' -- 更新记录不生效 --sc_log update s_c_info set score = 98 where 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 -- 计算两个数的和 create procedure cal @a int, -- 入参 @b int, -- 入参 @sum int output -- 出参 as set @sum = @a + @b go -- 定义变量 declare @sum1 int -- 调用存储过程 execute:执行 -- 执行存储过程语法:exec 存储过程名 参数1,参数2 [output] exec cal 5,4,@sum1 output -- 如果是出参一定要写output select @sum1 go -- 删除存储过程 -- drop table 表名 drop procedure cal go alter procedure cal @a int, -- 入参 @b int -- 入参 as select @a+@b go -- 修改存储过程名 -- 语法:[exec] sp_rename '旧存储过程名','新存储过程名' sp_rename 'cal','newCal' -- 会有警告 exec sp_rename 'newCal','cal' -- 会有警告 -- begin end alter procedure cal @a int, -- 入参 @b int -- 入参 as begin select @a+@b end -- 在`studentdb数据库`中建立存储过程`getPractice`,查询指定院系(名称)(作为存储过程的输入参数)中 -- 参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称,若院系不存在,返回提示信息。 drop procedure getPractice -- varchar 字符串类型 create procedure getPractice -- 指定院系(名称)(作为存储过程的输入参数) @name varchar(24) -- mysql中表示24表示24个字符 sqlserver也不是utf-8 as -- 1.查询指定院系的学生 --> D_ID -- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了 -- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系 -- 4.若院系不存在,返回提示信息。 if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在 begin select 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 where left(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='实践' end else print('院系不存在') go -- 调用存储过程 exec getPractice '法学院' exec getPractice '材料科学与工程学院' select count(*) from D_Info where D_Name='材料科学与工程学院' select left('wkx cool',5) -- 重命名:记得写单引号 sp_rename 'getPractice','getPctStu', -- 存储过程名:getPctStu -- 修改存储过程getPctStu,返回指定院系中参与实践课程的学生人次数 go alter procedure getPctStu -- 指定院系(名称)(作为存储过程的输入参数) @name varchar(24) -- mysql中24表示24个字符 sqlserver也不是utf-8 as -- 1.查询指定院系的学生 --> D_ID -- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了 -- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系 -- 4.若院系不存在,返回提示信息。 if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在 begin select count(*) -- 查询记录数(相同学号也没有关系,算作多条) from st_info,s_c_info,C_Info where left(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='实践' end else print('院系不存在') go -- 并利用该存储过程以“法学院”为输入参数验证执行的结果 go exec getPctStu '法学院' go -- 再修改存储过程getPctStu,返回指定院系中参与实践课程的学生人数。--> 一个学生只能出现一次 -- 原来题目:修改存储过程getPctStu,返回指定院系中参与实践课程的学生人次数 --> 可以有多次出现在查询出来的表中 alter procedure getPctStu -- 指定院系(名称)(作为存储过程的输入参数) @name varchar(24) -- mysql中表示24表示24个字符 sqlserver也不是utf-8 as -- 1.查询指定院系的学生 --> D_ID -- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了 -- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系 -- 4.若院系不存在,返回提示信息。 if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在 begin -- distinct st_info.St_ID --> 过滤掉相同学号的,合并为一条记录 select count(distinct st_info.St_ID) from st_info,s_c_info,C_Info where left(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='实践' end else print('院系不存在') go -- sc_log表中,sc_log表中有如下字段: -- 操作类型type,学号st_id,课程号c_no,旧成绩oldscore,新成绩newscore,操作员uname,操作时间udate, -- 其中操作员设定默认值为user,操作时间默认值为系统时间。 create table sc_log( type char(10), st_id char(10), c_no char(10), oldscore int, newscore int, -- uname varchar(24) default user, --错的 --> 系统的关键字 uname varchar(24) default 'user', udate datetime default 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 create trigger tr_sc on s_c_info for update,insert as -- 审计在s_c_info表中对score字段的更新和插入操作,将这些操作记录到sc_log表中 --> 日志操作 -- 如果是插入操作 --> deleted表为空 -- 如果是更新操作 --> delete表有一条旧数据 if (select count(*) from deleted) != 0 -- 更新操作 begin if update(score) -- 如果修改了score字段 insert into sc_log(type,st_id,c_no,oldscore,newscore) -- 有默认值我们不用写 -- deleted.score 对应 oldscore -- inserted score 对应 newscore select 'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score from deleted,inserted end else -- 表示这是插入操作 begin -- 没有 oldscore ,那就忽略不写,则生成的记录中默认该值为null insert into sc_log(type,st_id,c_no,newscore) -- type是数据库的关键字,所以为蓝色 select 'insert',inserted.st_id,inserted.c_no,inserted.score from inserted end -- 测试:插入操作 insert into s_c_info(st_id,c_no,score) values('0603060108','9720013',88) select * from sc_log; -- 更新记录 update s_c_info set score = 99 where st_id='0603060108' and c_no='9720013' select * from sc_log; -- 在s_c_info表上建立一个触发器tr_updasc,用于监控对成绩的更新(update) go -- 上一条语句结束 create trigger tr_updasc on s_c_info for update as -- 要求更新后的成绩不能比更新前低,如果新成绩低则取消操作,给出提示信息,否则允许更新。 if update(score) -- 如果更新成绩才生效 begin -- 分开写 declare @oldscore int declare @newscore int -- 实际变量 set @oldscore = (select score from deleted) set @newscore = (select score from inserted) if @newscore < @oldscore begin print('成绩不对') rollback transaction -- 回滚,所有操作均不生效 end end go -- 结束本次触发器执行 -- 更新记录 --sc_log update s_c_info set score = 100 where st_id='0603060108' and c_no='9720013' select * from s_c_info where st_id='0603060108' and c_no='9720013' -- 更新记录不生效 --sc_log update s_c_info set score = 98 where 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