数据库应用技术：第11章_存储过程与触发器

1、第11章 存储过程与触发器本章内容11.1 存储过程11.1.1 存储过程的功能及优势11.1.2 存储过程类型11.1.3 常用系统存储过程11.1.4 设计存储过程11.1.5 实现存储过程11.2 触发器11.2.1 DML触发器11.2.2 DDL触发器11.1 存储过程存储过程是SQL Server服务器上一组预编译的Transact-SQL语句，用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。10.1 存储过程概述11.1.1. 存储过程的功能及优势SQL Server的存储过程可实现以下功能：(1)接收输入参数并以输出参数的形式为调用过程或批处理返回多个值

2、。(2)包含执行数据库操作的编程语句，包括调用其他过程。(3)为调用过程或批处理返回一个状态值，以表示成功或失败(及失败原因)。10.1 存储过程概述存储过程具有以下优点(1)模块化编程。(2)快速执行。 (3)减少网络通信量。(4)提供安全机制。(5)保证操作一致性。10.1 存储过程概述SQL Server存储过程的类型包括：用户定义存储过程扩展存储过程。系统存储过程11.1.2. 存储过程的类型10.1 存储过程概述(1)用户定义存储过程是由用户创建并能完成某一特定功能(例如查询用户所需数据信息)的存储过程。它处于用户创建的数据库中，存储过程名前没有前缀sp_。10.1 存储过程概述(2

3、)扩展存储过程扩展存储过程是SQL Server可以动态装载和执行的动态链接库(DLL)。当扩展存储过程加载到SQL Server中，它的使用方法与系统存储过程一样。扩展存储过程只能添加到master数据库中，其前缀是xp_。10.1 存储过程概述(3)系统存储过程 是指由系统提供的存储过程，主要存储在master数据库中并以sp_为前缀，它从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。通过系统存储过程，SQL Server中的许多管理性或信息性的活动(例如使用sp_depends、sp_helptexts可以了解数据数据库对象、数据库信息)都可以顺利有效地完成。尽管系

4、统存储过程被放在master数据库中，仍可以在其他数据库中对其进行调用(调用时，不必在存储过程名前加上数据库名)。当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。11.1.3 常用系统存储过程1. sp_help2. sp_helpdb3. sp_helpfile4. sp_rename5. sp_renamedb6. sp_databases7. sp_tables8. sp_columns1. sp_help报告有关数据库对象(sys.sysobjects兼容视图中列出的所有对象)、用户定义数据类型或SQL Server 2005提供的数据类型的信息。语法格式如下。sp_h

5、elp objname = name 【例11.1】返回有关所有对象的信息。以下示例将列出有关teaching数据库中每个对象的信息。USE teachingGOEXEC sp_helpGO【例11.2】返回数据类型信息。以下示例将列出有关teaching数据库中smallint数据类型的信息。USE teachingGOEXEC sp_help smallintGO【例11.3】返回有关单个对象的信息。以下示例将显示有关student表的信息。USE teachingGOEXEC sp_help studentGO2. sp_helpdb报告有关指定数据库或所有数据库的信息。语法格式如下。s

6、p_helpdb dbname= name dbname = name：要报告其信息的数据库的名称。name的数据类型为sysname，没有默认值。如果未指定name，则sp_helpdb将报告sys.databases目录视图中所有数据库的信息。返回代码值为0(成功)或1(失败)。【例11.4】返回有关单个数据库的信息。以下示例显示有关teaching数据库的信息。EXEC sp_helpdb teaching GO【例11.5】返回有关所有数据库的信息。以下示例显示运行在SQL Server服务器上所有数据库的信息。EXEC sp_helpdbGO3. sp_helpfile返回与当前数据

7、库关联的文件的物理名称及属性。使用此存储过程可以确定附加到服务器或从服务器分离的文件名。语法格式如下。sp_helpfile filename = name filename = name：是当前数据库中任意文件的逻辑名称。name的数据类型为sysname，默认值为NULL。如果未指定name，则返回当前数据库中所有文件的属性。返回代码值为0(成功)或1(失败)。【例11.6】以下示例返回有关teaching中的文件的信息USE teachingGOEXEC sp_helpfileGO4. sp_rename在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、列、别名数据类型或Mic

8、rosoft .NET Framework公共语言运行时(CLR)用户定义类型。语法格式如下。sp_rename objname = object_name , newname = new_name , objtype = object_type 只能更改当前数据库中的对象名称或数据类型名称。大多数系统数据类型和系统对象的名称都不能更改。【例11.7】重命名表。以下示例将student表重命名为stu。USE teachingGOEXEC sp_rename student, stuGO【例11.8】重命名列。以下示例将student表中的SNO重命名为SID。USE teachingGOEX

