数据库应用基础（SQL Server 2019）（AIGC赋能版） 课件 项目8 创建存储过程和触发器

项目8创建存储过程和触发器《数据库应用基础（SQLServer2019）》项目目标理解存储过程的概念和类型掌握创建、调用和管理存储过程的方法理解触发器的概念和类型掌握设计、实现和管理触发器的方法本章目录8.1创建存储过程8.2创建触发器创建存储过程8.1任务8.1创建存储过程在T-SQL中存储和执行程序主要有两种方式：一种方式是将程序存储在本地并创建向SQLServer发送命令和处理结果的应用程序，另一种方式则是将程序作为存储过程存储在数据库中并创建执行存储过程和处理结果的应用程序。通过本任务将学习和掌握创建和执行存储过程的方法。任务目标了解存储过程的类型了解使用存储过程的好处掌握创建存储过程的方法掌握执行存储过程和字符串的方法掌握管理存储过程的的方法8.1.1存储过程的类型存储过程的类型类型说明用户定义存储过程封装可重用代码，可接受输入/输出参数，在用户数据库中创建临时存储过程存储于tempdb；#本地（当前连接可见），##全局（所有用户可见）系统存储过程以sp_前缀，管理SQLServer，位于Resource数据库，逻辑上出现在每个数据库的sys架构扩展存储过程以xp_前缀，使用C等语言创建（未来版本将删除，建议改用CLR过程）8.1.2使用存储过程的好处使用存储过程的好处✅减少网络流量：单个批处理执行，减少跨网络传输✅更强的安全性：用户无需直接操作基础对象参数化执行避免SQL注入可加密存储过程源代码✅代码重复使用：封装重复数据库操作✅更容易维护：数据库更改只需更新存储过程，应用程序层不变✅改进的性能：首次执行后缓存执行计划，后续重用8.1.3创建用户定义存储过程（1/2）CREATEPROCEDURE基本语法CREATE[ORALTER]{PROC|PROCEDURE}[架构.]过程名

[{@参数数据类型[VARYING][=默认值][OUTPUT][READONLY]}[,...]][WITH{ENCRYPTION|RECOMPILE|EXECUTEAS...}][FORREPLICATION]ASBEGINSQL语句...END8.1.3创建用户定义存储过程（2/2）参数说明：@参数：以@开头，最多2100个参数OUTPUT：输出参数，返回给调用方默认值：可为常量或NULLENCRYPTION：模糊化过程定义文本RECOMPILE：每次执行时重新编译，不缓存计划EXECUTEAS：指定安全上下文限制：不能使用CREATEVIEW、USE等语句例8.1：

创建带两个输入参数的存储过程uspGetGrade按姓名和课程名称检索成绩若只提供姓名，则检索所有课程成绩8.1.4执行存储过程EXECUTE语法

[EXEC|EXECUTE][@返回状态=]模块名[;数字][[@参数=]{值|@变量[OUTPUT]|[DEFAULT]}][,...][WITHRECOMPILE]说明可简写为EXEC若EXECUTE是批处理第一条语句，可省略关键字使用@参数名=值格式时，参数顺序可任意OUTPUT参数必须用变量传递例8.2：创建存储过程uspGetClassGrade并执行带输入参数（班级、课程名称）带输出参数（平均分、最高分、最低分）8.1.5执行字符串功能将T-SQL语句放在字符串变量中，然后用EXECUTE执行语法EXECUTE(@字符串变量|'常量字符串'[+...])[AS{LOGIN|USER}='名称']例8.3：使用变量拼接SQL字符串，查询电2401班英语成绩并按降序排序8.1.6管理存储过程查看信息sys.objects：查看过程名称列表sys.sql_modules：查看过程定义sp_helptext：显示存储过程定义sp_help：查看参数信息sp_depends：查看相关性修改ALTERPROCEDURE或SSMS右键“修改”重命名SSMS右键“重命名”或sp_rename删除DROPPROCEDURE[IFEXISTS]或SSMS右键“删除”创建触发器8.2任务8.2创建触发器SQLServer提供了两种主要机制来强制执行业务规则和数据完整性，即约束和触发器。约束可以在创建或修改表时创建，这方面的内容已经在项目3中已经学习过了。通过本任务将学习和掌握创建和使用触发器的方法。任务目标理解触发器的基本概念和类型掌握设计和实现DML触发器的方法掌握设计和实现DDL触发器的方法掌握管理触发器的相关操作方法8.2.1理解触发器触发器关联到表的特殊存储过程，在发生语言事件时自动执行两大类型DML触发器：响应INSERT、UPDATE、DELETE语句DDL触发器：响应CREATE、ALTER、DROP等DDL语句DML触发器的作用：实现级联更改防止恶意或错误的DML操作，强制执行比CHECK约束更复杂的限制评估数据修改前后状态并采取措施一个表可有多个同类DML触发器8.2.2实现DML触发器（1/2）DML触发器类型：AFTER触发器：在INSERT、UPDATE、DELETE操作执行之后激发（仅适用于表）INSERT触发器、UPDATE触发器、DELETE触发器INSTEADOF触发器：代替通常的触发动作执行，可用于表或视图创建DML触发器（CREATETRIGGER）：CREATETRIGGER[架构.]触发器名称ON[表|视图][WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{INSERT|UPDATE|DELETE}ASBEGINSQL语句...END8.2.2实现DML触发器（2/2）逻辑表inserted（新值）deleted（旧值）注意事项触发器内不能使用CREATEDATABASE、DROPTABLE等语句CREATETRIGGER必须是批处理第一条语句触发器不返回结果集（建议使用SETNOCOUNTON）TRUNCATETABLE不触发DELETE触发器例8.4：AFTERINSERT触发器：向学生表插入记录时自动向成绩表添加相关成绩记录例8.5：INSTEADOFDELETE触发器：删除学生记录时同时删除成绩表中相关记录8.2.3实现DDL触发器（1/2）DDL触发器作用域DATABASE：当前数据库中的DDL事件ALLSERVER：整个服务器中的DDL事件创建DDL触发器CREATETRIGGER触发器名称ON{ALLSERVER|DATABASE}[WITHENCRYPTION]{FOR|AFTER}{事件类型|事件组}ASBEGINSQL语句...END8.2.3实现DDL触发器（2/2）事件组示例DDL_TABLE_EVENTS（包含CREATE/ALTER/DROPTABLE）获取事件信息EVENTDATA()函数例8.6：数据库级DDL触发器safety：阻止删除视图，并回滚事务8.2.4管理触发器（1/2）修改触发器ALTERTRIGGER或SSMS右键“修改”重命名触发器sp_rename（不会更改定义文本中的名称）禁用/启用触发器DISABLETRIGGER触发器名ON{表|DATABASE|ALLSERVER}ENABLETRIGGER...查看触发器信息：sys.triggers（数据库级）、sys.server_triggers（服务器级）sp_helptext（查看