5.16学习总结
数据库原理补充 存储过程和触发器
存储过程
(1)在服务器上创建、运行的程序函数及过程。
(2)有应用程序调用启动,也可由数据完整性规则或触发器调用。
(3)只需在首次运行时候编译,从而加快复杂查询的运行速度。
(4)一个存储过程包含一组经常执行的,逻辑完整的SQL语句,可以传值、返回数据、修改数据。
(5)按作用可以分为两类: 查询类:select 操作类:insert, update, delete
(6)参数信息:成功执行信息,输入输出参数。
1.1存储过程的类型
(1) 系统存储过程 系统存储过程是由系统提供的存储过程,可以作为命令执行各种操作。系统存储过程定义在系统数据库master中,其前缀是sp_。 系统存储过程允许系统管理员执行修改系统表的数据库管理任务,可以在任何一个数据库中执行。
(2) 本地存储过程 在用户数据库中创建的存储过程,完成特定数据库操作任务,其名称不能以sp_为前缀。
(3) 临时存储过程 属于本地存储过程。如果本地存储过程的名称前面有一个“#”,该存储过程就称为局部临时存储过程,只能在一个用户会话中使用。 如果本地存储过程的名称前有两个“##”,该过程就是全局临时存储过程,可以在所有用户会话中使用。
(4) 远程存储过程 远程存储过程指从远程服务器上调用的存储过程。
(5) 扩展存储过程 在SQL Server 环境之外执行的动态链接库称为扩展存储过程,其前缀是sp_。使用时需要先加载到SQL Server 系统中,并且按照使用存储过程的方法执行。
1.2用户存储过程的创建与执行
Create procedure 存储过程名称[;版本号] [{参数 数据类型} [varing] [=默认值] [output]……..] [with {recompile | encryption | recompile,encryption}] [for replication] As SQL语句 参数说明: Varing:该关键字用于存储过程的输出参数为游标的情况。 默认值:用于输入参数。 With recompile:存储过程不驻留在内存中,而是每次执行重新编译。 With encryption:对它的SQL语句加密,其他用户无法查询。 For replication:存储过程只在复制过程中执行,与上一个不同时用。 SQL语句:不适用于创建数据库、索引、表、规则、触发器、过程。
例如: Create procedure pro_stu As select * from student 运行存储过程:execute 或 exec 存储过程名 例如: exec pro_stu
对于存储过程要注意下列几点: (1)用户定义的存储过程只能在当前数据库中创建。 (2)过程名存在sysobjects系统表中,文本存在syscomments中。 (3)存储过程是批处理的第一个语句,不用EXECUTE 关键字。
例如:向学生表中添加一行数据的存储过程 Create procedure pro_stu1 @num char(6),@name char(20), @sex char(2) As Insert into student(sno, sname, ssex) Values(@num, @name, @sex) 运行: Exec pro_stu1 ‘200501’,’张力’,’女’
存储过程的几种情况——带默认值
例如;向学生表中添加一行数据的存储过程 Create procedure pro_stu2 @num char(6)=’200502’, @name char(20)=’李莉’,@sex char(2)=’女’ As Insert into student(sno, sname, ssex) Values(@num, @name, @sex) 运行: Exec pro_stu2 没有指定输入参数的值,则按照默认值去执行。
存储过程的几种情况——带输出参数
例如: 创建一个存储过程,由于返回指定学号学生所选课程的总成绩 Create procedure pro_stu3 @num char(6), @g int output As Select @g=sum(grade) From sc Where sno=@num and grade is not null 运行存储过程: Declare @total int Exec pro_stu3 ‘200502’, @total output
1.3用户存储过程的修改
使用ALTER PROCEDURE命令可修改已存在的存储过程并保留以前赋予的许可。 语法格式: ALTER procedure 存储过程名称[;版本号] [{参数 数据类型} [varing] [=默认值] [output]……..] [with {recompile | encryption | recompile,encryption}] [for replication] As SQL语句 说明: 各参数含义与CREATE PROCEDURE相同。
1.4用户存储过程的删除
语法格式: DROP PROCEDURE { procedure } [ ,...n ] 说明: procedure指要删除的存储过程或存储过程组的名称;n:表示可以指定多个存储过程同时删除。 【例1】删除 XSCJ数据库中的student_info1 存储过程。 USE XSCJ GO DROP PROCEDURE student_info1
2 触发器
触发器是一种特殊的存储过程,其特殊性在于它并不需要由用户直接调用,当对表进行插入、删除、修改等操作时自动执行。 触发器可以用来实施复杂的完整性约束,以防止对数据的不正确修改。 触发器不允许带参数、也不允许被调用。 触发器不能返回任何结果。
触发器的类型
DELETE 触发器 INSERT 触发器 UPDATE 触发器
2.2工作原理
触发器触发时: 系统自动在内存中创建deleted表或inserted表 只读,不允许修改;触发器执行完成后,自动删除 inserted表 临时保存了插入或更新后的记录行 可从inserted表中检查插入的数据是否满足需求 如不满足,向用户报告错误消息,回滚插入操作 deleted表 临时保存了删除或更新前的记录行 可从deleted表检查被删除的数据是否满足业务需求 如不满足,向用户报告错误消息,并回滚插入操作
触发方式
按触发器被激活的时机:前/后触发和替代触发方式
前/后触发: 执行修改语句→各种约束检查→执行后触发器。 执行前触发器→执行修改语句→各种约束检查。 前/后触发只能创建在表上,不能创建在视图上。
替代触发: 引起触发器执行的修改语句停止执行,仅执行触发器,这种触发方式称为替代触发。 替代触发器可以创建在表或视图上。
引起触发器执行的修改语句若违反了某种约束: 后触发方式不会激活触发器。前触发器会被激活。 替代触发器方式会激活触发器。
2.3利用SQL命令创建触发器
语法格式: Create trigger 触发器名 On 表名|视图名 With encryption {for after |before | instead of }{delete| update |insert } [not for replication] [for each row] As ……SQL语句 For each row参数说明:行级触发器(否则是语句级触发器) 一个更新操作涉及多少行记录,触发器就被执行多少次。
例1: 对学生表更新后激活,显示共更新了多少行数据 Create trigger tr_stu on student For after update As print @rowcount
受限插入触发器
Create trigger ex53 ON sc FOR after INSERT AS delete from sc where sno= (select sno from inserted where sno not in (select sno from student )) or cno= (select cno from inserted where cno not in (select cno from course ))
2.4触发器的修改
1.利用SQL命令修改触发器:语法格式: ALTER trigger 触发器名 On 表名|视图名 With encryption {for after | before | instead of }{delete| update |insert } [not for replication] As ……SQL语句
2.5触发器的删除
语法格式: DROP TRIGGER { trigger } [ ,...n ] 说明: trigger:指要删除的触发器名称,包含触发器所有者名。 n:表示可以指定多个触发器。
【例5】 删除触发器reminder。 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminder GO
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库