9、EC sp_rename student.SNO, SID, COLUMNGO【例11.9】重命名索引。以下示例将PK_student索引重命名为PK_stu。USE teachingGOEXEC sp_rename student.PK_student, PK_stu, INDEXGO5. sp_renamedb更改数据库的名称。语法格式如下。sp_renamedb dbname = old_name , newname = new_name dbname = old_name：数据库的当前名称。old_name的数据类型为sysname，无默认值。 newname = new_name：数

10、据库的新名称。new_name必须遵循有关标识符的规则。new_name的数据类型为sysname，无默认值。返回代码值为0(成功)或非零数字(失败)。【例11.10】以下示例先创建Accounting数据库，然后将该数据库的名称更改为Financial，再查询sys.databases目录视图以确认数据库的新名称。USE masterGOCREATE DATABASE AccountingGOEXEC sp_renamedb NAccounting, NFinancialGOSELECT name, database_id, modified_dateFROM sys.databasesWH

11、ERE name = NFinancialGO6. sp_databases列出驻留在SQL Server 2005 Database Engine实例中的数据库或可以通过数据库网关访问的数据库。语法格式如下。sp_databases所返回的数据库名称可以作为USE语句的参数，用来更改当前数据库上下文。返回代码值为无。【例11.11】以下示例显示如何执行sp_databases。EXEC sp_databasesGO7. sp_tables返回可在当前环境中查询的对象列表。这些对象是可以在FROM子句中出现的任何对象。语法格式如下。sp_tables table_name = name , t

12、able_owner = owner , table_qualifier = qualifier , table_type = type , fUsePattern = fUsePattern【例11.12】返回可在master数据库中查询的对象列表。USE master EXEC sp_tablesGO【例11.13】返回有关teaching中的表的信息。以下示例返回有关teaching数据库中的dbo所拥有的表的信息。USE teachingGOEXEC sp_tables table_name = %, table_owner = dbo, table_qualifier = teach

13、ingGO8. sp_columns返回当前环境中可查询的指定表或视图的列信息。语法格式如下。sp_columns table_name = object , table_owner = owner table_name = object：用于返回目录信息的表或视图的名称。object的数据类型为 nvarchar(384)，没有默认值。支持通配符模式匹配。 table_owner = owner：用于返回目录信息的表或视图的对象所有者。owner的数据类型为nvarchar(384)，默认值是NULL。支持通配符模式匹配。如果未指定owner，则应用基础DBMS的默认表或视图可见性规则。返回

14、代码值为无。【例11.14】以下示例返回指定表course的列信息。USE teachingGOEXEC sp_columns table_name = course, table_owner = dbo11.1.4 设计存储过程1.存储过程的设计规则2.限定存储过程内的名称3.加密过程定义1 创建存储过程2 执行存储过程3 修改存储过程4 重新编译存储过程5 删除存储过程11.1.5 实现存储过程1.使用Transact-SQL命令创建存储过程10.2.1 创建存储过程创建存储过程语句的语法格式如下：CREATE PROCEDURE procedure_name ; number param

15、eter data_type VARYING =default OUTPUT , .n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION FOR REPLICATION AS sql_statement ,.n 1 创建用户定义的存储过程（1）打开SQL Server管理平台，展开节点“对象资源管理器”“数据库服务器”“可编程性”“存储过程”，在窗口的右侧显示出当前数据库的所有存储过程。单击鼠标右键，在弹出的快捷菜单中选择“新建存储过程”命令 。10.2 存储过程的创建与使用2使用图形工具创建存储过程（2）在打开的SQL命令窗口中，系统给出

16、了创建存储过程命令的模板，如图10-2所示。在模板中可以输入创建存储过程的Transact-SQL语句后，单击“执行”按钮即可创建存储过程。 10.2.1 创建存储过程（3）建立存储过程的命令被成功执行后，在“对象资源管理器”“数据库服务器”“可编程性”“存储过程”中可以看到新建立的存储过程 10.2.1 创建存储过程2 执行存储过程执行存储过程的语法格式：EXECUTE return_status= procedure_name ;number|procedure_name_var parameter=value|variable OUTPUT|DEFAULT ,.n WITH RECOMP

17、ILE 10.2 存储过程的创建与使用10.2.3 修改存储过程1.使用Transact-SQL命令修改存储过程ALTER PROCEDURE的语法规则是：ALTER PROCEDURE procedure_name ; number parameter data_typeVARYING=default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONFOR REPLICATION AS sql_statement .n 3 修改存储过程修改存储过程可以通过Transact-SQL语句和使用图形工具实现。10.2 存储

