其他数据库对象学习资料

1、第10章 其他数据库对象学习目标本章重点本章内容10/12/20221学习目标标从业务数数据角度度来看，同一种种业务数数据有可可能分散散在不同同的表中中，如何何从一个个数据库库对象中中查看这这些分散散存储的的数据呢呢？从安全角角度来看看，不同同的操作作人员或或许只能能看到表表中不同同部分的的数据。从数据的的应用角角度来看看，一个个报表中中的数据据往往来来自于多多个不同同的表中中，如何何提高报报表的设设计效率率呢？视视图是解解决这些些问题的的一种有有效手段段。存储过程程是一个个可重用用的代码码模块，可以高高效率地地完成指指定的操操作。触发器是是一种特特殊类型型的存储储过程，可以实实现自动动化的操

2、操作。用户定义义函数是是由用户户根据自自己应用用程序的的需要而而定义的的可以完完成特定定操作的的函数。本章将全全面研究究视图、存储过过程、触触发器、用户定定义函数数等数据据库对象象的特点点和使用用方式。1/15/20202本章重点点视图特点点和类型型管理视图图存储过程程特点和和类型管理存储储过程触发器特特点和类类型管理触发发器用户定义义函数1/15/20203本章内容容10.1视视图10.2存存储过程程10.3触触发器10.4用用户定义义函数10.5上上机练习习10.6习习题1/15/20204本章内容容10.1视视图10.2存存储过程程10.3触触发器10.4用用户定义义函数10.5上上机练

3、习习10.6习习题1/15/2020510.1视视图本节全面面研究与与视图有有关的内内容。首先，分分析视图图的作用用和存在在意义。其次，研研究了视视图的类类型和特特点。接下来，探讨创创建视图图技术。然后，讨讨论如何何通过视视图修改改表中数数据。最后，浏浏览如何何通过图图形化工工具来创创建和维维护视图图。1/15/20206概述数据是存存储在表表中，对对数据的的操纵主主要是通通过表进进行的。但是，仅仅通通过表操操纵数据据会带来来一系列列的性能能、安全全、效率率等问题题。下面面，对这这些问题题进行分分析。从业务数数据角度度来看，由于数数据库设设计时考考虑到数数据异常常等问题题，同一一种业务务数据有

4、有可能被被分散在在不同的的表中，但是对对这种业业务数据据的使用用经常是是同时使使用的。前面讲讲过的连连接、子子查询、联合等等技术就就是解决决这种问问题的一一种手段段。但是是，对于于多个表表来说这这些操作作都是比比较复杂杂的，能能不能只只通过一一个数据据库对象象就可以以同时看看到这些些分散存存储的业业务数据据呢？如如果能得得话，将将大大简简化查询询语句的的复杂程程度。从数据安安全角度度来看，由于工工作性质质和需求求不同，不同的的操作人人员只是是需要查查看表中中的部分分数据，不能查查看表中中的所有有数据。从数据的的应用角角度来看看，在设设计报表表时，需需要明确确地指定定数据的的来源途途径和方方式。

5、能能不能采采取有效效手段，提高报报表的设设计效率率呢？解决上述述问题的的一种有有效手段段就是视视图。1/15/20207视图的内内容基表的列列的子集集或行的的子集，也就是是说视图图可以是是基表的的其中一一部分。两个或多多个基表表的联合合，也就就是说视视图是对对多个基基表进行行联合运运算检索索的SELECT语句句。两个或多多个基表表的连接接，也就就是说视视图是通通过对若若干个基基表的连连接生成成的。基表的统统计汇总总，也就就是说视视图不仅仅是基表表的投影影，还可可以是经经过对基基表的各各种复杂杂运算的的结果。另外一个个视图的的子集，也就是是说视图图既可以以基于表表，也可可以基于于另外一一个视图图

6、。来自于函函数或同同义词中中的数据据。视图和基基表的混混合，在在视图的的定义中中，视图图和基表表可以起起到同样样的作用用。1/15/20208视图类型型在Microsoft SQLServer2005系统统中，可可以把视视图分成成3种类类型，即即标准视视图、索索引视图图和分区区视图。一般情况况下的视视图都是是标准视视图，它它是一个个虚拟表表并不占占物理存存储空间间。如果果希望提提高聚合合多行数数据的视视图性能能，那么么可以创创建索引引视图。索引视图图是被物物理化的的视图，它包含含有经过过计算的的物理数数据。通过使用用分区视视图，可可以连接接一台或或多台服服务器中中成员表表中的分分区数据据，使得

