《数据库应用技术：第11章 存储过程与触发器》由会员分享，可在线阅读，更多相关《数据库应用技术：第11章 存储过程与触发器（53页珍藏版）》请在金锄头文库上搜索。
1、第11章 存储过程与触发器本章主要介绍SQL Server 2005中存储过程的概念、类型、常用系统存储过程以及如何创建存储过程；另外介绍了触发器的类型以及创建和使用。11.1 存储过程存储过程是由Transact-SQL命令编写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。11.1.1 存储过程的功能及优势Microsoft SQL Server中的存储过程与其他编程语言中的过程类似，可以实现以下功能。 接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。 包含用于在数据库中执行操作(包括调用其他过程)的编程语句。 向调用过程或批处理返回状态值，以指明成功或
2、失败(以及失败的原因)。可以使用Transact-SQL语言的EXECUTE命令运行存储过程。存储过程与函数不同，存储过程不返回取代其名称的值，也不能直接在表达式中使用。在SQL Server中使用存储过程而不使用存储在客户端计算机本地的Transact-SQL程序有以下优势。 存储过程已在服务器注册。 存储过程具有安全特性(例如权限)和所有权链接，且具有可以附加到存储过程的证书。用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。 存储过程可以加强应用程序的安全性。参数化存储过程有助于保护应用程序不受SQL Injection攻击。 存储过程允许模块化程序设计。存储过程
3、一旦创建，以后即可在程序中调用任意多次。这样可以改进应用程序的可维护性，并允许应用程序统一访问数据库。 存储过程可以减少网络通信流量。一个需要数百行Transact-SQL代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。11.1.2 存储过程类型Microsoft SQL Server 2005中有多种可用的存储过程。1. 用户定义的存储过程用户定义的存储过程是指封装了可重用代码的模块或例程。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言(DDL)和数据操作语言(DML)的命令，然后返回输出参数。在SQL Server 2005中，
4、用户定义的存储过程有Transact-SQL或CLR两种类型。(1)Transact-SQL存储过程Transact-SQL存储过程是指保存的Transact-SQL语句集合，可以接受和返回用户提供的参数。存储过程中可能包含根据客户端应用程序提供的信息在一个或多个表中插入新行所需的语句。存储过程也可能从数据库向客户端应用程序返回数据。例如，电子商务Web应用程序可以使用存储过程实现根据联机用户指定的搜索条件返回有关特定产品的信息。(2)CLR存储过程CLR存储过程是指对Microsoft .NET Framework公共语言运行时CLR方法的引用，可以接受和返回用户提供的参数。它们在.NET
5、Framework程序集中是作为类的公共静态方法实现的。2. 扩展存储过程扩展存储过程是指Microsoft SQL Server的实例可以动态加载和运行的DLL，允许使用编程语言(例如C语言)创建自己的外部例程。扩展存储过程直接在SQL Server的实例的地址空间中运行，可以使用SQL Server扩展存储过程API完成编程。3. 系统存储过程SQL Server 2005中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程称为系统存储过程。例如，sys.sp_changedbowner就是一个系统存储过程。从物理意义上讲，系统存储过程存储在源数据库中，并且带有sp_前缀。从逻辑
6、意义上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。在SQL Server 2005中，可将GRANT、DENY和REVOKE权限应用于系统存储过程。SQL Server支持在SQL Server和外部程序之间提供一个接口，以实现各种维护活动的系统存储过程。这些扩展存储程序使用xp_前缀。11.1.3 常用系统存储过程下面介绍几种常用的系统存储过程。1. sp_help报告有关数据库对象(sys.sysobjects兼容视图中列出的所有对象)、用户定义数据类型或SQL Server 2005提供的数据类型的信息。语法格式如下。sp_help objname = nam
7、e sp_help过程仅在当前数据库中查找对象。如果未指定name，则sp_help将列出当前数据库中所有对象的对象名称、所有者和对象类型。sp_helptrigger提供有关触发器的信息。 objname = name：sysobjects类型或systypes表中任何用户定义数据类型的某个对象的名称。name的数据类型为nvarchar(776)，默认值为NULL。不能接受数据库名称。返回代码值为0(成功)或1(失败)。返回的结果集取决于name是否已指定、何时指定以及属于何种数据库对象。该系统存储过程的使用权限为具有public角色的成员身份。l 如果执行不带参数的sp_help，则返回
8、当前数据库中现有的所有类型对象的汇总信息，即Name(对象名称)、所有者(对象所有者)及Object_type(对象类型)。【例11.1】返回有关所有对象的信息。以下示例将列出有关teaching数据库中每个对象的信息。USE teachingGOEXEC sp_helpGO执行结果如图11.1所示。图11.1 teaching 数据库中每个对象的信息l 如果name是SQL Server数据类型或用户定义数据类型，则sp_help将返回结果集包括Type_name(数据类型名称)、Storage_type(SQL Server类型名称)、长度(数据类型的物理长度(以字节为单位)、Prec(精
9、度(数字总位数)、小数位数(小数点右边的数字位数)、Nullable(指示是否允许NULL值：“是”或“否”)、Default_name(绑定到此类型的默认值的名称)、Rule_name(绑定到此类型的规则的名称)、排序规则(数据类型的排序规则)。【例11.2】返回数据类型信息。以下示例将列出有关teaching数据库中smallint数据类型的信息。USE teachingGOEXEC sp_help smallintGO执行结果如图11.2所示。图11.2 teaching 数据库中smallint数据类型的信息l 如果name是数据库对象而不是数据类型，则sp_help将根据指定的数据库
10、对象的类型返回结果集，同时还包括Name(表名)、所有者(表所有者)、Type(表类型)及Created_datetime(表的创建日期)。根据指定的数据库对象，sp_help将返回其他结果集。如果name是系统表、用户表或视图，则sp_help将返回结果集，结果集包括的内容及含义见表11.1至表11.8。但是，不会为视图返回说明数据文件在文件组中位置的结果集。 返回的有关列对象的结果集表11.1 有关列对象的结果集列名数据类型说明Column_namenvarchar(128)列名Typenvarchar(128)列数据类型Computedvarchar(35)指示是否计算列中的值：“是”或
11、“否”Lengthint以字节为单位的列长度Precchar(5)列精度Scalechar(5)列小数位数Nullablevarchar(35)指示是否允许列中包含NULL值：“是”或“否”TrimTrailingBlanks varchar(35)剪裁尾随空格，返回Yes或 NoFixedLenNullInSource varchar(35)仅为保持向后兼容性Collationsysname列的排序规则，对于非字符数据类性为NULL 针对标识列返回的结果集表11.2 针对标识列返回的结果集列名数据类型说明Identity nvarchar(128)其数据类型被声明为标识的列名Seednume
12、ric标识列的起始值Incrementnumeric用于此列中的值的增量Not For Replicationint复制登录名(如 sqlrepl)试图在表中插入数据时，不强制使用IDENTITY属性：1 = True；0 = False 针对各列返回的结果集表11.3 针对各列返回的结果集列名数据类型说明RowGuidColsysname全局唯一标识符列的名称 针对文件组返回的结果集表11.4 针对文件组返回的结果集列名数据类型说明Data_located_on_filegroupnvarchar(128)数据所在的文件组：主要文件组、次要文件组或事务日志文件组 针对索引返回的结果集表11.
13、5 针对索引返回的结果集列名数据类型说明index_namesysname索引名index_descriptionvarchar(210)索引的说明index_keysnvarchar(2078)要生成索引的列的列名 针对约束返回的结果集表11.6 针对约束返回的结果集列名数据类型说明constrain_typenvarchar(146)约束的类型constraint_namenvarchar(128)约束的名称delete_actionnvarchar(9)指示DELETE操作是：No Action、CASCADE还是N/A。仅适用于FOREIGN KEY 约束update_actionnv
14、archar(9)指示UPDATE操作是：No Action、Cascade 还是N/A。仅适用于FOREIGN KEY约束status_enabledvarchar(8)指示是否启用约束：Enabled、Disabled 或N/A。仅适用于CHECK和FOREIGN KEY约束Status_for_replicationvarchar(19)指示约束是否用于复制。仅适用于CHECK和FOREIGN KEY约束constrain_keysnvarchar(2078)构成约束的列的名称。对于默认值和规则而言，则为定义默认值或规则的文本 针对执行引用的对象返回的结果集表11.7 针对执行引用的对象
15、返回的结果集列名数据类型说明Table is referenced by viewsnvarchar(516)标识引用表的其他数据库对象 针对存储过程、函数或扩展存储过程返回的结果集表11.8 针对存储过程、函数或扩展存储过程返回的结果集列名数据类型说明Parameter_namenvarchar(128)存储过程参数名Typenvarchar(128)存储过程参数的数据类型Lengthsmallint最大物理存储长度(以字节为单位)Precint精度，即数字总位数Scaleint小数点右边的数字位数Param_ordersmallint参数的顺序Parameter_namenvarchar(128)存储过程参数名【例11.3】返回有关单个对象