18、过程的创建与使用2使用SQL Server管理平台修改存储过程 10.2.3 修改存储过程4重新编译存储过程 在执行诸如添加索引或更改索引列中的数据等操作更改了数据库时，应重新编译访问数据库表的原始查询计划以对其重新优化。 在Microsoft SQL Server 2005重新启动后第一次运行存储过程时自动执行此优化。当存储过程使用的基础表发生变化时，也会执行此优化。但如果添加了存储过程可能从中受益的新索引，将不自动执行优化，直到下一次Microsoft SQL Server重新启动后再运行该存储过程时执行优化。在这种情况下，强制在下次执行存储过程时对其重新编译会很有用。10.2 存储过程的

19、创建与使用5 删除存储过程 存储过程可以被快速删除和重建，因为它没有存储数据。DROP PROCEDURE的语法如下：DROP PROCEDURE procedure_name ,.n 例如删除例10-2创建的存储过程employee_dep：DROP PROCEDURE employee_depGO1.使用Transact-SQL命令删除存储过程10.2.4 删除存储过程2使用SQL Server管理平台删除存储过程 1)连接到相应的Microsoft SQL Server Database Engine实例之后，在“对象资源管理器”中，单击服务器名称以展开服务器树。2)展开“数据库”，然后

20、选择用户数据库，如teaching。3)展开“可编程性”，右键单击其中要删除的存储过程如getstudent2，然后单击 “删除”触发器是一种特殊类型的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过过程名字直接调用。当对某一表进行UPDATE、INSERT、DELETE操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器的主要作用就是能够实现由主键和外键所不能保证的参照完整性和数据的一致性。 11.2 触发器概述1. DML触发器类型AFTER触发器INSTEAD OF触发器CLR触发器11.2

21、.1 DML触发器2. 触发器与约束的比较约束和DML触发器在特殊情况下各有优点。DML触发器的主要优点在于它们可以包含使用Transact-SQL代码的复杂处理逻辑。因此，DML触发器可以支持约束的所有功能；但DML触发器对于给定的功能并不总是最好的方法。11.2.1 DML触发器3. DML触发器功能比较11.2.1 DML触发器4. 实现DML触发器(1)创建DML触发器前应考虑的问题(2)多个DML触发器(3)触发器权限和所有权(4)创建DML触发器11.2.1 DML触发器10.4.1 创建触发器1. 使用Tranasct-SQL命令创建DML触发器CREATE TRIGGER语句的

22、语法格式如下：CREATE TRIGGER trigger_name ON table_name | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS sql_statement . n 10.4 触发器的创建与使用2使用图形工具创建DML触发器(4)创建触发器10.4.3 修改触发器(5).使用插入的表和删除的表(6)修改DML触发器(7)重命名DML触发器(8)禁用和启用DML触发器(9)删除DML触发器10.4 触发器的创建与使用1. DML触发器与DDL触发器的比较DDL触发器和DML触发

23、器的用处不同。DML触发器在INSERT、UPDATE和DELETE语句上操作，并且有助于在表或视图中修改数据时强制业务规则，扩展数据完整性。DDL触发器在CREATE、ALTER、DROP和其他DDL语句上操作。它们用于执行管理任务，并强制影响数据库的业务规则。它们应用于数据库或服务器中某一类型的所有命令。可以使用相似的Transact-SQL语法创建、修改和删除DML触发器和DDL触发器，它们还具有其他相似的行为。11.2.2 DDL触发器10.4 触发器的创建与使用1. DML触发器与DDL触发器的比较与DML触发器相同，DDL触发器可以运行在Microsoft .NET Framewo

24、rk中创建的以及在SQL Server中上载的程序集中打包的托管代码。与DML触发器相同，可以为同一个Transact-SQL命令创建多个DDL触发器。同时，DDL触发器和激发它的命令运行在相同的事务中。可从触发器中回滚此事务。严重错误可能会导致整个事务自动回滚。从批处理中运行并显式包含ROLLBACK TRANSACTION语句的DDL触发器将取消整个批处理。11.2.2 DDL触发器10.4 触发器的创建与使用2. 设计DDL触发器(1)确定触发器的作用域(2)指定Transact-SQL语句或语句组1)选择触发DDL触发器的特定DDL语句2)选择触发DDL触发器的一组预定义的DDL语句11.2.2 DDL触发器10.4 触发器的创建与使用3. 实现DDL触发器(1)创建DDL触发器(2)修改DDL触发器(3)禁用和启用DDL触发器(4)删除DDL触发器11.2.2 DDL触发器10.6 SQL Server的锁机制本章小结（1）存储过程是一组SQL语句和流程控制语句的集合，以一个名字存储并