存储过程与触发器.ppt

第九章 存储过程与触发器,9.1 存储过程的综述,9.5 系统存储过程和扩展存储过程,目录,9.2 创建、执行、修改、删除简单存储过程,9.3 创建和执行含参数的存储过程,9.4 存储过程的重新编译,9.6 案例中的存储过程,9.7 触发器综述,9.8 触发器的创建和执行,9.9 修改和删除触发器,9.10 嵌套触发器,9.11 案例中的触发器,存储过程由一组预先编辑好的SQL语句组成。将其放在服务器上，由用户通过指定存储过程的名字来执行它。触发器是一种特殊类型的存储过程，它不是由用户直接调用，而是当用户对数据进行操作（包含数据的INSERT、UPDATE或DELETE操作）时自动执行。 本章主要介绍存储过程和触发器的基本概念及其创建、修改和使用等,存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中便于以后调用，这样可以提高代码的执行效率。,操作方法。,9.1 存储的过程综述,9.1.1 存储过程的概念,9.1.2 存储过程的类型,在SQL Server中存储过程可以分为五类。即系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。,9.2 创建、执行、修改、删除简单存储过程,9.2.1 创建存储过程,（1）使用企业管理器创建存储过程,【例9.1】在Student数据库中，创建一个名称为ST_CHAXUN_01的存储过程，该存储过程返回计算机系学生的姓名、性别、出生日期信息。其操作步骤如下： （1） 打开企业管理器，展开控制台目录，依次展开服务器组、服务器、数据库节点。 （2） 单击相应的数据库（这里我们选择Student数据库）。在其右边的详细窗格中右键单击“存储过程”图标，在弹出的快捷菜单中选择“新建存储过程”命令。,（3) 执行“新建存储过程”命令，打开“存储过程属性”对话框，如图9.1所示。在其文本框中首先输入所有者和存储过程名称，例如所有者DBO替换OWNER，用过程名ST_CHAXUN_01替换PROCEDURE NAME。,(4) 在文本框的第二行输入存储过程文本，根据题意输入如下语句。 SELECT 姓名，性别，出生日期 FROM 学生 WHERE 系部代码01,(5) 输入完成后，单击“检查语法”按钮，语法检查 是否正确。,(6) 如果没有任何错误，单击“确定”按钮，将存储过程保存到数据库中。,（2）使用SQL语句创建存储过程,在查询分析器中，用SQL语句创建存储过程的语法格式如下： CREATE PROC EDURE procedure_name ;number,【例9.2】在Student数据库中，创建一个查询存储过程ST_PRO_BJ，该存储过程将返回计算机系的班级名称。其程序清单如下：,USE student GO CREATE PROCEDURE ST_PRO_BJ,AS SELECT 班级名称 FROM 班级,系部 WHERE 系部.系部代码=班级.系部代码 and 系部.系部名称=计算机 GO,9.2.2 执行存储过程,对存储在服务器上的存储过程,可以使用EXECUTE命令或其名称执行它，其语法格式如下： EXEC UTE return_status = procedure_name ;number | procedure_name_var ,parameter=value | variable OUTPUT | DEFAULT ,n WITH RECOMPLE,【例9.】在查询分析器中执行ST_PRO_BJ，其代码清单如下： USE Student EXECUTE ST_PROC_BJ GO,其执行结果如下图所示。,9.2.3 查看存储过程,对用户建立的存储过程，可以使用企业管理器或有关的系统存储过程查看该存储过程的定义。,（1）使用企业管理器查看存储过程,（2）使用系统存储过程查看存储过程,9.2.4 修改存储过程,当存储过程所依赖的基本表发生变化或者根据需要，用户可以对存储过程的定义或者参数进行修改。更改通过执行CREATE PROCEDURE语句创建的过程，不会更改权限，也不影响相关的存储过程或触发器。修改存储过程可以使用ALTER PROCEDURE语句，其语法格式为：,ALTER PROC EDURE procedure_name ; number parameter data_type VARYING =default OUTPUT ,n WITH,RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION FOR REPLICATION AS Sql_statement ,n,【例9.】 修改存储过程ST_PRO_BJ，使该存储过程返回经济管理系的班级名称。其程序清单如下： USE student,GO ALTER PROC DBO.ST_PROC_BJ AS SELECT 班级名称 FROM 班级,系部 WHERE 系部.系部代码=班级.系部代码 and 系部.系部名称=经济管理系 GO,9.2.5 删除存储过程,当存储过程不再需要时，可以使用企业管理器或DROP PROCEDURE语句将其删除。,()使用企业管理器删除存储过程,在企业管理器中，用鼠标右键单击要删除的存储过程，从弹出的快捷菜单中选择“删除”命令，将弹出“移除对象”对话框，在次对话框中，单击“全部移出”按钮，删除该存储过程。,(2)使用DROP PROCEDURE语句删除存储过程,DROP PROCEDURE语句可以一次从当前数据库中将一个或多个存储过程或过程组删除，其语法格式如下： DROP PROCEDURE 存储过程名，n,【例9.】 删除存储过程ST_CHAXUN_01，其程序清单如下： USE student GO DROP PROCEDURE ST_CHAXUN_01 GO,9.3 创建含参数的存储过程,在存储过程中使用参数，可以扩展存储过程的功能。使用输入参数，可以将外部信息传到存储过程；使用输出参数，可以将存储过程内的信息传到外部。,【例9.】在Student数据库中，建立一个名为XIBU_INFOR的存储过程，它带有一个参数，用于接受系部代码，显示该系部名称和系主任信息。其程序清单如下： USE student,GO CREATE PROCEDURE XIBU_INFOR 系部代码 CHAR (2) AS SELECT系部名称, 系主任 FROM 系部 WHERE 系部代码=系部代码 GO,9.4 存储过程重新编译,1.在建立存储过程时设定重新编译,其语法格式如下： CREAT PROCEDURE procedure_name WITH RECOMPLE AS sql_statement,2.在执行存储过程时设定重新编译,其语法格式如下： EXECTUE procedure_name WITH RECOMPILE,3.通过使用系统存储过程设定重新编译,其语法格式如下： EXEC sp_recompile OBJECT,【例9.】利用sp_recompile命令为存储过程ST_PROC_BJ设定重编译标记。 在查询分析器中执行代码： EXEC sp_recompile ST_PROC_BJ GO 运行后提示：“已成功地将对象 ST_PROC_BJ标记为重新编译。”,在SQL Server中有两类重要的存储过程：系统存储过程和扩展存储过程。这些存储过程为用户管理数据库、获取系统信息、查看系统对象提供了很大的帮助。下面分别对两类存储过程做简单的介绍。,9.5 存储过程与扩展存储过程,9.5.1 系统存储过程,在SQL Server中存在两百多个系统存储过程，这些系统存储过程的使用，使用户很容易地管理SQL Server的数据库。在安装SQL Server数据库系统时，系统存储过程被系统安装在master数据库中，并且初始化状态只有系统管理员拥有使用权。所有的系统存储过程名称都是以sp_开头。,【例9.9】利用sp_addgroup命令在当前数据库中建立一个角色user_group。执行如下代码： USE master,GO EXEC sp_addgroup user_group,【例9.10】利用sp_addlogin命令建立一个登录用户名为user01。执行如下代码： USE master GO EXEC sp_addlogin user01,【例9.11】利用sp_addtype命令创建新的用户自定义数据库类型user_date ，该类型为datetime数据类型。,执行如下代码： EXEC sp_addtype user_date, datetime 运行结果为类型已添加。,【例9.12】使用SP_monitor显示CPU,I/O的使用信息 。 在查询分析器中输入如下代码： USE master GO EXEC SP_monitor GO,返回如下图所示的结果集，该结果报告了当时有关SQL Server繁忙程度的信息。,9.5.2 扩展存储过程,【例9.13】使用sp_addextendproc存储过程将一个编写好的扩展存储过程xp_userprint.dll注册到SQL Server中。 执行代码如下： EXEC sp_addextendproc xp_userprint, xp_userprint.dll,【例9.14】使用存储过程xp_dirtree返回本地操作系统的系统目录”C:winnt”目录树。 执行代码如下：,EXEC xp_dirtree ”C:winnt” 执行结果返回目录树。,【例9.15】利用扩展存储过程xp_cmdshell为一个操作系统外壳执行指定命令串，并作为文本返回任何输出。 执行代码如下： EXEC master xp_cmdshell “dir *.exe” GO 执行结果返回系统目录下的文件内容文本信息。,9.6 案例中的存储过程,1. 创建一个查询存储过程,USE student GO 如果存储过程TEACHER存在，将其删除 IF EXISTS(SELECT NAME FROME SYSOBJECTS WHERE NANE=TEACHER AND TYPE=P) DROP PROCEDURE TEACHER GO,建立一个查询存储过程 CREATE PROCEDURE TEACHER 查询选项 WITH ENCRYPTION AS SELECT 姓名,职称 FROM 教师,系部 WHERE 系部.系部代码=教师.系部代码 and 系部.系部名称=计算机 GO,执行TEACHER EXEC TEACHER GO,2. 创建带输入参数的存储过程,USE student GO 如果存储过程教师查询存在，将其删除 IF EXISTS(SELECT NAME FROME SYSOBJECTS WHERE NANE=教师查询 AND TYPE=P) DROP PROCEDURE教师查询 GO 创建一个带参数的存储过程 教师查询,CREATE PROCEDURE 教师查询 XIBIE CHAR(8) 查询选项 WITH ENCRYPTION AS SELECT 教师.姓名,教师.职称,教师.职务 FROM 教师,系部 WHERE 系部.系部代码=教师.系部代码 and 系部.系部名称=XIBIE,ORDER BY 教师.教师编号 GO 执行存储过程，并向存储过程传递参数。 EXEC教师查询 计算机 GO,3. 创建带输出参数的存储过程,USE Student GO 如果存储过程单科成绩分析存在，将其删除 IF EXISTS(SELECT NAME FROME SYSOBJECTS WHERE NANE= 单科成绩分析 AND TYPE=P) DROP PROCEDURE单科成绩分析 GO 创建存储过程 单科成绩分析,定义一个输入参数KECHENGMING 定义三个输出参数 AVGCHENGJI,MAXCHENGJI和MINCHENGJI,用于接受平均成绩，最高成绩和最低成绩 CREATE PROCEDURE 单科成绩分析 KECHENGMING VARCHAR(20) AVGCHENGJI tinyint OUTPUT, MAXCHENGJI tinyint OUTPUT, MINCHENGJI tinyint OUTPUT, AS,SELECT AVGCHENGJI=AVG(成绩)，MAXCHENGJI=MAX (成绩)，,MINCHENGJI=MIN (成绩) FROM 课程注册 WHERE 课程号 (SELECT课程号 FROM 课程 WHERE 课程名=KECHENGMING) GO USE STUDENT 声明四个变量，用于保存输入和输出参数 DECLARE KECHENGMING VARCHAR(20),DECLARE AVGCHENGJI tinyint OUTPUT DECLARE MAXCHENGJI tinyint OUTPUT DECLARE MINCHENGJI tinyint OUTPUT 为输出参数赋值 SELECT KECHENGMING=计算机 执行存储过程 EXEC成绩查询 1 KECHENGMING， AVGCHENGJI1 OUTPUT, MAXCHENGJI1 OUTPUT,MINCHENGJI1 OUTPUT 显示结果 SELECT KECHENGMING AS课程名, AVGCHENGJI1 AS 平均成绩, MAXCHENGJI 1 AS最高成绩, MINCHENGJI 1 AS最低成绩 GO,9.7 触发器综述,9.7.1 触发器的概念,触发器是一种特殊类型的存储过程，它也是由T-SQL语句组成，可以完成存储过程能完成的功能，但是它具有自己的显著的特点：它与表紧密相连，可以看做表定义的一部分；它不可能通过名称被直接调用，更不允许参数，而是当用户对表中的数据进行修改时，自动执行；它可以用于,SQL Server约束，默认值和规则的完整性检查，实施更为复杂的数据完整性约束。,9.7.2 触发器的优点,触发器包含复杂的处理逻辑，能够实现复杂的完整性约束。同其他约束相比，它主要有以下优点：,（1） 触发器自动执行。在对表中的数据做了任何修改（如手工输入或者通过应用程序实现的修改）之后立即被激活。 （2） 触发器能够对数据库中的相关表实现级联更改。触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。例如，可以在产品表的产品编号字段上建立一个插入触发器，当对产品表增加记录时，在产品销售表的产品编号上自动插入产品编号值。,(3)触发器可以实现比CHECK约束更为复杂的数据完整性约束。在数据库中为了实现数据完整性约束，可以使用CHECK约束或触发器。CHECK约束不允许引用其他表中的列来完成检查工作，而触发器可以引用其他表中的列。例如，在Student数据库中，向学生表中插入记录时，当输入系部代码时，必须先检查系部表中是否存在该系。这只能通过触发器实现，而不能通过CHECK约束完成。 (4)触发器可以评估数据修改前后的表的,状态，并根据其差异采取对策。 (5)一个表中可以同时存在三个不同操作的触发器（INSERT，UPDATE或DELETE），对于同一个修改语句可以有多个不同的对策以响应。,9.7.3 触发器的种类,SQL Server 2000按触发被激活的时机可以分为两种类型：AFTER触发器和INSTEAD OF触发器。 AFTER触发器又称为后触发器，该类,触发器是在引起触发器执行的修改语句成功完成之后执行。如果修改语句因错误（如违反约束或语法错误）而执行失败，触发器将不会执行。此类触发器只能定义在表上，不能创建在视图上。可以为每个触发器操作（INSERT，UPDATE或DELETE）创建多个AFTER触发器。如果表有多个AFTER触发器，可使用sp_settriggerorder定义哪个AFTER触发器最先激发，哪个最后激发。除第一个和最后一个触发器外，所有其他的AFTER触发器的激发顺序不确定，并且无法控制。,INSTEAD OF触发器又称为替代触发器，当引起触发器执行的修改语句停止时，该类触发器替代触发操作执行。该类触发器既可在表上定义，也可在视图上定义。对于每个该类触发操作（INSERT，UPDATE或DELETE），只能定义一个INSTEAD OF触发器。,9.8 触发器的创建和执行,（1）使用企业管理器创建触发器,9.8.1 创建触发器,（2）使用SQL语句创建触发器,【例9.18】在Student数据库中，为“产品”表中产品编号建立一个名为del_xiaoshou的DELETE触发器，其作用是当删除“产品”表中的记录时，同时删除“产品销售”表中与“产品”表相关的记录。其程序清单如下： USE Student GO CREATE TRIGGER del_xiaoshou ON dbo.产品 FOR DELETE,AS DELETE 产品销售WHETE 产品编号 IN (SELECT 产品编号) FROM DELETED GO,9.8.2 查看触发器,(1)使用系统存储过程查看触发器信息,【例9.19】 使用系统存储过程sp_helptrigger查看“产品”表上存在的触发器的信息。其程序清单如下： USE student GO EXEC SP_helptrigger 产品 GO,(2)使用企业管理器查看触发器信息,（1）使用系统存储过程修改触发器名称,9.9 修改和删除触发器,9.9.1 修改触发器,对触发器进行重命名，可以使用系统存储过程sp_rename来完成，其语法格式如下： EXECUTE sp_rename 触发器原名，触发器新名,（2）使用企业管理器修改触发器文本,使用企业管理器修改触发器的操作步骤与创建触发器相似，只不过在打开“触发器” 对话框后，从名称对话框中选择需要修改的触发器，然后对文本中的SQL语句进行修改即可。修改完后，使用“语法检查”选项来验证语法是否正确。最后，单击“确定”按钮，完成触发器的修改。,（3）使用SQL语句修改触发器,【例9.21】 删除“产品”表中产品编号为“0001”的记录，观察触发器del_xiaoshou的作用。其程序清单如下： USE student,GO DELETE FROM 产品 WHERE 产品编号=0001 GO 在查询分析器中运行，其结果如下图所示。,9.9.3 删除触发器,其语法格式为： ALTER TABLE 表名 ENABLE|DISABLE 触发器名称,9.9.2 禁止或启用触发器,9.10 嵌套触发器,1.使用系统存储过程改变嵌套,使用sp_config系统存储过程设置是否允许嵌套的语法格式如下： EXEC sp_configure nested trigger,0|1,2.使用企业管理器设置嵌套,9.11 案例中的触发器,1.创建一个INSERT触发器,USE Student