7、得这些数数据看起起来就像像来自一一个表中中一样。1/15/20209创建视图图在Microsoft SQLServer2005系统统中，主主要使用用CREATE VIEW语语句创建建视图。只能在在当前数数据库中中创建视视图。当当创建视视图时，Microsoft SQLServer首首先验证证视图定定义中所所引用的的对象是是否存在在。视图的名名称应该该符合命命名规则则。是否否指定视视图的架架构则是是可选的的。视图图的外表表和表的的外表是是一样的的，因此此为了区区别表和和视图，建议采采用一种种命名机机制，使使人容易易分辨出出视图和和表，例例如可以以在视图图名称之之前使用用vw_作为前前缀。1/15

8、/202010【例10-1】使用CREATEVIEW语句句创建简简单的视视图1/15/202011【例10-2】使用sp_helptext系统统存储过过程查看看视图信信息1/15/202012【例10-3】使用WITH ENCRYPTION子子句加密密视图定定义文本本信息1/15/202013【例10-4】使用DROP VIEW语语句删除除视图1/15/202014通过视图图修改数数据无论在什什么时候候修改视视图的数数据，实实际上都都是在修修改视图图的基表表中的数数据。在在满足一一定的限限制条件件下，可可以通过过视图自自由地插插入、删删除和更更新基表表中的数数据。在修改视视图时，要注意意一些条

9、条件1/15/202015【例10-5】通过视视图修改改数据1/15/202016【例10-5】1/15/202017使用SQLServerManagementStudio除了使用用CREATE VIEW语语句之外外，也可可以使用用SQL ServerManagement Studio图形形化工具具定义视视图。1/15/202018【例10-6】使用图图形化工工具定义义视图1/15/202019本章内容容10.1视视图10.2存存储过程程10.3触触发器10.4用用户定义义函数10.5上上机练习习10.6习习题1/15/20202010.2存存储过程程存储过程程可以提提高应用用程序的的设计效效

10、率和增增强系统统的安全全性。本节全面面介绍存存储过程程的特点点、类型型、创建建及执行行等内容容。1/15/202021存储过程程的特点点存储过程程是一个个可重用用的代码码模块，可以高高效率地地完成指指定的操操作。在在Microsoft SQLServer2005系统统中，既既可以使使用Transact-SQL语言言编写存存储过程程，也可可以使用用CLR方式编编写存储储过程。使用CLR编编写存储储过程是是Microsoft SQLServer2005系统统与.NET框框架紧密密集成的的一种表表现形式式。使用Transact-SQL语言言编写存存储过程程而不使使用存储储在客户户端计算算机上的的Tr

11、ansact-SQL语言有有许多优优点1/15/202022存储过程程的类型型在Microsoft SQLServer2005系统统中，提提供了3种基本本的存储储过程类类型，即即用户定定义的存存储过程程、扩展展存储过过程和系系统存储储过程。用户定义义的存储储过程是是主要的的存储过过程类型型，是封封装了可可重用代代码的模模块或例例程。用户定义义的存储储过程可可以接受受输入参参数、向向客户端端返回表表格或标标量结果果和消息息、调用用数据定定义语言言、数据据操纵语语言语句句，然后后返回参参数。在Microsoft SQLServer2005系统统中，用用户定义义的存储储过程又又可以分分为Transa

12、ct-SQL类型型的存储储过程和和CLR类型的的存储过过程。1/15/202023扩展存储储过程扩展存储储过程是是指使用用某种变变成语言言如C语语言创建建的外部部例程，是可以以在MicrosoftSQLServer实例中中动态加加载和运运行的DLL。但是，微微软公司司宣布从从Microsoft SQLServer2005版本本开始，将逐步步删除扩扩展存储储过程类类型，这这是因为为使用CLR存存储过程程可以可可靠和安安全地替替代扩展展存储过过程的功功能。1/15/202024系统存储储过程系统存储储过程是是指用来来完成MicrosoftSQL Server2005中许多多管理活活动的特特殊存储储

13、过程。从物理上上来看，系统存存储过程程存储在在Resource系系统数据据库中，并且带带有sp_前缀缀。从逻辑上上来看，系统存存储过程程出现在在每个系系统数据据库和用用户数据据库的sys架架构中。在Microsoft SQLServer2005系统统中，主主要的系系统存储储过程类类型和功功能如表表10-1所示示。1/15/202025创建存储储过程的的规则在设计和和创建存存储过程程中应该该满足一一定的约约束和规规则。只只有满足足了这些些约束和和规则，才可以以创建有有效的存存储过程程。虽然说在在CREATE PROCEDURE语句句中可以以包括任任意数量量和类型型的Transact-SQL语句句

