资源预览内容
第1页 / 共55页
第2页 / 共55页
第3页 / 共55页
第4页 / 共55页
第5页 / 共55页
第6页 / 共55页
第7页 / 共55页
第8页 / 共55页
第9页 / 共55页
第10页 / 共55页
亲,该文档总共55页,到这儿已超出免费预览范围,如果喜欢就下载吧!
资源描述
第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)如果一个
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号