视图、存储过程和触发器

1、SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第1页第第10章章 视图、存储过程和触发器视图、存储过程和触发器 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社教学目标教学重点教学过程SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第2页教学目标教学目标l视图、存储过程和触发器都是数据库中的视图、存储过程和触发器都是数据库中的重要对象。本章讲述视图、存储过程和触重要对象。本章讲述视图、存储过程和触发器的管理操作。发器的管理操作。

2、 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第3页教学重点教学重点l了解视图的基本概念和特点了解视图的基本概念和特点l掌握创建和修改视图技术掌握创建和修改视图技术l掌握使用视图更新数据技术掌握使用视图更新数据技术l了解存储过程的类型和作用了解存储过程的类型和作用l掌握创建存储过程技术掌握创建存储过程技术l掌握执行存储过程技术掌握执行存储过程技术l理解触发器的特点和作用理解触发器的特点和作用l掌握创建触发器技术掌握创建触发器技术SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6

3、月17日第4页教学过程教学过程l视图视图l存储过程存储过程l触发器触发器 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第5页10.1 视图视图 l视图的特点视图的特点l创建、修改和删除视图创建、修改和删除视图 l通过视图更新数据通过视图更新数据 l查看和隐藏视图定义信息查看和隐藏视图定义信息 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第6页视图的特点视图的特点l视图只是一种逻辑对象，是一种虚拟表，视图只是一种逻辑对象，是一种虚拟表，它并不是物理对象，因为视图不占

4、物理存它并不是物理对象，因为视图不占物理存储空间。在视图中被查询的表称为视图的储空间。在视图中被查询的表称为视图的基表。大多数的基表。大多数的SELECT语句都可以用在语句都可以用在创建视图中。创建视图中。l使用视图有许多优点，例如集中用户使用使用视图有许多优点，例如集中用户使用的数据、掩码数据的复杂性、简化权限管的数据、掩码数据的复杂性、简化权限管理以及为向其他应用程序输出而重新组织理以及为向其他应用程序输出而重新组织数据等等。数据等等。 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第7页创建、修改和删除视图创建、修改和删除视

5、图l当创建视图时，当创建视图时，Microsoft SQL Server首先验证首先验证在视图定义中所参考的对象是否存在。视图的名在视图定义中所参考的对象是否存在。视图的名称必须符合命名规则。指定视图所有者的姓名是称必须符合命名规则。指定视图所有者的姓名是可选的。因为视图的外表和表的外表是一样的，可选的。因为视图的外表和表的外表是一样的，因此，为了区别表和视图，应该使用一种命名机因此，为了区别表和视图，应该使用一种命名机制，使人容易分辨出视图和表。创建视图的语法制，使人容易分辨出视图和表。创建视图的语法形式如下：形式如下： CREATE VIEW view_name ( column, n)

6、WITH ENCRYPTION AS select_statement WITH CHECK OPTIONSQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第8页通过视图更新数据通过视图更新数据l如果满足一些限制条件，那么可以通过视图自由地插入、如果满足一些限制条件，那么可以通过视图自由地插入、删除和更新数据。一般地，视图必须定义在一个表上并且删除和更新数据。一般地，视图必须定义在一个表上并且不包括合计函数或在不包括合计函数或在SELECT语句中不包括语句中不包括GROUP BY子句。在修改视图时，要注意下列一些条件：子句。在修改视图

7、时，要注意下列一些条件： 不能同时影响两个或两个以上的基表。可以修改由两个或两个以不能同时影响两个或两个以上的基表。可以修改由两个或两个以上的基表得到的视图，但是每一次修改的数据只能影响一个基表；上的基表得到的视图，但是每一次修改的数据只能影响一个基表； 某些列不能修改。不能修改那些通过计算得到结果的列，例如包某些列不能修改。不能修改那些通过计算得到结果的列，例如包含有计算值的列、有内置函数的列或有合计函数的列；含有计算值的列、有内置函数的列或有合计函数的列； 如果影响到表中那些没有缺省值的列，那么可能引起错误。例如，如果影响到表中那些没有缺省值的列，那么可能引起错误。例如，如果使用如果使用I

