《中职 数据库应用基础（SQL Server 2016）项目8教学课件.pptx》由会员分享，可在线阅读，更多相关《中职 数据库应用基础（SQL Server 2016）项目8教学课件.pptx（16页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、中职 数据库应用基础（SQL Server 2016）项目8教学课件工信版p理解存储过程的概念和类型p掌握创建、调用和管理存储过程的方法p理解触发器的概念和类型p掌握设计、实现和管理触发器的方法 项目目标项目目标 8.1.1 存储过程的类型1.用户定义存储过程。用户定义存储过程是指封装了可重用代码的模块或例程，可以接受输入参数、向客户端返回表或标量结果和消息、调用数据定义语言和数据操作语言语句，然后返回输出参数。2.临时存储过程。临时存储过程是用户定义存储过程的一种形式。临时存储过程与永久过程相似，只是临时存储过程存储于tempdb中。3.系统存储过程。系统存储过程是指由SQL Server提
2、供的存储过程，可以用来管理SQL Server和显示有关数据库和用户的信息。4.扩展的用户定义存储过程扩展过程允许你使用编程语言（例如C）创建外部例程。这些过程是指SQL Server的实例可以动态加载和运行的DLL。任务任务8.1 8.1 创建存储过程创建存储过程8.1.2 使用存储过程的好处1.减少了服务器/客户端网络流量存储过程中的命令作为代码的单个批处理执行。这可以显著减少服务器与客户端之间的网络流量，因为只有对执行过程的调用才会跨网络发送。如果没有过程提供的代码封装，每个单独的代码行都不得不通过网络发送。2.更强的安全性多个用户和客户端程序可以通过过程对基础数据库对象执行操作，即使用
3、户和程序对这些基础对象没有直接权限。存储过程控制执行哪些进程和活动，并且保护基础数据库对象。这消除在了单独的对象级别授予权限的要求，并且简化了安全层。3.代码的重复使用任何重复的数据库操作的代码都非常适合于在过程中进行封装。这消除了不必要地重复编写相同的代码、降低了代码不一致性，并且允许拥有所需权限的任何用户或应用程序访问和执行代码。4.更容易维护在客户端应用程序调用过程并且将数据库操作保持在数据层中时，对于基础数据库中的任何更改，只有过程是必须更新的。应用程序层保持独立，并且不必知道对数据库布局、关系或进程的任何更改的情况。5.改进的性能默认情况下，在首次执行过程时将编译过程，并且创建一个执
4、行计划，供以后的执行重复使用。因为查询处理器不必创建新计划，所以，它通常用更少的时间来处理过程。任务任务8.1 8.1 创建存储过程创建存储过程8.1.3 创建用户定义存储过程CREATE OR ALTER PROC|PROCEDURE 架构名称.过程名称;数字 参数 类型架构名称.数据类型 VARYING=默认值 OUT|OUTPUT|READONLY ,.WITH,.FOR REPLICATIONAS BEGIN SQL语句;.END;:=ENCRYPTION RECOMPILE EXECUTE AS 子句任务任务8.1 8.1 创建存储过程创建存储过程8.1.5 执行字符串EXECUTE
5、语句的主要用途是执行存储过程。但也可以预先将Transact-SQL语句放在字符串变量中，然后使用EXECUTE语句来执行这个字符串，语法格式如下。EXEC|EXECUTE (字符串变量|N常量字符串+.)AS LOGIN|USER=登录名;其中字符串变量指定一个局部变量的名称，该局部变量可以是任意char、varchar、nchar或nvarchar数据类型，其中包括(max)数据类型。N常量字符串 表示常量字符串，该字符串可以是nvarchar或varchar数据类型。如果包含字母N，则字符串将解释为nvarchar数据类型。LOGIN指定要模拟的上下文是登录名。模拟范围为服务器。USER
6、指定要模拟的上下文是当前数据库中的用户。模拟范围只限于当前数据库。对数据库用户的上下文切换不会继承该用户的服务器级别权限。执行字符串时，数据库上下文的更改只在EXECUTE语句结束前有效。任务任务8.1 8.1 创建存储过程创建存储过程8.1.6 管理存储过程1.查看存储过程信息查看过程名称的列表：使用sys.objects目录视图。显示存储过程的定义：使用sys.sql_modules目录视图。查看存储过程的定义：使用sp_helptext系统存储过程。查看存储过程包含的参数：使用sp_help系统存储过程。查看存储过程的相关性：使用sp_depends系统存储过程。任务任务8.1 8.1
7、创建存储过程创建存储过程8.1.6 管理存储过程2.修改存储过程若要对现有的用户定义存储过程进行修改，可以使用ALTER PROCEDURE语句来实现。在对象资源管理器中可以针对指定的存储过程快速生成所需的ALTER PROCEDURE语句。3.重命名存储过程若要重命名存储过程，可在对象资源管理器中右键单击该存储过程并选择“重命名”，然后输入新的过程名称。此外，也可使用系统存储过程sp_rename对用户定义存储过程进行重命名。4.删除存储过程可以使用SSMS对象资源管理器将存储过程从数据库中删除。也可以使用DROP PROCEDURE语句从当前数据库中删除一个或多个存储过程或存储过程组，语法
8、格式如下。DROP PROC|PROCEDURE IF EXISTS 构架名称.过程,.任务任务8.1 8.1 创建存储过程创建存储过程8.2.1 理解触发器1.DML触发器当数据库中发生数据操作语言（DML）事件时将调用DML触发器。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可以用于强制业务规则和数据完整性、查询其他表并包括复杂的Transact-SQL语句。触发器和触发它的语句可以作为在触发器内回滚的单个事务对待。如果检测到错误（例如磁盘空间不足），则整个事务即自动回滚。按照触发器事件的不同，DML触发器分为三种类型，即INS
9、ERT触发器、UPDATE触发器以及DELETE触发器。2.DDL触发器当服务器或数据库中发生数据定义语言（DDL）事件时将调用DDL发器。像常规DML触发器一样，DDL触发器将激发存储过程以响应事件。但与DML触发器不同的是，它们不是为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发，而是为响应多种数据定义语言（DDL）语句而激发。这些语句主要是以CREATE、ALTER和DROP开头的语句。DDL触发器可以用于管理任务，例如审核和控制数据库操作。任务任务8.2 8.2 创建触发器创建触发器8.2.1 理解触发器1.DML触发器当数据库中发生数据操作语言（DML）事件时将
10、调用DML触发器。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可以用于强制业务规则和数据完整性、查询其他表并包括复杂的Transact-SQL语句。触发器和触发它的语句可以作为在触发器内回滚的单个事务对待。如果检测到错误（例如磁盘空间不足），则整个事务即自动回滚。按照触发器事件的不同，DML触发器分为三种类型，即INSERT触发器、UPDATE触发器以及DELETE触发器。2.DDL触发器当服务器或数据库中发生数据定义语言（DDL）事件时将调用DDL发器。像常规DML触发器一样，DDL触发器将激发存储过程以响应事件。但与DML触发器
11、不同的是，它们不是为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发，而是为响应多种数据定义语言（DDL）语句而激发。这些语句主要是以CREATE、ALTER和DROP开头的语句。DDL触发器可以用于管理任务，例如审核和控制数据库操作。任务任务8.2 8.2 创建触发器创建触发器8.2.2 实现DML触发器1.DML触发器的类型（1）AFTER触发器：这种类型的触发器在执行了INSERT、UPDATE或DELETE语句操作之后执行，它仅适用于表。AFTER触发器可以分为以下三种类型：INSERT触发器、UPDATE触发器和 DELETE触发器。（2）INSTEAD OF触发
12、器：执行这种类型的触发器可以代替通常的触发动作，还可以为带有一个或多个基表的视图定义INSTEAD OF触发器，通过这些触发器能够扩展视图可支持的更新类型。2.创建DML触发器DML触发器可以使用CREATE TRIGGER语句来创建，基本语法格式如下。CREATE OR ALTER TRIGGER 架构名称.触发器名称ON 表|视图WITH ENCRYPTIONFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEAS.任务任务8.2 8.2 创建触发器创建触发器8.2.2 实现DML触发器1.DML触发器的类型（1）AFTER触发器：这种类型的触发器在执行了INS
13、ERT、UPDATE或DELETE语句操作之后执行，它仅适用于表。AFTER触发器可以分为以下三种类型：INSERT触发器、UPDATE触发器和 DELETE触发器。（2）INSTEAD OF触发器：执行这种类型的触发器可以代替通常的触发动作，还可以为带有一个或多个基表的视图定义INSTEAD OF触发器，通过这些触发器能够扩展视图可支持的更新类型。2.创建DML触发器DML触发器可以使用CREATE TRIGGER语句来创建，基本语法格式如下。CREATE OR ALTER TRIGGER 架构名称.触发器名称ON 表|视图WITH ENCRYPTIONFOR|AFTER|INSTEAD O
14、FINSERT,UPDATE,DELETEAS.任务任务8.2 8.2 创建触发器创建触发器8.2.3 实现DDL触发器1.理解DDL触发器设计DDL触发器之前，必须了解DDL触发器的作用域并确定触发触发器的Transact-SQL语句或语句组。在响应当前数据库或服务器中处理的Transact-SQL事件时，都会激发DDL触发器。触发器的作用域取决于事件。例如，每当数据库中发生CREATE TABLE事件时，都会触发为响应CREATE TABLE事件创建的DDL触发器。每当服务器中发生CREATE LOGIN事件时，都会触发为响应CREATE LOGIN事件。2.创建DDL触发器DDL触发器可
15、以使用CREATE TRIGGER语句来创建，基本语法格式如下。CREATE OR ALTER TRIGGER 触发器名称ON ALL SERVER|DATABASEWITH ENCRYPTIONFOR|AFTER 事件类型|事件组,.AS SQL语句.任务任务8.2 8.2 创建触发器创建触发器8.2.4 管理触发器1.修改触发器使用ALTER TRIGGER可以更改以前使用CREATE TRIGGER语句创建的DML或DDL触发器的定义。除了以ALTER关键字开头之外，ALTER TRIGGER的语法组成与CREATE TRIGGER是相同的。2.重命名触发器若要重命名触发器，可使用sp_
16、rename系统存储过程来实现。重命名触发器并不会更改它在触发器定义文本中的名称。要在定义中更改触发器的名称，应直接修改触发器。若要重命名触发器，也可以使用DROP TRIGGER删除已有触发器，然后使用CREATE TRIGGER创建新的触发器。使用DISABLE TRIGGER语句来禁用触发器：DISABLE TRIGGER 架构名称.触发器名称,.|ALLON 对象名称|DATABASE|ALL SERVER已禁用的触发器可以使用ENABLE TRIGGER语句重新启用：ENABLE TRIGGER 构架名称.触发器名称,.|ALLON 对象名称|DATABASE|ALL SERVER;
17、任务任务8.2 8.2 创建触发器创建触发器8.2.4 管理触发器4.查看触发器信息p获取有关数据库中的触发器的信息：使用sys.triggers目录视图。p获取有关服务器范围内的触发器的信息：使用sys.server_triggers目录视图。p获取有关激发触发器的事件的信息：使用sys.trigger_events、sys.events以及sys.server_trigger_events目录视图。p查看触发器定义：使用sys.sql_modules目录视图或sp_helptext系统存储过程。p查看触发器依赖关系：使用sys.sql_dependencies目录视图或sp_depends系统存储过程。5.删除触发器删除DML触发器：DROP TRIGGER 架构名称.触发器名称,.删除DDL触发器DROP TRIGGER 触发器名称,.ON DATABASE|ALL SERVER任务任务8.2 8.2 创建触发器创建触发器