14、，但是是某些特特殊的语语句是不不能包含含在存储储过程定定义中的的。1/15/202026创建存储储过程在Microsoft SQLServer2005系统统中，可可以使用用CREATE PROCEDURE语句句创建存存储过程程。需要要强调的的是，必必须具有有CREATE PROCEDURE权限限才能创创建存储储过程，存储过过程是架架构作用用域中的的对象，只能在在本地数数据库中中创建存存储过程程。在创建存存储过程程时，应应该指定定所有的的输入参参数、执执行数据据库操作作的编程程语句、返回至至调用过过程或批批处理表表明成功功或失败败的状态态值、捕捕捉和处处理潜在在错误的的错误处处理语句句。1/15

15、/202027CREATEPROCEDURE语句CREATEPROCEDURE procedure_nameparameter_namedata_type, WITH procedure_optionASsql_statement1/15/202028【例10-7】使用CREATEPROCEDURE语句创创建存储储过程1/15/202029【例10-8】使用OUTPUT关关键字创创建存储储过程1/15/202030执行存储储过程在Microsoft SQLServer2005系统统中，可可以使用用EXECUTE语句句执行存存储过程程。EXECUTE语句也也可以简简写为EXEC。如果将要要执行的

16、的存储过过程需要要参数，那么应应该在存存储过程程名称后后面带上上参数值值。1/15/202031【例10-9】使用EXEC语句执执行存储储过程1/15/202032【例10-10】执行行带有参参数的存存储过程程1/15/202033【例10-11】在执执行EXEC语语句时引引用参数数和使用用OUTPUT关键字字1/15/202034修改和删删除存储储过程在Microsoft SQLServer2005系统统中，可可以使用用ALTERPROCEDURE语句修修改已经经存在的的存储过过程。修修改存储储过程不不同于删删除和重重建存储储过程，其目的的是保持持存储过过程的权权限不发发生变化化。例如，如如

17、果修改改HumanResources.GetEmployeeInfo存储过过程，那那么与该该存储过过程对象象相关的的权限将将不会发发生任何何变化。但是，如果删删除HumanResources.GetEmployeeInfo存储储过程并并且重新新创建同同名的存存储过程程，那么么该存储储过程对对象相关关的权限限都需要要重新定定义。如果数据据库中某某个存储储过程不不再需要要了，可可以使用用DROPPROCEDURE语语句删除除该存储储过程。这种删删除是永永久性的的，不能能恢复。1/15/202035存储过程程的执行行过程存储过程程创建之之后，在在第一次次执行时时需要经经过语法法分析阶阶段、解解析阶段

18、段、编译译阶段和和执行阶阶段。语法分析析阶段是是指创建建存储过过程时，系统检检查其创创建语句句的语法法正确性性的过程程。在存存储过程程的创建建过程中中如果碰碰到语法法错误，那么该该存储过过程创建建失败。如果语语法检查查通过之之后，系系统将该该存储过过程的名名称存储储在当前前数据库库的sys.sql_modules目录录视图中中。解析阶段段是指某某个存储储过程首首次执行行时，查查询处理理器从sys.sql_modules目目录视图图中读取取该存储储过程的的文本并并且检查查该过程程引用的的对象名名称是否否存在的的过程。编译阶段段是指分分析存储储过程和和生成存存储过程程执行计计划的过过程。执行阶段段

19、是指执执行驻留留在过程程高速缓缓冲存储储区中的的存储过过程的执执行计划划的过程程。1/15/202036查看存储储过程的的信息在Microsoft SQLServer2005系统统中，可可以使用用系统存存储过程程和目录录视图查查看有关关存储过过程的信信息。如果希望望查看存存储过程程的定义义信息，那么可可以使用用sys.sql_modules目录视视图、OBJECT_DEFINITION元数数据函数数、sp_helptext系统存存储过程程等。1/15/202037【例10-12】查看看存储过过程信息息1/15/202038本章内容容10.1视视图10.2存存储过程程10.3触触发器10.4用用