8、NSERT语句向视图中插入数据，且该视图的基表有一语句向视图中插入数据，且该视图的基表有一个没有缺省值的列或有一个不允许空的列，且该列没有出现在视个没有缺省值的列或有一个不允许空的列，且该列没有出现在视图的定义中，那么就会产生一个错误消息；图的定义中，那么就会产生一个错误消息； 如果在视图定义中指定了如果在视图定义中指定了WITH CHECK OPTION选项，那么系统选项，那么系统验证所修改的数据。验证所修改的数据。 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第9页查看和隐藏视图定义信息查看和隐藏视图定义信息l为了显示创建视

9、图的文本信息，使用为了显示创建视图的文本信息，使用SQL Server Enterprise Manager查询查询Information_schema.views视图或执行视图或执行sp_helptext系统存储过程，并且使用视图名称作系统存储过程，并且使用视图名称作为该系统存储过程的参数，其语法形式为为该系统存储过程的参数，其语法形式为sp_helptext object_name。 l在实际应用中，经常需要禁止用户看到这些定义在实际应用中，经常需要禁止用户看到这些定义文本。如果不需要用户看到这些定义，那么可以文本。如果不需要用户看到这些定义，那么可以把把syscomments系统表中的有

10、关内容进行加密。系统表中的有关内容进行加密。通过在视图定义语句中增加通过在视图定义语句中增加WITH ENCRYPTION选项，可以加密包含选项，可以加密包含CREATE VIEW语句文本的语句文本的syscomments系统表。系统表。 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第10页10.2 存储过程存储过程 l存储过程的特点存储过程的特点 l存储过程的类型存储过程的类型 l创建存储过程创建存储过程 l执行存储过程执行存储过程 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社20

11、22年6月17日第11页存储过程的特点存储过程的特点l存储过程是存储在服务器上的一组预编译存储过程是存储在服务器上的一组预编译的的Transact-SQL语句。存储过程是一种封语句。存储过程是一种封装重复任务操作的一种方法，支持用户提装重复任务操作的一种方法，支持用户提供的变量，具有强大的编程功能。供的变量，具有强大的编程功能。l一般地，在一般地，在Microsoft SQL Server系统中系统中使用存储过程可以带来许多好处使用存储过程可以带来许多好处 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第12页存储过程的好处存储过

12、程的好处l与其他应用程序共享应用程序的逻辑，因此确保一致的数与其他应用程序共享应用程序的逻辑，因此确保一致的数据访问和操纵。据访问和操纵。l提供一种安全机制。如果用户被授予执行存储过程的权限，提供一种安全机制。如果用户被授予执行存储过程的权限，那么即使该用户没有访问在该存储过程中所参考的表或视那么即使该用户没有访问在该存储过程中所参考的表或视图的权限，该用户也可以完全执行该存储过程，而不会受图的权限，该用户也可以完全执行该存储过程，而不会受到影响。到影响。l由于存储过程在第一次执行之后，其执行规划就驻存在过由于存储过程在第一次执行之后，其执行规划就驻存在过程高速缓冲存储器中。在以后的操作中，只

13、需要从过程高程高速缓冲存储器中。在以后的操作中，只需要从过程高速缓冲存储器中调用编译好的存储过程的二进制形式来执速缓冲存储器中调用编译好的存储过程的二进制形式来执行，因此可以加速存储过程的执行，提高系统的性能。行，因此可以加速存储过程的执行，提高系统的性能。l减少网络交通。减少网络交通。 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第13页存储过程的类型存储过程的类型l在在Microsoft SQL Server 2000关系型数据关系型数据库管理系统中，支持库管理系统中，支持5种类型的存储过程：种类型的存储过程：系统存储过程、

14、本地存储过程、临时存储系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。不过程、远程存储过程和扩展存储过程。不同类型的存储过程具有不同的作用。同类型的存储过程具有不同的作用。SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第14页创建存储过程创建存储过程lCREATE PROCEDURE语句语法形式如下：语句语法形式如下： CREATE PROCEDURE procedure_name parameter data_type WITH RECOMPILE | ENCRYPTION | RECOMPILE , EN

