存储过程与触发器节

《存储过程与触发器节》由会员分享，可在线阅读，更多相关《存储过程与触发器节（49页珍藏版）》请在金锄头文库上搜索。

1、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提供，用户可以直接使用。用户自定义存储过程需要用

2、户自己创建和维护。系统存储过程使用“sp_”作为前缀。,存储过程的优点： 可以在单个存储过程中执行一系列 SQL 语句 。 可以从自己的存储过程内引用其它存储过程，这可 以简化一系列复杂语句。 存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。 存储过程一旦执行一次后，其执行的计划就会驻留在计算机的高速缓冲存储器中。其后对同一个存储过程的调用就可以直接利用编译后在高速缓存中的二进制形式来完成操作。,8.6.2 创建存储过程,在SQL-Server中，可以使用三种方法创建存储过程： 使用向导创建存储过程。 使用SQL-Server企业管理器创建存储过程。 使用Transa

3、ct-SQL语句中的Create Procedure命令创建存储过程。 默认情况下，创建存储过程的许可权归属数据库的所有者，数据库的所有者可以授权给其他用户。,8.6.2.1 使用向导创建存储过程 （1）在企业管理器中选中某个SQL-Server服务器中的数据库，这里选中scdb数据库。选择工具菜单中的“向导”菜单项，系统会弹出“选择向导”对话框。选中“创建存储过程向导”选项 。 （2）单击“确定”按钮，出现“欢迎使用创建存储过程向导”对话框 。 （3）单击“下一步”按钮，出现“选择数据库”对话框，该对话框用于选择创建存储过程中使用的数据库。,（4）选择数据库，单击“下一步”按钮，出现“选择操

4、作对象”对话框，在该对话框中，列出了所有可选择的表，以及可以对表进行的数据库操作，即插入删除和更新。,图9-4 “选择操作对象”对话框,（5）单击“下一步”按钮，出现确认存储过程信息对话框。,8.6.2.2 使用企业管理器创建存储过程 （1）在企业管理器中选中某个SQL-Server服务器中的数据库，这里选中scdb数据库。右键单击数据库下的“存储过程”选项，弹出快捷菜单，,（2）在快捷菜单中选择“新建存储过程”命令出现“新建存储过程”对话框。,（3）在“新建存储过程”对话框的“文本”列表框中输入存储过程名称和程序语句 CREATE PROCEDURE query_proc AS Select

5、 * 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_na

6、me：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。 parameter：过程中的参数。 data_type：参数的数据类型。 Default：参数的默认值。 OUTPUT：表明参数是返回参数。 AS：指定过程要执行的操作。 sql_statement：过程中要包含的任意数目和类型的 Transact-SQL 语句。,例： 不带有参数的存储过程 下面的存储过程从scdb数据库的三个表中返回学生选课情况，结果包括学号、姓名、课程名、成绩。该存储过程不使用任何参数 。,use zzz GO CREATE PROCEDURE proc1 AS SELECT s.sno,

7、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.

8、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：是用来保存参数或者返回参数的变量

9、。 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 , OptionN

10、ame = 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 查看存储过程 创建存储过程之后，它的名字就存储在系统表sysobjec

11、ts中，它的源代码存放在系统表syscomments中。可以使用企业管理器或系统存储过程查看用户创建的存储过程。 1使用企业管理器查看用户创建的存储过程 在企业管理器中，打开指定的服务器和数据库项，并单击scdb中的“存储过程”项，此时在右边的窗口中显示出scdb数据库中的所有存储过程。,右键单击要查看的存储过程，从弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性”选项。或者左键双击要查看的存储过程，也可以弹出“存储过程属性”选项。在此对话框中能够看到存储过程的源代码。,2使用系统存储过程查看用户创建的存储过程 sp_help sp_helptext sp_depends sp_store

12、d_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,8.6.4.3 重命名存储过程 1使用企业管理器重命名存储过程名称 在企业管理器中，右键单击要更名的存储过程，从弹出的快捷菜单中选择“重命名”选项，当存储过程名称变

13、成可输入状态时，就可以直接修改该存储过程的名称了。,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 是要删除的存

14、储过程或存储过程组的名称。 例如： USE sales GO DROP PRODEDURE proc1 GO,8.6.5 触发器概述,8.6.5.1 触发器的概念 触发器（trigger）是一种特殊的存储过程，它不同于一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。 触发器是一个功能强大的工具，与表紧密连接，可以看作是表格定义的一部分。当用户修改（INSERT、UPDATE或DELETE）指定表或视图中的数据时，该表中的相应的触发器就会自动执行。 通常可以在触发器内编写一段自动执行的程序，用于保证数据操作的完整性，从而扩展了对默认值、约束和规则

15、的完整性检查。,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 表用于存储 DEL