《《存储过程和触发器》ppt课件》由会员分享，可在线阅读，更多相关《《存储过程和触发器》ppt课件（35页珍藏版）》请在金锄头文库上搜索。
1、SQL Server 2005 数据库应用与开发,第08章 存储过程和触发器,内容提要: 存储过程（Stored Procedure）是一组完成特定功能的Transact- SQL语句的集合。存储过程是通过用户、其他过程或触发器来调用执行。 利用存储过程可以保证数据的完整性，提高执行重复任务的性能和数据的一致性。 存储过程主要应用于控制访问权限、为数据库表中的活动创建审计追踪、将关系到数据库及其所有相关应用程序的数据定义语句和数据操作语句分隔开。 触发器（Trigger）是一种特殊的存储过程。触发器通常在特定的表上定义，当该表的相应事件发生时自动执行，用于实现强制业务规则和数据完整性等。,第0
2、8章 存储过程和触发器,本章内容: 8.1 存储过程概述 8.2 创建和管理存储过程 8.3 触发器概述 8.4 创建和管理触发器 8.5小结,8.1 存储过程概述,存储过程的主要用途： 提高了处理复杂任务的能力。 增强了代码的复用率和共享性。存储过程一旦创建后即可在程序中调用任意多次。 减少了网络中数据的流量。存储过程在服务器注册，加快了过程的运行速度。 加强了系统的安全性。存储过程具有安全特性（例如权限）和所有权链接，用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。,8.1 存储过程概述,8.1.1 存储过程的类型 SQL Server 2005支持的存储过程的类
3、型主要有如下4类。 （1）系统存储过程。SQL Server 2005 中的许多管理活动都是存储过程执行的。 从物理意义上讲，系统存储过程存储在源数据库中，并且带有 sp_ 前缀。 从逻辑上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的 sys 构架中。 （2）用户定义的存储过程。用户可以自己创建存储过程。存储过程是指封装了可重用代码的模块或例程。用户存储过程有两种类型：,8.1 存储过程概述,8.1.1 存储过程的类型 Transact-SQL 存储过程是指保存的 Transact-SQL 语句集合，可接受和返回用户提供的参数。 CLR 存储过程是指对 Microsoft .NE
4、T Framework 公共语言运行时 (CLR) 方法的引用，可以接受和返回用户提供的参数 （3）临时存储过程。以“#”或“#”为前缀，表示局部临时存储过程和全局临时存储过程。 （4）扩展存储过程。以xp_为前缀，是SQL Server 2005的实例可以动态加载和运行的 DLL。,8.1 存储过程概述,8.1.2 存储过程的设计原则 用户创建存储过程时，应注意遵循以下几点原则。 存储过程最大不能超过128MB。 用户定义的存储过程只能在当前数据库中创建。 存储过程是为了处理那些需要被多次运行的Transact-SQL语句集。 SQL Server允许在存储过程创建时引用一个不存在的对象，系
5、统只检查创建存储过程的语法。执行时，存储过程引用了一个不存在的对象，则这次执行操作将会失败。 存储过程可以嵌套使用。嵌套的最大层次可以用 NESTLEVEL函数来查看。,8.1 存储过程概述,8.1.3 常用系统存储过程的使用 SQL Server 2005提供了许多系统存储过程,下面介绍几种常用的系统存储过程。 （1）sp_helpdb 用于查看数据库名称及大小。 （2）sp_helptext 用于显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。 （3）sp_renamedb 用于重命名数据库。 （4）sp_rename 用于更改当前数据库中用户创建对象（如表、列或用户
6、定义数据类型）的名称。 （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 |“可编程性”|“存储过程”。 （
7、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语句的语法格式
8、如下： 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 W
9、HERE 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.co
10、urseno 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.cour
11、seno 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
12、.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）修改完毕，执行该存
13、储过程，将修改后的存储过程保存到数据库中。,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 修改存储过程st
14、udent_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特权的用户，都
15、可以执行此存储过程。 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_n
16、ame=何影, 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操作时被触发执行的一段程序。 触发器有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。,8.3 触发器概