15、CRYPTION AS sql_statementSQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第15页执行存储过程执行存储过程l只有具有存储过程的执行权限只有具有存储过程的执行权限EXECUTE，才可以执行存储过程。执行存储过程有两才可以执行存储过程。执行存储过程有两种方法。一种方法是直接执行存储过程，种方法。一种方法是直接执行存储过程，另外一种方法是在另外一种方法是在INSERT语句中执行存储语句中执行存储过程。过程。l直接执行存储过程就是调用直接执行存储过程就是调用EXECUTE语句语句来执行存储过程。在执行存储过程的时候，

16、来执行存储过程。在执行存储过程的时候，需要提供存储过程的名称和存储过程所需需要提供存储过程的名称和存储过程所需要的参数。要的参数。 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第16页10.3 触发器触发器 l触发器的概念触发器的概念 l创建触发器创建触发器 l触发器的工作原理触发器的工作原理 l一个触发器实例一个触发器实例 SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第17页触发器的概念触发器的概念l当有操作影响到触发器保护的数据时，触发器就自动发生。当有操作影

17、响到触发器保护的数据时，触发器就自动发生。因此，触发器是在特定表上进行定义的，该表也称为触发因此，触发器是在特定表上进行定义的，该表也称为触发器表。当有操作针对触发器表时，例如在表中插入、删除、器表。当有操作针对触发器表时，例如在表中插入、删除、更新数据时，如果该表有相应操作类型的触发器，那么触更新数据时，如果该表有相应操作类型的触发器，那么触发器就自动触发执行。因为触发器在操作有效之后才能执发器就自动触发执行。因为触发器在操作有效之后才能执行，所以在修改中他们代表行，所以在修改中他们代表“最后的动作最后的动作”。假如导致触。假如导致触发器的一个请求失败，发器的一个请求失败，SQL Serve

18、r系统将拒绝更新数据，系统将拒绝更新数据，并且对那些应用程序返回一个错误消息。并且对那些应用程序返回一个错误消息。l触发器既然是一种特殊类型的存储过程，那么它与存储过触发器既然是一种特殊类型的存储过程，那么它与存储过程也有一些区别。例如，存储过程可以由用户直接调用执程也有一些区别。例如，存储过程可以由用户直接调用执行，但是触发器不能被直接调用执行。行，但是触发器不能被直接调用执行。lMicrosoft SQL Server 2000系统提供了系统提供了3种类型的触发种类型的触发器，即器，即INSERT类型、类型、UPDATE类型和类型和DELETE类型。类型。SQL Server实用简明教程实

19、用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第18页创建触发器创建触发器l可以使用可以使用CREATE TRIGGER语句创建触发器。语句创建触发器。在在CREATE TRIGGER语句中，指定了定义触发语句中，指定了定义触发器的基表器的基表 、触发器执行的事件和触发器的所有指、触发器执行的事件和触发器的所有指令。令。CREATE TRIGGER语句的基本语法形式如语句的基本语法形式如下：下： CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF

20、DELETE , INSERT , UPDATE AS sql_statementSQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第19页触发器的工作原理（触发器的工作原理（1）l当向表中插入数据时，当向表中插入数据时，INSERT触发器触发触发器触发执行。当执行。当INSERT触发器触发时，新的记录触发器触发时，新的记录增加到触发器表中和增加到触发器表中和inserted表中。该表中。该inserted表是一个逻辑表，保存了所插入表是一个逻辑表，保存了所插入记录的拷贝，允许用户参考记录的拷贝，允许用户参考INSERT语句中语句中数

21、据。触发器可以检查数据。触发器可以检查inserted表，来确表，来确定该触发器的操作是否应该执行和如何执定该触发器的操作是否应该执行和如何执行。在行。在inserted表中的那些记录，总是触表中的那些记录，总是触发器表中一行或多行记录的冗余。发器表中一行或多行记录的冗余。SQL Server实用简明教程实用简明教程(第二版第二版) 清华大学出版社清华大学出版社2022年6月17日第20页触发器的工作原理（触发器的工作原理（2）l当触发一个当触发一个DELETE触发器时，被删除的触发器时，被删除的记录放在一个特殊的记录放在一个特殊的deleted表中。表中。deleted表是一个逻辑表，用来保存