20、户定义义函数10.5上上机练习习10.6习习题1/15/20203910.3触触发器MicrosoftSQL Server2005系统提提供了两两种强制制业务逻逻辑和数数据完整整性的机机制，即即约束技技术和触触发器技技术。前面已经经讲过了了约束技技术，本本节讲述述触发器器技术。1/15/202040触发器类类型按照触发发事件的的不同，可以把把Microsoft SQLServer2005系统统提供的的触发器器分成两两大类型型，即DML触触发器和和DDL触发器器。需要要补充的的是，DDL触触发器是是Microsoft SQLServer2005系统统新增的的功能，以前的的版本只只有DML触发发器

21、。当数据库库中发生生数据操操纵语言言(datamanipulation language，DML)事事件时将将调用DML触触发器。DML事件包包括在指指定表或或视图中中修改数数据的INSERT语语句、UPDATE语语句或DELETE语语句。在在DML触发器器中，可可以执行行查询其其他表的的操作，也可以以包含更更加复杂杂的Transact-SQL语句句。DML触发发器将触触发器本本身和触触发事件件的语句句作为可可以在触触发器内内回滚的的单个事事务对待待。也就就是说，当在执执行触发发器操作作过程中中，如果果检测到到错误发发生，则则整个触触发事件件语句和和触发器器操作的的事务自自动回滚滚。DDL触触

22、发器与与DML触发器器相同的的是，都都需要触触发事件件进行触触发。但但是，DDL触触发器的的触发事事件是数数据定义义语言(datadefinition language，DDL)语语句。1/15/202041DML触触发器的的基本类类型按照触发发器事件件类型的的不同，可以把把Microsoft SQLServer2005系统统DML触发器器分成3种类型型，即INSERT类类型、UPDATE类类型和DELETE类类型。这也是DML触触发器的的基本类类型。1/15/202042创建DML触发发器DML触触发器是是一种特特殊类型型的存储储过程，所以DML触触发器的的创建和和存储过过程的创创建方式式有

23、很多多相似的的地方。可以使使用CREATETRIGGER语句创创建DML触发发器。在CREATE TRIGGER语语句中，指定了了定义触触发器的的基表或或视图、触发事事件的类类型和触触发的时时间、触触发器的的所有指指令等内内容。使用CREATETRIGGER语句创创建DML触发发器1/15/202043CREATETRIGGER语句句CREATETRIGGERtrigger_nameONtable_name_or_view_nameWITH ENCRYPTION FOR| AFTER |INSTEADOF DELETE , INSERT , UPDATE ASsql_statement1/15

24、/202044【例10-13】查看看触发器器信息1/15/202045DML触触发器的的工作原原理前面介绍绍了DML触发发器的基基本概念念、类型型、特点点、创建建、修改改、删除除等内容容，现在在我们来来看看触触发器是是如何工工作的。通过了了解触发发器的工工作原理理，就可可以更好好地使用用触发器器，写出出效率更更高的触触发器。下面主要要介绍INSERT、DELETE和UPDATE类型型触发器器的工作作原理。1/15/202046INSERT触触发器的的工作原原理当向表中中插入数数据时，INSERT触发器器触发执执行。当当INSERT触发器器触发时时，新的的记录增增加到触触发器表表中和insert

25、ed表中中。该inserted表表是一个个逻辑表表，保存存了所插插入记录录的拷贝贝，允许许用户参参考INSERT语句句中数据据。触发发器可以以检查inserted表，来确定定该触发发器的操操作是否否应该执执行和如如何执行行。在inserted表中中的那些些记录，总是触触发器表表中一行行或多行行记录的的冗余。1/15/202047DELETE触触发器的的工作原原理当触发一一个DELETE触发发器时，被删除除的记录录放在一一个特殊殊的deleted表表中。deleted表是一一个逻辑辑表，用用来保存存已经从从表中删删除的记记录。该该deleted表允允许参考考原来的的DELETE语句删删除的已已经

26、记录录在日志志中的数数据。当当使用DELETE语语句时，应该考考虑下列列一些因因素：当记录放放在deleted表表中的时时候，该该记录就就不会存存在数据据库的表表中了。因此，在数据据库表和和deleted表之之间没有有共同的的记录。逻辑表deleted总是存存放在内内存中，以提供供性能。在DELETE触发器器中，不不能包括括TRUNCATETABLE语语句，这这是因为为该语句句是不记记日志的的操作。1/15/202048UPDATE触触发器的的工作原原理修改一条条记录就就等于插插入一条条新记录录和删除除一条旧旧记录。同样，UPDATE语句也也可以看看成是由由删除一一条记录录的DELETE语句句

