《《SQL Server2000数据库及应用》-李伟红-电子教案 第8章 存储过程和触发器的创建与维护》由会员分享，可在线阅读，更多相关《《SQL Server2000数据库及应用》-李伟红-电子教案 第8章 存储过程和触发器的创建与维护（96页珍藏版）》请在金锄头文库上搜索。
1、第8章 存储过程和触发器的 创建与维护,北华航天工业学院 李伟红,本章学习目标,了解存储过程的概念； 掌握创建、执行存储过程的方法； 掌握查看和修改存储过程的方法； 掌握删除存储过程的方法； 了解触发器和一般存储过程的主要区别； 掌握创建和查看触发器的方法； 掌握修改和删除触发器的方法。,8.1 概 述,8.1.1 存储过程概述,在SQL Server 2000中，可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。,8.1.1 存储过程概述,应该提倡多使用存储过程，原因有以下几点。 （
2、1）实现了模块化编程。 （2）调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。 （3）存储过程可以接受输入参数并可以返回输出值。 （4）存储过程具有对数据库立即访问的功能。 （5）使用存储过程可以加快程序的运行速度。 （6）使用存储过程可以减少网络流量。 （7）使用存储过程可以提高数据库的安全性。,8.1.1 存储过程概述,在SQL Server 2000中，存储过程分为两类：系统存储过程和用户自定义的存储过程。系统存储过程是由系统自动创建的，主要存储在master数据库中，一般以sp_为前缀。系统存储过程完成的功能主要是从系统表中获取信息。用户自定义存储过程是由用户创建并能完成
3、某一特定功能的存储过程。,8.1.2 触发器概述,触发器是一种特殊类型的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。,8.1.2 触发器概述,触发器可以用于SQL Server 2000约束、默认值和规则的完整性检查，和CHECK约束相比较，触发器可以强制实现更加复杂的数据完整性，而且可以引用其他表中的字段。,8.1.2 触发器概述,在SQL Server 2000中，触发器分为两种，即AFTER触发器和INSTEAD OF触发器。 AFTER触发器：这种类型的触发器将在数据变动（INSERT、UPDATE和DELETE操作）完成以后才被触发。可
4、以对变动的数据进行检查，如果发现错误，将拒绝接受或回滚变动的数据。AFTER触发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。,8.1.2 触发器概述,INSTEAD OF触发器：这种类型的触发器将在数据变动以前被触发，并取代变动数据的操作（INSERT、UPDATE和DELETE操作），而去执行触发器定义的操作。在表或视图上，每个INSERT、UPDATE和DELETE语句最多只可以定义一个INSTEAD OF触发器。,8.2 存储过程,8.2.1 创建存储过程,在SQL Server 2000中，可以使用三种方法创建存储过程。 使用创建存储过程向导创建存储过程。 使用SQ
5、L Server 2000企业管理器创建存储过程。 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。,1使用创建存储过程向导创建存储过程,（1）在企业管理器中，选中某个SQL Server服务器，选择要创建存储过程的数据库，选择“工具”菜单中的“向导”菜单项，单击向导中“数据库”选项左边的加号，选中“创建存储过程向导”选项。 （2）单击“确定”按钮。,2使用SQL Server 2000企业管理器创建存储过程,使用SQL Server 2000企业管理器创建存储过程的步骤如下： （1）在SQL Server 2000企业管理器中，选择指定的服务器和数据库，
6、右击要创建存储过程的数据库，在弹出的快捷菜单中依次选择“新建”“存储过程”命令；或者右击数据库中的存储过程图标，从弹出的快捷菜单中选择“新建存储过程”命令。,3使用Transact-SQL语句创建存储过程,可以使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程，只能在当前数据库中创建存储过程。,3使用Transact-SQL语句创建存储过程,CREATE PROCEDURE的语法形式如下： CREATE PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT ,.n W
7、ITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n ,其中各参数的含义如下： procedure_name：用于指定所要创建存储过程的名称。 number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起除去。,3使用Transact-SQL语句创建存储过程,parameter：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。 data_type：用于指定参数的数据类型。 VARYING：用于
8、指定作为输出OUTPUT参数支持的结果集。仅适用于游标参数。,3使用Transact-SQL语句创建存储过程,default：用于指定参数的默认值。 OUTPUT：表明该参数是一个返回参数。 RECOMPILE：表明SQL Server 2000不会保存该存储过程的执行计划，该存储过程每执行一次都要重新编译。,3使用Transact-SQL语句创建存储过程,ENCRYPTION：表示对存储过程文本进行加密。 FOR REPLICATION：用于指定该存储过程只能在数据复制时使用。本选项不能和WITH RECOMPILE选项一起使用。,3使用Transact-SQL语句创建存储过程,AS：用于指
9、定该存储过程要执行的操作。 sql_statement：是存储过程中包含的任意数目和类型的Transact-SQL语句。,3使用Transact-SQL语句创建存储过程,例8-1 创建一个带有参数的存储过程“存储过程_读者借阅信息”，该存储过程根据传入的读者编号，返回此人的基本信息及此人借阅的图书信息。,3使用Transact-SQL语句创建存储过程,8.2.2 管理存储过程,1查看存储过程 存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用企业管理器或系统存储过程来查看用户创建的存储过程。,1查看存储过程,在企业管理器中
10、查看用户创建的存储过程的方法如下。 （1）在企业管理器中，打开指定的服务器和数据库项，并单击存储过程文件夹，此时在右边的窗格中就会显示出数据库中的所有存储过程。,1查看存储过程,（2）右击要查看的存储过程，从弹出的快捷菜单中选择“属性”命令，会弹出“存储过程属性”对话框。,1查看存储过程,也可以使用系统存储过程来查看用户创建的存储过程。可供使用的系统存储过程及其语法形式如下： （1）sp_help：用于显示存储过程的参数及其数据类型 sp_help objname= name 参数name为要查看的存储过程的名称。,1查看存储过程,（2）sp_helptext：用于显示存储过程的源代码 sp_
11、helptext objname= name 参数name为要查看的存储过程的名称。,1查看存储过程,（3）sp_depends：用于显示和存储过程相关的数据库对象 sp_depends objname=object 参数object为要查看依赖关系的存储过程的名称。,1查看存储过程,（4）sp_stored_procedures：用于返回当前数据库中的存储过程列表 sp_stored_proceduressp_name=name ,sp_owner=owner ,sp_qualifier = qualifier,1查看存储过程,其中，sp_name = name 用于指定返回目录信息的过程名
12、；sp_owner = owner 用于指定过程所有者的名称；sp_qualifier = qualifier 用于指定过程限定符的名称。,1查看存储过程,例8-2 使用系统存储过程查看“存储过程_读者借阅信息”的参数及其数据类型。 程序清单如下： -查看存储过程的参数及其数据类型 USE BOOKS GO sp_help 存储过程_读者借阅信息 GO,1查看存储过程,例8-3 使用系统存储过程查看“存储过程_读者借阅信息”的相关的数据库对象。 程序清单如下： -查看相关的数据库对象 USE BOOKS GO sp_depends 存储过程_读者借阅信息 GO,2修改存储过程定义,在企业管理器
13、中，单击目录树中的存储过程图标，在窗口的右侧右击要修改的存储过程，从弹出的快捷菜单中选择“属性”命令，则会出现存储过程属性对话框。,2修改存储过程定义,其语法形式如下： ALTER PROCEDURE procedure_name;number parameterdata_type VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n ,3重命名存储过程,在企业管理器中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”命令，
14、或者双击存储过程名称，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称了。,3重命名存储过程,也可以使用系统存储过程sp_rename修改存储过程的名称，其语法形式如下： sp_rename 原存储过程名称，新存储过程名称,8.2.3 执行存储过程,在SQL Server 2000中可以使用EXECUTE命令来直接执行存储过程，语法形式如下： EXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPIL
15、E ,8.2.3 执行存储过程,其中各选项的含义如下： EXECUTE：执行存储过程的命令关键字，如果此语句是批处理中的第一条语句，可以省略此关键字。 return_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在使用前，必须在批处理、存储过程或函数中声明过。,8.2.3 执行存储过程,procedure_name：指定执行的存储过程的名称。 ;number：用来指定该存储过程与其他同名存储过程同组时的标识号。 procedure_name_var：是局部定义变量名，代表存储过程名称。,8.2.3 执行存储过程,parameter：是在创建存储过程时定义的过程参数。调用时向存储过程所传递的参数值由value参数或variable变量提供，或者使用DEFAULT关键字指定使用该参数的默认值，OUTPUT参数说明指定参数为返回参数。 WITH RECOMPILE：指定在执行存储过程时重新编译执行计划。,8.2.3 执行存储过程,例8-4 执行前面创建的无参存储过程“存储过程_读者信息”。 程序清单如下： USE BOOKS EXEC 存储过程_读者信息 或直接写存储过程的名称： USE BOOKS GO 存储过程_读者信息,8.2.3 执行存储过程,例8-5 执行有参存储过程“存储过程_读者借阅信息”，该存储过程有一个输入参数“读者编号”。 程序清单
