大型数据库_第4章存储过程与触发器资料

2020年2月5日第1页第4章存储过程和触发器本章概述本章要点本章内容2020年2月5日第2页本章概述如何提高Transact-SQL语句的执行效率呢？如何加强数据库中数据完整性的机制呢？这些问题的解决都依赖于数据库的编程对象。典型的数据库编程对象包括视图、存储过程、触发器、函数等。存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。触发器是一种特殊类型的存储过程，可以实现自动化的操作。本章将全面研究存储过程、触发器特点和使用方式。2020年2月5日第3页本章要点存储过程的特点、类型和作用使用CREATEPROCEDURE语句创建存储过程存储过程的执行方式DML触发器的特点和创建方式DML触发器的工作原理使用CREATETRIGGER语句创建DML触发器DDL触发器的特点和创建方式2020年2月5日第4页本章内容4.1存储过程4.2触发器4.3本章小结4.1存储过程存储过程可以提高应用程序的设计效率和增强系统的安全性。本节将全面介绍存储过程的特点、类型、创建、执行等内容。2020年2月5日第5页类型在MicrosoftSQLServer2008系统中，提供了3种基本的存储过程类型，即用户定义的存储过程、扩展存储过程和系统存储过程。2020年2月5日第6页系统存储过程系统存储过程是方便查询系统信息或完成系统管理任务，一般以sp_开头，并存放在sys架构中。常用的系统存储过程有：1．Sp_help报告有关数据库对象（sys.sysobjects兼容视图中列出的所有对象）、用户定义数据类型或某种数据类型的信息。语法：sp_help[[@objname=]'name'][@objname=]'name'：某个对象的名称。2.sp_helpdb报告有关指定数据库或所有数据库的信息。语法：sp_helpdb[[@dbname=]'name'][@dbname=]'name'：要报告其信息的数据库的名称。如果未指定name，则sp_helpdb将报告sys.databases目录视图中所有数据库的信息。3.sp_helpfile返回与当前数据库关联的文件的物理名称及属性。使用此存储过程确定附加到服务器或从服务器分离的文件名。语法：sp_helpfile[[@filename=]'name'][@filename=]'name'是当前数据库中任意文件的逻辑名称。如果未指定name，则返回当前数据库中所有文件的属性。4.sp_helpfilegroup返回与当前数据库相关联的文件组的名称及属性。语法：sp_helpfilegroup[[@filegroupname=]'name'][@filegroupname=]'name'：当前数据库中任意文件组的逻辑名称。如果没有指定name，则列出当前数据库中的所有文件组并只显示在结果集部分中显示的第一个结果集。5.sp_helpindex报告有关表或视图上索引的信息。语法：sp_helpindex[@objname=]'name'[@objname=]'name'：用户定义的表或视图的名称。6.sp_helpsort显示SQLServer2008实例的排序顺序和字符集。7.sp_helptext显示用户定义规则的定义、默认值、未加密的Transact-SQL存储过程、用户定义Transact-SQL函数、触发器、计算列、CHECK约束、视图或系统对象（如系统存储过程）。语法：sp_helptext[@objname=]'name'[,[@columnname=]computed_column_name]参数：[@objname=]'name'：架构范围内的用户定义对象的限定名称和非限定名称。仅当指定限定对象时才需要引号。如果提供的是完全限定名称（包括数据库名称），则数据库名称必须是当前数据库的名称。对象必须在当前数据库中。name的数据类型为nvarchar(776)，无默认值。[@columnname=]'computed_column_name'：要显示其定义信息的计算列的名称。例1：查看函数multisql_tablevalued_func信息。sp_helptextmultisql_tablevalued_func例2：查看表grade_zcj的计算列“总成绩”信息。sp_helptext'grade_zcj','总成绩'8.sp_helptrigger返回对当前数据库的指定表定义的DML触发器的类型.sp_helptrigger不能用于DDL触发器。语法:sp_helptrigger[@tabname=]'table'[,[@triggertype=]'type']参数:[@tabname=]'table':当前数据库中将为其返回触发器信息的表的名称。[@triggertype=]'type':将为其返回有关信息的DML触发器的类型。可以是DELETE、INSERT、UPDATE。9.sp_monitor：显示有关MicrosoftSQLServer的统计信息。10.sp_rename在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、列、别名数据类型或Microsoft.NETFramework公共语言运行时(CLR)用户定义类型。注意：更改对象名的任一部分都可能破坏脚本和存储过程。建议删除该对象，然后使用新名称重新创建该对象。语法：sp_rename[@objname=]'object_name',[@newname=]'new_name'[,[@objtype=]'object_type']参数：[@objname=]'object_name'：用户对象或数据类型的名称。如果要重命名的对象是表中的列，则object_name的格式必须是table.column。如果要重命名的对象是索引，则object_name的格式必须是table.index。[@newname=]'new_name'：指定对象的新名称。[@objtype=]'object_type'：要重命名的对象的类型。COLUMN表示要重命名的列，DATABASE表示要重命名用户定义数据库，INDEX表示重命名用户定义索引，OBJECT可用于重命名约束（CHECK、FOREIGNKEY、PRIMARY/UNIQUEKEY）、用户表和规则等对象，USERDATATYPE表示重命名通过执行CREATETYPE或sp_addtype添加别名数据类型或CLR用户定义类型。11.sp_who提供有关MicrosoftSQLServer数据库引擎实例中的当前用户、会话和进程的信息。可以筛选信息以便只返回那些属于特定用户或特定会话的非空闲进程。语法：sp_who[[@loginame=]'login'|sessionID|'ACTIVE']参数[@loginame=]'login'|sessionID|'ACTIVE'用于筛选结果集。login用于标识属于特定登录名的进程。sessionID是属于SQLServer实例的会话标识号。ACTIVE排除正在等待用户发出下一个命令的会话。如果没有提供任何值，则过程报告属于实例的所有会话。12.sp_columns返回当前环境中可查询的指定表或视图的列信息。语法：sp_columns[@table_name=]object[,[@table_owner=]owner][,[@table_qualifier=]qualifier][,[@column_name=]column][,[@ODBCVer=]ODBCVer]参数：[@table_name=]object：用于返回目录信息的表或视图的名称。支持通配符模式匹配。[@table_owner=]owner：用于返回目录信息的表或视图的对象所有者。支持通配符模式匹配。如果未指定owner，则是默认表或视图可见性规则。如果当前用户拥有的表或视图具有指定名称，则返回该表的列。如果未指定owner，并且当前用户不拥有具有指定object的表或视图，则sp_columns将搜索数据库所有者所拥有的具有指定object的表或视图。如果有，则返回该表的列。[@table_qualifier=]qualifier：表或视图限定符的名称。在SQLServer中，此列表示数据库名称。在某些产品中，该列表示表所在数据库环境的服务器名。[@column_name=]column：一个单独的列，当只需要目录信息的一列时可使用该参数。如果未指定column，则返回所有列。支持通配符模式匹配。[@ODBCVer=]ODBCVer：当前使用的ODBC版本。例：查询dbo架构中的student表的列信息。sp_columnsstudent,dbo,database_demo13.sp_databases列出驻留在SQLServer2005数据库引擎实例中的数据库或可以通过数据库网关访问的数据库。14.sp_fkeys返回当前环境的逻辑外键信息。该过程显示各种外键关系，包括禁用的外键。语法:sp_fkeys[@pktable_name=]'pktable_name'[,[@pktable_owner=]'pktable_owner'][,[@pktable_qualifier=]'pktable_qualifier']{,[@fktable_name=]'fktable_name'}[,[@fktable_owner=]'fktable_owner'][,[@fktable_qualifier=]'fktable_qualifier']参数:[@pktable_name=]'pktable_name':带主键的表的名称。不支持通配符模式匹配。必须提供该参数或fktable_name参数，或二者都提供。[@pktable_owner=]'pktable_owner'：表（带主键）的所有者的名称。不支持通配符模式匹配。在SQLServer中，如果当前用户拥有具有指定名称的表，则返回该表的列。如果未指定pktable_owner，并且当前用户没有具有指定名称pktable_name的表，则此过程将查找由数据库所有者拥有并具有指定名称pktable_name的表。如果有，则返回该表的列。[@pktable_qualifier=]'pktable_qualifier'：表（带主键）限定符的名称。在SQLServer中，限定符表示数据库名称。在某些产品中，该列表示表所在数据库环境的服务器名。[@fktable_name=]'fktable_name'：表（带外键）的名称。不支持通配符模式匹配。必须提供该参数或pktable_name参数，或二者都提供。[@fktable_owner=]'fktable_owner'：用于表（带外键）的所有者的名称。不支持通配符模式匹配。[@fktable_qualifier=]'fktable_qualifier'：在SQLServer中，限定符表示数据库名称。在某些产品中，该列表示表所在数据库环境的服务器名。例：查看引用student表的外建表信息。Sp_fkeysstudent15.sp_pkeys返回当前环境中单个表的主键信息。语法：sp_pkeys[@table_name=]'name'[,[@table_owner=]'owner'][,[@table_qualifier=]'qualifier']参数：[@table_name=]'name'：为其返回信息的表。不支持通配符模式匹配。[@table_owner=]'owner'：为指定的表指定所有者。在SQLServer中，如果当前用户拥有一个具有指定名称的表，则返回该表的列。如果未指定owner，并且当前用户没有名称为指定name的表，则此过程将查找由数据库所有者拥有，并且名称为指定name的表。如果有，则返回该表的列。[@table_qualifier=]'qualifier'：表限定符。在SQLServer中，此列表示数据库名称。在某些产品