第12章 存储过程与触发器,12.1 了解存储过程 12.2 创建存储过程 12.3 执行存储过程 12.4 修改存储过程 12.6 查看存储过程 12.7 了解触发器 12.8 创建触发器 12.9 修改触发器 12.10 删除触发器 12.11 查询触发器信息,存储过程与触发器是SQL Server中的两类数据库对象。它们都是由T-SQL语句编写而成的过程，所不同的是存储过程是由用户根据需要调用执行的，而触发器则是由某个动作（如删除或修改一条记录）引发执行的。另外，存储过程可以不依附于表而单独存在，而触发器则必须依附于一个特定的表。它们与函数也不同，函数可以直接通过函数名返回数值，其返回值可以直接在表达式中使用，而存储过程与触发器则不能直接通过其名称带回返回值，也不能直接在表达式中使用。,12.1 了解存储过程,在SQL Server中定义的过程被称为存储过程。存储过程是一组预先编译好的T-SQL代码,作为一个整体用于执行特定的操作。存储过程属于数据库对象，它们存放在数据库中，需要时用户可以调用。,SQL Server的存储过程分为两大类： 1系统存储过程 2用户定义的存储过程 对用户定义的存储过程，可以依据不同的分类标准予以分类。 （1）临时存储过程。 （2）本地存储过程。 （3）远程存储过程。 （4）扩展存储过程。,12.2 创建存储过程,12.2.1 使用T-SQL语句创建存储过程 在SQL Server中，使用CREATE PROCEDURE语句创建一个永久或临时存储过程。,命令格式： CREATE PROCEDURE procedure_name ; number parameter data_type = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE ,ENCRYPTION AS sql_statement .n ,命令说明： （1）procedure_name ：所要创建的存储过程的名称。该名称必须符合标识符规则，且对于数据库及其所有者该名称必须惟一。建议不要以sp_开头以免与系统存储过程混淆。如果创建的是临时存储过程，则必须以或开头。procedure_name之前可以指定存储过程的所有者，但不必加上数据库名，因为所有用户创建的存储过程都是存放在当前数据库中的。,（2）; number ：表示存储过程的下标。当希望创建一组同名的存储过程时，该数字表示该组中各个存储过程的下标（相当于数组的下标）。用一条 DROP PROCEDURE 语句可以将同组的所有存储过程一起删去。如果存储过程名称中包含界定符，则该数字应放在界定符外。,（3）parameter data_type ：指定存储过程要使用的参数及其数据类型。存储过程参数的首字符是，无OUTPUT选项表示参数为输入参数，有OUTPUT选项表示参数为输出参数。 （4）default ：表示存储过程所用参数的默认值，必须是常量或 NULL。如果定义了默认值，执行存储过程时可以不必给出参数值。如果存储过程将对该参数使用 LIKE 关键字，则默认值中可以包含通配符。,（5）RECOMPILE：使用该选项表示存储过程在每次运行时都将重新编译。 （6）ENCRYPTION：使用该选项表示对syscomments 表中包含 CREATE PROCEDURE 语句文本的条目进行加密，以防止将存储过程作为 SQL Server 复制的一部分发布。 （7）sql_statement ：指定存储过程要执行的SQL操作语句。,12.2.2 使用企业管理器创建存储过程,12.3 执行存储过程,命令格式： EXEC UTE return_status = procedure_name ;number | procedure_name_var parameter = value | variable OUTPUT | DEFAULT ,.n ,命令说明： （1）EXECUTE：如果对存储过程的调用是批处理中的第一条语句，可以直接使用存储过程的名称。如果对存储过程的调用不是批处理中的第一条语句，应使用该关键字, 该关键字可以略写为EXEC。,（2）return_status：是一个以开头的备选整型变量名称，用于保存存储过程的返回状态值（该状态值在定义存储过程的SELECT语句块中由RETURN返回）。这个变量必须在 EXECUTE 语句之前已经声明过。当需要从存储过程中返回执行状态时，必须使用该形式（见例12-10）。,（3）procedure_name ;number ：是拟调用的存储过程名称，如果是扩展存储过程，则其名称要区分大小写。 （4）procedure_name_var ：是用于保存存储过程名称的局部变量名称。,（5）parameter ：是调用存储过程时的参数名称，应该与定义存储过程时的参数名称相同。后面有OUTPUT时是存储过程的输出参数，没有OUTPUT时是存储过程的输入参数。,（6）value ：是调用存储过程时参数parameter的取值。如果参数名称parameter没有指定，则参数值必须按照 CREATE PROCEDURE 语句中定义的顺序给出。如果参数值是一个对象名称或字符串，则必须用英文单引号括起来。如果参数值是一个关键字，则必须用双引号括起来。如果在 CREATE PROCEDURE 语句中已经定义了默认值（可以为 NULL），则用户执行该过程时可以不指定参数。如果该过程使用了带 LIKE 关键字的参数名称，则默认值必须是常量，并且可以包含通配符%、_、 及 。,（7）variable OUTPUT ：是保存参数返回值的变量名称。有OUTPUT选项时，存储过程必须返回一个参数值给这个变量。有OUTPUT选项时，该变量所对应的参数必须在创建存储过程时有相应的OUTPUT定义。 （8）DEFAULT：应该与创建存储过程时的参数默认值一致。,12.4 修改存储过程,存储过程建立完毕后，可以根据需要修改其名称、参数和T-SQL语句。 12.4.1 更改存储过程名称 只有数据库所有者和存储过程的所有者才有权修改存储过程名称。,12.4.2 修改存储过程的参数与定义 1使用T-SQL语句修改存储过程 命令格式： ALTER PROC EDURE procedure_name ; number parameter data_type = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION AS sql_statement .n ,命令说明： 其中各项语法含义与创建存储过程类似。 2使用企业管理器修改存储过程,12.5 删除存储过程,不再需要存储过程时，可以利用企业管理器或T-SQL语言删除。 12.5.1 使用T-SQL语言删除存储过程 使用T-SQL语言从当前数据库中删除一个或多个存储过程的语法如下。,命令格式： DROP PROCEDURE procedure_name ,.n 命令说明： procedure_name是存储过程名称或存储过程组名称。注意删除存储过程组时不必写出下标，即可将组中所有成员全部删除。 12.5.2 使用企业管理器删除存储过程,12.6 查看存储过程,12.6.1 使用T-SQL语言查看存储过程的信息 （1）查看定义存储过程的文本： sp_helptext objname = procedure_name,（2）查看存储过程的名称、所有者、建立时间： sp_help objname = procedure_name （3）查看存储过程所引用的表（或查看一个表被哪些存储过程所引用）： sp_depends objname = table_name,（4）查看一个存储过程引用了哪些表及其列： sp_depends objname = procedure_name 12.6.2 使用企业管理器查看存储过程的信息,12.7 了解触发器,SQL Server 2000 提供了两种主要机制用于维护数据的完整性：一种是第10章介绍的约束，另一种就是本章将要介绍的触发器。触发器虽然是一种特殊的存储过程，但是它与表却是紧密联系的，离开了表它将不复存在（这点与约束十分类似）。,触发器又不像存储过程那样需要调用才能执行，而是在对表中数据进行增、删、改（INSERT、UPDATE、 DELETE）操作时被触发而执行。触发器可以包含复杂的 T-SQL 语句。在触发器内设置回滚，可以将触发它的语句所在的事务全部回滚，这样检测到错误时可以回滚事务以撤销错误。,12.7.1 触发器执行过程中的两个表 触发器是在表上建立的，我们将触发器所依附的表称为触发器表。当修改表的数据而引发了触发器时，触发器将执行一系列T-SQL命令，在执行这些动作之前系统首先自动建立两个表，Deleted表与Inserted表。这两个表临时驻留在内存当中，其结构与触发器表完全相同，一旦触发器执行完毕，两个表也随之消失。,可以使用Deleted表与Inserted表测试某些数据修改的效果以及设置触发器操作的条件，但不能直接修改这两个表中的数据。 Deleted表存放由于执行DELETE或UPDATE操作时从触发器表（被删除数据的表）中删除的数据行，即触发器表中需要删除的数据将被移到Deleted表中。,Inserted表用于存放执行INSERT或UPDATE操作时向触发器表（被插入数据的表）中插入的数据行，即新的数据行被同时插入到两个表触发器表和Inserted表中 。 UPDATE操作相当于先执行DELETE操作，然后再执行INSERT操作，因此要用到Deleted与Inserted两个表。,12.7.2 SQL Server 2000的两类触发器 SQL Server 2000中可以建立两类触发器，一类是在触发后将执行一组新的T-SQL语句用以代替引起触发器执行的语句（原有语句没有执行，仅执行新语句），称之为INSTEAD OF触发器；另一类是在引起触发器执行的语句执行完毕后，再执行触发器中的语句，称之为AFTER类触发器。,（1）INSTEAD OF触发器既可以在表上定义，也可以在视图上定义。 （2）AFTER触发器只能在表上定义。,12.8 创建触发器,创建触发器之前要注意如下几点： （1）创建触发器所使用的语句CREATE TRIGGER必须是批处理中的第一个语句。,（2）只有表的所有者、sysadmin 固定服务器角色成员以及 db_owner 和 db_ddladmin 固定数据库角色成员有权在本表上创建触发器，且不能将该权限授予其他用户。 （3）触发器为数据库对象，其命名规则必须与标识符命名规则一致。,（4）只能在当前数据库中创建触发器，但是触发器可以引用其他数据库的对象。 （5）不能在临时表或系统表上创建触发器。触发器可以引用临时表，但不能引用系统表。 （6）尽管TRUNCATE TABLE 语句用于删除表中所有记录，但由于它不写入日志，故不能引发 DELETE 触发器。,（7）WRITETEXT语句不会引发INSERT或UPDATE触发器。 （8）如果一个触发器中含有回滚事务语句RULLBACK TRANSACTION，且引发触发器的语句位于一个事务中，则该触发器触发时将回滚事务。,（9）在触发器内不能使用的命令有：CREATE、ALTER、GRANT 、REVOKE 、TRUNCATE TABLE、DROP等。 （10）如果一个