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