27、和增加加一条记记录的INSERT语语句组成成。当在在某一个个有UPDATE触发发器表的的上面修修改一条条记录时时，表中中原来的的记录移移动到deleted表中，修改过过的记录录插入到到了inserted表中。触发器器可以检检查deleted表表和inserted表及被被修改的的表，以以便确定定是否修修改了多多个行和和应该如如何执行行触发器器的操作作。1/15/202049一个具体体的示例例为了更加加全面地地掌握开开发触发发器的步步骤和技技术，本本节通过过一个具具体的示示例，全全面讲述述使用Transact-SQL语语言开发发和创建建触发器器的技术术。一般地，开发触触发器的的过程包包括用户户需求

28、分分析、确确定触发发器的逻逻辑结构构、编写写触发器器代码和和测试触触发器。1/15/202050【例10-14】设计计和实现现DML触发器器1/15/202051创建t_accountData_insert触发器器1/15/202052创建t_accountData_delete触发1/15/202053一组插入入数据的的操作1/15/202054审计到的的插入数数据的操操作1/15/202055一组删除除数据的的操作1/15/202056审计到的的删除数数据的操操作1/15/202057DDL触触发器DDL触触发器与与DML触发器器有许多多类似的的地方，如可以以自动触触发完成成规定的的操作、

29、都可以以使用CREATETRIGGER语句句创建等等，但是是也有一一些不同同的地方方。例如，DDL触触发器的的触发事事件主要要是CREATE、ALTER、DROP以及GRANT、DENY及REVOKE等语语句，并并且触发发的时间间条件只只有AFTER，没有有INSTEADOF。1/15/202058【例10-15】使用用DDL触发器器1/15/202059本章内容容10.1视视图10.2存存储过程程10.3触触发器10.4用用户定义义函数10.5上上机练习习10.6习习题1/15/20206010.4用用户定义义函数在Microsoft SQLServer2005系统统中，用用户定义义函数是是

30、接受参参数、执执行操作作并且将将运算结结果以值值的形式式返回的的例程。这种返回回值既可可以是单单个标量量值，也也可以是是一个结结果集。在Microsoft SQLServer2005系统统中，用用户定义义函数既既可以使使用Transact-SQL语言言编写，也可以以使用任任何.NET编编程语言言来编写写。1/15/202061特点在Microsoft SQLServer2005系统统中，所所有的用用户定义义函数都都具有相相同的由由两部分分组成的的结构：标题和和正文。标题可以以定义这这些内容容：具有有可选架架构/所所有者名名称的函函数名称称；输入入参数名名称和数数据类型型；可以以用于输输入参数数

31、的选项项；返回回参数数数据类型型和可选选名称；可以用用于返回回参数的的选项等等。正文定义义了函数数将要执执行的操操作，这这些操作作既可以以是一个个或多个个Transact-SQL语句，也可以以是.NET程程序集的的引用。在Microsoft SQLServer2005系统统中，用用户定义义函数又又可以分分为两大大类，即即用户定定义标量量函数和和用户定定义表值值函数。用户定定义标量量函数返返回在RETURNS子句中中定义的的类型的的单个数数据值。对于多多语句标标量函数数，定义义在BEGIN END块中中的函数数体包含含一系列列返回单单个值的的Transact-SQL语句。返回类类型可以以是除te

32、xt、ntext、image、cursor、timestamp以以外的任任何数据据类型。用户定定义表值值函数返返回table数据类类型。实实际上，在表值值函数中中，表是是单个SELECT语语句的结结果集。1/15/202062创建时的的考虑在Microsoft SQLServer2005系统统中，可可以分别别使用CREATEFUNCTION、ALTERFUNCTION、DROPFUNCTION语句句来实现现用户定定义函数数的创建建、修改改和删除除。在创创建用户户定义函函数时，每个完完全限定定用户函函数的名名称(schema_name.function_name)必须须唯一。函数的BEGINEN

33、D块块中的语语句不能能有任何何副作用用。函数数副作用用是指对对具有函函数外作作用域(例如修修改数据据库表)的资源源状态的的任何永永久性更更改。函函数中的的语句唯唯一能做做的更改改是对函函数上的的局部对对象(如如局部游游标或局局部变量量)的更更改。不不能在函函数中执执行的操操作包括括对数据据库表的的修改、对不在在函数上上的局部部游标进进行操作作、发送送电子邮邮件、尝尝试修改改目录，以及生生成返回回至用户户的结果果集。1/15/202063使用CREATEFUNCTION语句句在Microsoft SQLServer2005系统统中，使使用CREATEFUNCTION语句句可以创创建标量量函数、内联表表值函数数、多语语句表值值函数。需要说说明的是是，如