第08章存储过程和触发器

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,清华大学出版社,.SQL Server 2005,数据库应用与开发,SQL Server 2005,数据库应用与开发,第,08,章 存储过程和触发器,内容提要,:,存储过程（,Stored Procedure,）是一组完成特定功能的,Transact-SQL,语句的集合。存储过程是通过用户、其他过程或触发器来调用执行。,利用存储过程可以保证数据的完整性，提高执行重复任务的性能和数据的一致性。,存储过程主要应用于控制访问权限、为数据库表中的活动创建审计追踪、将关系到数据库及其所有相关应用程序的数据定义语句和数据操作语句分隔开。,触发器（,Trigger,）是一种特殊的存储过程。触发器通常在特定的表上定义，当该表的相应事件发生时自动执行，用于实现强制业务规则和数据完整性等。,第,08,章 存储过程和触发器,本章内容,:,8.1,存储过程概述,8.2,创建和管理存储过程,8.3,触发器概述,8.4,创建和管理触发器,8.5,小结,8.1,存储过程概述,存储过程的主要用途：,提高了处理复杂任务的能力。,增强了代码的复用率和共享性。存储过程一旦创建后即可在程序中调用任意多次。,减少了网络中数据的流量。存储过程在服务器注册，加快了过程的运行速度。,加强了系统的安全性。存储过程具有安全特性（例如权限）和所有权链接，用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。,8.1,存储过程概述,8.1.1,存储过程的类型,SQL Server 2005,支持的存储过程的类型主要有如下,4,类。,（,1,）系统存储过程。,SQL Server 2005,中的许多管理活动都是存储过程执行的。,从物理意义上讲，系统存储过程存储在源数据库中，并且带有,sp_,前缀。,从逻辑上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的,sys,构架中。,（,2,）用户定义的存储过程。用户可以自己创建存储过程。存储过程是指封装了可重用代码的模块或例程。用户存储过程有两种类型：,8.1,存储过程概述,8.1.1,存储过程的类型,Transact-SQL,存储过程是指保存的,Transact-SQL,语句集合，可接受和返回用户提供的参数。,CLR,存储过程是指对,Microsoft.NET Framework,公共语言运行时,(CLR),方法的引用，可以接受和返回用户提供的参数,（,3,）临时存储过程。以“,#”,或“,#”,为前缀，表示局部临时存储过程和全局临时存储过程。,（,4,）扩展存储过程。以,xp_,为前缀，是,SQL Server 2005,的实例可以动态加载和运行的,DLL,。,8.1,存储过程概述,8.1.2,存储过程的设计原则,用户创建存储过程时，应注意遵循以下几点原则。,存储过程最大不能超过,128MB,。,用户定义的存储过程只能在当前数据库中创建。,存储过程是为了处理那些需要被多次运行的,Transact-SQL,语句集。,SQL Server,允许在存储过程创建时引用一个不存在的对象，系统只检查创建存储过程的语法。执行时，存储过程引用了一个不存在的对象，则这次执行操作将会失败。,存储过程可以嵌套使用。嵌套的最大层次可以用,NESTLEVEL,函数来查看。,8.1,存储过程概述,8.1.3,常用系统存储过程的使用,SQL Server 2005,提供了许多系统存储过程,下面介绍几种常用的系统存储过程。,（,1,）,sp_helpdb,用于查看数据库名称及大小。,（,2,）,sp_helptext,用于显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。,（,3,）,sp_renamedb,用于重命名数据库。,（,4,）,sp_rename,用于更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。,（,5,）,sp_helplogins,查看所有数据库用户登录信息。,（,6,）,sp_helpsrvrolemember,用于以查看所有数据库用户所属的角色信息。,8.2,创建和管理存储过程,8.2.1,创建存储过程,1,使用,SQL Server Management Studio,创建存储过程,利用,SQL Server Management Studio,创建存储过程就是创建一个模板，通过改写模板创建存储过程。具体参考步骤如下。,（,1,）启动,SQL Server Management Studio,，在对象资源管理器中，展开“数据库”,|teaching|“,可编程性”,|“,存储过程”。,（,2,）如图,8.1,所示，右击“存储过程”节点，选择“新建存储过程”菜单命令。,8.2,创建和管理存储过程,8.2.1,创建存储过程,1,使用,SQL Server Management Studio,创建存储过程,（,3,）系统弹出存储过程模板，如图,8.2,所示，用户可以参照模板在其中输入合适的,Transact-SQL,语句。,（,4,）单击工具栏中的“执行”按钮，即可将存储过程保存在数据库中。,（,5,）刷新“存储过程”节点，可以观察到下方出现了新建的存储过程。,8.2,创建和管理存储过程,8.2.1,创建存储过程,2,使用,CREATE PROCEDURE,语句创建存储过程,CREATE PROCEDURE,语句的语法格式如下：,CREATE PROC EDURE procedure_name ;number,parameter_data_type,VARYING =default OUTPUT,n,WITH RECOMPILE|ENCRYPTION ,FOR REPLICATION,AS sql_statament ,n,例,8.1,创建一个存储过程，输出所有学生的姓名、课程名称和期末成绩信息。,程序代码如下,:,CREATE PROCEDURE student_score,AS,SELECT sname,cname,final,FROM student s,course c,score sc,WHERE s.studentno=sc.studentno and c.courseno=sc.courseno,8.2,创建和管理存储过程,8.2.1,创建存储过程,例,8.2,创建一个存储过程，输出指定学生的姓名及课程名称、期末成绩信息。,程序代码如下,:,CREATE PROCEDURE student_score1,student_name nchar(8),AS,SELECT sname,cname,final,FROM student s,course c,score sc,WHERE s.studentno=sc.studentno and c.courseno=sc.courseno and.sname=student_name,AS sql_statament ,n,例,8.3,创建一个存储过程，用输出参数返回指定学生的所有课程的期末成绩的平均值,程序代码如下,:,CREATE PROCEDURE student_score2,student_name nchar(8),average numeric(6,2),OUTPUT,AS,SELECT average=AVG(final),FROM student s,course c,score sc,WHERE s.studentno=sc.studentno and c.courseno=sc.courseno,and s.sname=student_name,例,8.4,创建一个存储过程，用输出参数返回指定学生的所有课程的期末成绩的平均值，若不指定学生姓名，则返回所有学生的所有课程的期末成绩的平均值。,程序代码如下,:,CREATE PROCEDURE student_score3,student_name nchar(8)=NULL,average numeric(6,2)OUTPUT,AS,SELECT average=AVG(final),FROM student s,course c,score sc,WHERE s.studentno=sc.studentno and c.courseno=sc.courseno,and(,s.sname=student_name,or student_name IS NULL),8.2,创建和管理存储过程,8.2.2,修改存储过程,1.,利用,SQL Server Management Studio,修改存储过程,修改存储过程的参考操作步骤如下。,（,1,）在“对象资源管理器”中展开“数据库”,|teaching|“,可编程性”,|“,存储过程”。,（,2,）右击要修改的用户存储过程如,student_score,，在弹出快捷菜单中选择“修改”命令。,（,3,）在查询编辑器中出现存储过程的源代码，用户可以直接进行修改。,（,4,）修改完毕，执行该存储过程，将修改后的存储过程保存到数据库中。,8.2,创建和管理存储过程,8.2.2,修改存储过程,2.,使用,ALTER PROCEDURE,语句修改存储过程,使用,ALTER PROCEDURE,语句可以修改存储过程。,ALTER PROCEDURE,语句的语法格式如下：,ALTER PROC EDURE procedure_name ;number,parameter_data_type,VARYING =default OUTPUT ,n,WITH RECOMPILE|ENCRYPTION ,FOR REPLICATION,AS sql_statament ,n,例,8.5,修改存储过程,student_score,，使其以加密方式存储在系统表,syscomments,中。,程序代码如下,:,ALTER PROCEDURE student_score,WITH ENCRYPTION,AS,SELECT sname,cname,final,FROM student s,course c,score sc,WHERE s.studentno=sc.Studentno and c.courseno=sc.courseno,8.2,创建和管理存储过程,8.2.3,执行存储过程,利用,EXECUTE,语句可以执行存储过程。对于存储过程的所有者或任何一名对此过程拥有,EXECUTE,特权的用户，都可以执行此存储过程。,EXECUTE,语句的语法格式如下：,EXEC UTE return_status=procedure_name ;number,parameter1=value|parameter1=variable OUTPUT .,WITH RECOMPILE,例,8.6,执行存储过程,student_score2,。,分析：由于该存储过程有输出参数，那么必须在执行存储过程前定义一个变量，以接收存储过程要传出的值。然后可以使用如下语句输出变量,ave,的值。,程序代码如下,:,DECLARE ave numeric(6,2),EXEC student_score2 student_name=,何影,average=ave OUTPUT,SELECT ave,例,8.7,使用默认值执行的存储过程,student_score3,。,程序代码如下,:,DECLARE ave numeric(6,2),EXEC student_score3 average=ave OUTPUT,SELECT ave,8.3,触发器概述,触发器（,Trigger,）是一种响应数据操作语言（,DML,）事件或数据定义语言（,DDL,）事件而执行的特殊类型的存储过程，是在用户对某一表中的数据进行,UPDATE,、,INSERT,和,DELETE,操作时被触发执行的一段程序。,触发器有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。