存储过程与触发器节.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,8.6,节 存储过程与触发器,8.6.1,存储过程的基本概念,8.6.2,创建存储过程,8.6.3,执行存储过程,8.6.4,管理存储过程,8.6.5,触发器的基本概念,8.6.6,创建触发器,8.6.7,管理触发器,8.6.1,存储过程的基本概念,在使用,Transact-SQL,语言编程的过程中，可以将某些多,次调用以实现某个特定任务的代码段编写成一个过程，,将其保存在数据库中，并由,SQL Server,服务器通过过程,名调用它们，这些过程就叫做,存储过程,。,存储过程分为系统存储过程和用户自定义的存储过程。系统存储过程由,SQL Server 2000,提供，用户可以直接使用。用户自定义存储过程需要用户自己创建和维护。,系统存储过程使用,“,sp_,”,作为前缀,。,存储过程的优点：,可以在单个存储过程中执行一系列,SQL,语句。,可以从自己的存储过程内引用其它存储过程，这可,以简化一系列复杂语句。,存储过程在创建时即在服务器上进行编译，所以执行起来比单个,SQL,语句快。,存储过程一旦执行一次后，其执行的计划就会驻留在计算机的高速缓冲存储器中。其后对同一个存储过程的调用就可以直接利用编译后在高速缓存中的二进制形式来完成操作。,8.6.2,创建存储过程,在,SQL-Server,中，可以使用三种方法创建存储过程：,使用向导创建存储过程。,使用,SQL-Server,企业管理器创建存储过程。,使用,Transact-SQL,语句中的,Create Procedure,命令创建存储过程,。,默认情况下，创建存储过程的许可权归属数据库的所有者，数据库的所有者可以授权给其他用户。,8.6.2.1,使用向导创建存储过程,（,1,）在企业管理器中选中某个,SQL-Server,服务器中的数据库，这里选中,scdb,数据库。选择工具菜单中的,“,向导,”,菜单项，系统会弹出,“,选择向导,”,对话框。选中,“,创建存储过程向导,”,选项。,（,2,）单击,“,确定,”,按钮，出现,“,欢迎使用创建存储过程向导,”,对话框。,（,3,）单击,“,下一步,”,按钮，出现,“,选择数据库,”,对话框，该对话框用于选择创建存储过程中使用的数据库。,（,4,）选择数据库，单击,“,下一步,”,按钮，出现,“,选择操作对象,”,对话框，在该对话框中，列出了所有可选择的表，以及可以对表进行的数据库操作，即插入删除和更新。,图,9-4,“,选择操作对象,”,对话框,（,5,）单击,“,下一步,”,按钮，出现确认存储过程信息对话框。,8.6.2.2,使用企业管理器创建存储过程,（,1,）在企业管理器中选中某个,SQL-Server,服务器中的数据库，这里选中,scdb,数据库。右键单击数据库下的,“,存储过程,”,选项，弹出快捷菜单，,（,2,）在快捷菜单中选择,“,新建存储过程,”,命令出现,“,新建存储过程,”,对话框。,（,3,）在,“,新建存储过程,”,对话框的,“,文本,”,列表框中输入存储过程名称和程序语句,CREATE PROCEDURE query_proc,AS,Select*from sc,存储过程,query_proc,完成的功能是从,sc,表中查询全部数据。,（,4,）单击,“,检查语法,”,按钮，执行语法正确性检验。,（,5,）单击,“,确定,”,按钮，返回企业管理器窗口，可以看到所创建的存储过程。,8.6.2.3,使用,Transact-SQL,语句创建存储过程,CREATE PROC EDURE,procedure_name,parameter data_type,VARYING =default OUTPUT ,.n,WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION ,AS,sql_statement,procedure_name,：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。,parameter,：过程中的参数。,data_type,：参数的数据类型。,Default,：参数的默认值。,OUTPUT,：表明参数是返回参数。,AS,：指定过程要执行的操作。,sql_statement,：过程中要包含的任意数目和类型的,Transact-SQL,语句。,例：不带有参数的存储过程,下面的存储过程从,scdb,数据库的三个表中返回学生选课情况，结果包括学号、姓名、课程名、成绩。该存储过程不使用任何参数。,use zzz,GO,CREATE PROCEDURE proc1,AS,SELECT s.sno,sname,cname,score,FROM s,c,sc,where s.sno=sc.sno and o=o,go,exec proc1,例：带有参数的存储过程,设计一个带有参数的存储过程，该参数用于传递查询成绩的范围，根据该参数检索出符合该分数段的学生选课信息。,use zzz,GO,CREATE PROCEDURE proc2(low int,high int),AS,SELECT s.sno,sname,cname,score,FROM s,c,sc,where s.sno=sc.sno and o=o and(score between low and high),go,8.6.3,执行存储过程,EXEC UTE ,return_status=,procedure_name,parameter=value|variable OUTPUT|DEFAULT ,.n,WITH RECOMPILE,EXEC UTE,：为执行存储过程的关键字。如果所执行存储过程语句为批处理中的第一个语句时，则可以省略,EXECUTE,关键字。,return_status,：是一个可选的整型变量，保存存储过程的返回状态。,parameter,：是过程参数，在,CREATE PROCEDURE,语句中定义。,Value,：是过程中参数的值。,variable,：是用来保存参数或者返回参数的变量。,OUTPUT,：指定存储过程必须返回一个参数。,DEFAULT,：根据过程的定义，提供参数的默认值。,1,不带参数的存储过程的调用：,USE zzz,EXECUTE proc1,2,带参数的存储过程的调用：,Exec proc2 75,，,85,或者,exec proc2 low=75,high=85,3,自动执行的存储过程,可以通过调用一个系统存储过程来设置一个存储过程为自动执行的存储过程。这样的存储过程可以在,SQL server,启动时自动执行。这个系统存储过程是,sp_procoption,其调用语法格式为：,sp_procoption ProcName=procedure,OptionName=option,OptionValue=value,ProcName=procedure,：是要为其设置或查看选项的过程名。,OptionName=option,：要设置的选项的名称。,option,的唯一值是,startup,，该值设置存储过程的自动执行状态。设置为自动执行的存储过程会在每次,Microsoft SQL Server,启动时运行。,OptionValue=value,：表示选项是设置为开（,true,或,on,）还是关（,false,或,off,）。,8.6.4,管理存储过程,8.6.4.1,查看存储过程,创建存储过程之后，它的名字就存储在系统表,sysobjects,中，它的源代码存放在系统表,syscomments,中。可以使用企业管理器或系统存储过程查看用户创建的存储过程。,1,使用企业管理器查看用户创建的存储过程,在企业管理器中，打开指定的服务器和数据库项，并单击,scdb,中的,“,存储过程,”,项，此时在右边的窗口中显示出,scdb,数据库中的所有存储过程。,右键单击要查看的存储过程，从弹出的快捷菜单中选择,“,属性,”,选项，弹出,“,存储过程属性,”,选项。或者左键双击要查看的存储过程，也可以弹出,“,存储过程属性,”,选项。在此对话框中能够看到存储过程的源代码。,2,使用系统存储过程查看用户创建的存储过程,sp_help,sp_helptext,sp_depends,sp_stored_procedures,8.6.4.2,修改存储过程,1,使用企业管理器修改存储过程,2,使用,Transact-SQL,语句修改存储过程,ALTER PROC EDURE procedure_name,parameter data_type,VARYING =default OUTPUT,.n,WITH,RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION,AS,sql_statement,THANK YOU,SUCCESS,2025/1/15 周三,26,可编辑,8.6.4.3,重命名存储过程,1,使用企业管理器重命名存储过程名称,在企业管理器中，右键单击要更名的存储过程，从弹出的快捷菜单中选择,“,重命名,”,选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称了。,2,使用系统存储过程重命名存储过程名称,sp_rename old_name,，,new_name,例如：,USE zzz,GO,sp_rename proc1,proc3,GO,8.6.4.4,删除存储过程,1,使用企业管理器删除存储过程,在企业管理器中，右键单击要更名的存储过程，从弹出的快捷菜单中选择,“,删除,”,选项，弹出,“,除去对象,”,对话框，在该对话框中，单击,“,全部除去,”,按钮，即可完成删除操作。,2,使用,Transact-SQL,语句删除存储过程,DROP PROCEDURE procedureNmae ,.n,ProcedureName,是要删除的存储过程或存储过程组的名称。,例如：,USE sales,GO,DROP PRODEDURE proc1,GO,8.6.5,触发器概述,8.6.5.1,触发器的概念,触发器（,trigger,）是一种特殊的存储过程，它不同于一般的存储过程。一般的存储过程通过存储过程名称被直接调用，,而触发器主要是通过事件进行触发而被执行。,触发器是一个功能强大的工具，与表紧密连接，可以看作是表格定义的一部分。当用户修改（,INSERT,、,UPDATE,或,DELETE,）指定表或视图中的数据时，该表中的相应的触发器就会自动执行。,通常可以在触发器内编写一段自动执行的程序，用于保证数据操作的完整性，从而扩展了对默认值、约束和规则的完整性检查。,8.6.5.2,触发器的优点,1,触发器是自动执行的，当用户对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。,2,触发器可以通过数据库中的相关表进行层叠更改，实现多个表之间数据的一致性和完整性。,3,触发器可以强制限制，这些限制比用,CHECK,约束所定义的更复杂。,8.6.5.3,触发器的分类,AFTER,触发器：又称后触发器（,After Trigger,）,这种类型的触发器将在数据变动（,UPDATE,、,INSERT,和,DELETE,操作）完成后才被触发。,指定,AFTER,与指定,FOR,相同。,AFTER,触发器只能在表上定义。,在同一个数据表中可以创建多个,AFTER,触发器。,Server2000,默认的为,AFTER,触发器。,INSTEAD OF,触发器：,又称前触发器（,Instead Of Trigger,）,INSTEAD OF,触发器在数据变动以前被触发，并取代变动数据的操作（,UPDATE,、,INSERT,和,DELETE,操作），而去执行触发器定义的操作。,INSTEAD OF,触发器可以在表或视图上定义。,在表或视图上，每个,UPDATE,、,INSERT,和,DELETE,语句最多可以定义一个,INSTEAD OF,触发器。,deleted,表和,inserted,表,deleted,表用于存储,DELETE,和,UPDATE,语句所影响的行的备份。在执行,DELETE,或,UPDATE,语句时，行从基本表中删除，并转移到,deleted,表中。,deleted,表和基本表通常没有相同的行。,inserted,表用于存储,INSERT,和,UPDATE,语句所影响的行的备份。在一个插入或更新事务处理中，新建行被同时添加到,inserted,表和基本表中。,inserted,表中的行是基本表中新行的备份。,8.6.6,创建触发器,创建触发器前应考虑下列问题：,CREATE TRIGGER,语句必须是批处理中的第一个语句。,创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。,触发器为数据库对象，其名称必须遵循标识符的命名规则。,虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。,虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。,在含有用,DELETE,或,UPDATE,操作定义的外键的表中，不能定义,INSTEAD OF DELETE,和,INSTEAD OF UPDATE,触发器。,虽然,TRUNCATE TABLE,语句类似于没有,WHERE,子句（用于删除行）的,DELETE,语句，但它并不会引发,DELETE,触发器，因为,TRUNCATE TABLE,语句没有记录。,创建触发器时需指定：,触发器名称。,在其上定义触发器的表。,触发器将何时激发。,激活触发器的数据修改语句。有效选项为,INSERT,、,UPDATE,或,DELETE,。多个数据修改语句可激活同一个触发器。,执行触发操作的编程语句。,8.6.6.1,使用,SQL-Server,企业管理器创建触发器,打开企业管理器，在控制台根目录下依次展开服务器组、服务器节点、数据库节点。,选择某一个数据库，并双击,【,表,】,节点，在右侧窗口的列表中选择其中的一张表,sc,表。,右击所选中的表，在弹出的下拉菜单中选择,【,所有任务,】,，并继续选择级联菜单中的,【,管理触发器,】,命令，将打开,【,触发器属性,】,对话框。,在,【,触发器属性,】,对话框的文本框中，将,TRIGGER NAME,修改为新建触发器的,名称，并输入触发器所包含的,SQL,语句。,通过点击,【,检查语法,】,按钮来判断触发器的语法是否正确，最后，点击,【,确定,】,按,钮保存新建的触发器。,（,6,）单击,“,应用,”,按钮，在名称下拉列表中出现新创建的,TR_UpdateSalers,触发器的名称，单击,“,确定,”,按钮，即可关闭该对话框，成功创建触发器。,8.6.6.2,使用,Transact-SQL,语句创建触发器,CREATE TRIGGER,trigger_name,ON,table|view,WITH ENCRYPTION,FOR|AFTER|INSTEAD OF INSERT ,UPDATE ,WITH APPEND,NOT FOR REPLICATION,AS,IF UPDATE(column),AND|OR UPDATE(column),.n,sql_statement .n ,trigger_name,：是触发器的名称。,Table|view,：是在其上执行触发器的表或视图。,AFTER,：指定触发器只有在触发,SQL,语句中指定的所有操作都已成功执行后才激发。如果仅指定,FOR,关键字，则,AFTER,是默认设置。,INSTEAD OF,：指定执行触发器而不是执行触发,SQL,语句，从而替代触发语句的操作。,AS,：是触发器要执行的操作。,sql_statement,：是触发器的条件和操作。,IF UPDATE(column),：测试在指定的列上进行的,INSERT,或,UPDATE,操作，不能用于,DELETE,操作。,【,例,】,在数据库,SCDB,中设计一个触发器，该触发器的作用为：当在,s,表中删除某一个学生时，在,sc,表中有关该学生的选课信息也全部删除。,提示：在此例中，由于涉及到了对学生表删除操作，因而需要设计一个,DELETE,类型的触发器。,在查询分析器中运行如下命令：,USE scdb,GOCREATE TRIGGER del_student ON S,AFTER DELETEAS DELETE FROM sc WHERE sno IN(SELECT sno FROM,DELETED,),Print,删除表,sc,中的相关记录条数为,+str(rowcount)+,条,GO,【,例,】,在数据库,scdb,中设计一个触发器，该触发器能够保证在,sc,表中添加新的记录时，学生的学号必须已经存在于,s,表中。,提示：设计该触发器有助于实现学生信息的完整性。在此例中由于涉及到了,sc,表中的添加操作，因而需要设计一个,INSERT,类型的触发器。在查询分析器中运行如下命令：,USE xscj,GO,CREATE TRIGGER insert_SC ON SC,AFTER INSERT,AS,IF EXISTS,(SELECT*FROM INSERTED WHERE sno IN(SELECT sno FROM s),PRINT,添加成功！,ELSE,BEGIN,PRINT,添加的学生不存在！,ROLLBACK TRANSACTION,END,创建了触发器,insert_sc,之后，我们可以添加新的学生纪录进行测试，例如：,insert into sc values(,10,C1,80),由于,S,表中不存在编号为,10,的学生，因而添加操作将会被取消。,ROLLBACK TRANSACTION,用于回滚已经完成的操作。运行结果如图所示。,8.6.3,修改与删除触发器,1,使用企业管理器修改触发器,在控制台根目录下依次展开服务器组、服务器节点、数据库节点。选择某一个数据库，并双击,【,表,】,节点，在右侧窗口的列表中选择某一张需要对其所建立的触发器进行修该的表。右击所选中的表，在弹出的下拉菜单中选择,【,所有任务,】,，并继续选择级联菜单中的,【,管理触发器,】,命令，将打开,【,触发器,】,属性对话框，在,【,触发器属性,】,对话框中，在,【,名称,】,下拉列表框选择一个已有的触发器，并在,【,文本,】,编辑框中对其所包含的,SQL,语句进行修改。,在语法检查无误的情况下，点击,【,确定,】,按钮保存修改。如果需要删除当前触发器，可以点击,【,删除,】,按钮。,2,使用,ALTER TRIGGER,语句修改触发器,ALTER TRIGGER,语句与,CREATE TRIGGER,语句的语法格式及其参数类似，部分语法格式为：,ALTER TRIGGER,触发器名称,ON,表名,|,视图名,WITH ENCRYPTION FOR|AFTER|INSTEAD OF DELETE,INSERT,UPDATEAS SQL,语句,.n,3,使用,DROP TRIGGER,语句删除触发器移除触发器时，与触发器有关的信息将从,sysobjects,和,syscomments,系统表中删除。可以通过删除触发器或删除表两种方式移除触发器。删除表时，将除去所有与表相关联的触发器。,DROP TRIGGER,语句的语法格式为：,DROP TRIGGER,触发器名称,.n,触发器名称为数据库中现有的触发器。可以指定一个或多个触发器名称来删除一个或多个触发器。,习题,1,、简述什么是存储过程？简要说明存储过程的语法格式。并给出一个带参数的存储过程实例。,2,、简述什么是触发器？简要说明触发器的语法格式。并给出一个,AFTER,触发器实例。,THANK YOU,SUCCESS,2025/1/15 周三,51,可编辑,