数据库教程(精品·公开课件).ppt

数据库应用技术SQL Server 2005 第9章 存储过程和触发器 能力目标: 能根据项目需求设计存储过程。 能根据项目逻辑设计中数据完整性要求设计编写触发器。 任务设计： 编写简单的存储过程。 编写简单的触发器实现完整性控制。 知识要求： 存储过程和触发器的基本概念。 存储过程和触发器的编程方法。 9.1 存储过程 9.1.1 存储过程的基本知识 概念 存储过程（Stored Procedure）一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。客户端应用程序可以通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。 优点 使用存储过程而不使用存储在客户端计算机本地的 T-SQL 程序的优点包括： 允许标准组件式编程，增强重用性和共享性 能够实现较快的执行速度 能够减少网络流量 可被作为一种安全机制来充分利用 9.1.1 存储过程的基本知识 分类 在SQL Server 2005中存储过程分为三类：系统提供的存储过程、用户自定义存储过程和扩展存储过程。 系统：系统提供的存储过程，sp_*，例如：sp_rename 扩展：SQL Server环境之外的动态链接库DLL，xp_ 远程：远程服务器上的存储过程 用户：创建在用户数据库中的存储过程 临时：属于用户存储过程，#开头（局部：一个用户会话）,##（全 局：所有用户会话） 9.1.2 创建用户存储过程 使用存储过程模板创建存储过程 在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“存储过程”，选择“新建存储过程”命令，如图所示： 9.1.2 创建用户存储过程 在右侧查询编辑器中出现存储过程的模板，用户可以在此基础上编辑存储过程，单击“执行”按钮，即可创建该存储过程。 9.1.2 创建用户存储过程 例9-1：创建一个简单的存储过程 USE Library GO CREATE PROCEDURE borrowed_num AS SELECT Rname,Lendnum FROM Reader WHERE Rname=赵良宇 存储过程建好了，什么时候，怎么用呢？ 执行存储过程： borrowed_num 或 EXEC borrowed_num 执行结果： 9.1.2 创建用户存储过程 使用T-SQL语句创建表 格式： CREATE PROC 过程名 @形参名 类型 @变参名 类型 OUTPUT AS SQL语句 9.1.2 创建用户存储过程 例9-2：创建一个多表查询的存储过程。 USE Library GO CREATE PROCEDURE borrowed_book1 AS SELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDate FROM reader r INNER JOIN borrow b ON r.RID=b.RID INNER JOIN book k ON b.BID=k. BID WHERE Rname=程鹏 执行存储过程： 9.1.2 创建用户存储过程 borrowed_book1 或 EXEC borrowed_book1 执行结果： 9.1.3 管理用户存储过程 获得存储过程的信息 语法： sp_helptext [[@objname=]存储过程名] 说明: 要显示用来创建过程的文本，可在存储过程所在的数据库中执行sp_helptext，并使用过程名作为参数。使用ENCRYPTION选项创建的存储过程不能使用sp_helptext查看。 9.1.3 管理用户存储过程 修改存储过程 语法： ALTER PROC [ EDURE] 存储过程名[，n] @形参名 数据类型 @变参名 数据类型 OUTPUT 说明: 如果需要更改存储过程中的语句或参数，可以删除或重新创建该存储过程，也可以直接修改该存储过程。删除或重新创建 存储过程时，所有与该存储过程相关的权限都将丢失；而修改 存储过程时，过程或参数定义会更改，但权限将保留。修改存 储过程使用语句ALTER PROCEDURE来完戍。 9.2 触发器 9.2.1 触发器的基本知识 基本概念 触发器是特殊的存储过程，基于一个表创建，主要作用就是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。 当触发器所保护的数据发生变化（update,insert,delete）后，自动运行以保证数据的完整性和正确性。通俗的说：通过一个动作（update,insert,delete）调用一个存储过程（触发器）