第9章 存储过程和触发器.doc

存储过程和触发器
教学目标:
掌握存储过程和触发器的基本概念,学会编写简单的存储过程和触发器,对存储过程和触发器的实际应用有较好的理解。
存储过程
存储过程的基本知识
概念
存储过程(Stored Procedure)是一组编译好存储在服务器上的完成特定功能T-SQL代码,是某数据库的对象。客户端应用程序可以通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行存储过程。
优点
使用存储过程而不使用存储在客户端计算机本地的 T-SQL 程序的优点包括:
允许标准组件式编程,增强重用性和共享性
能够实现较快的执行速度
能够减少网络流量
可被作为一种安全机制来充分利用
分类
在SQL Server 2005中存储过程分为三类:系统提供的存储过程、用户自定义存储过程和扩展存储过程。
系统:系统提供的存储过程,sp_*,例如:sp_rename
扩展:SQL Server环境之外的动态链接库DLL,xp_
远程:远程服务器上的存储过程
用户:创建在用户数据库中的存储过程
临时:属于用户存储过程,#开头(局部:一个用户会话),##(全局:所有用户会话)
创建用户存储过程
使用存储过程模板创建存储过程
在【对象资源管理器】窗口中,展开“数据库”节点,再展开所选择的具体数据库节点,再展开选择“可编程性”节点,右击“存储过程”,选择“新建存储过程”命令,如图所示:
在右侧查询编辑器中出现存储过程的模板,用户可以在此基础上编辑存储过程,单击“执行”按钮,即可创建该存储过程。
例9-1:创建一个简单的存储过程。
USE Library
GO
CREATE PROCEDURE borrowed_num
AS
SELECT Rname,Lendnum
FROM Reader
WHERE Rname='赵良宇'
存储过程建好了,什么时候,怎么用呢?
执行存储过程:
borrowed_num 或
EXEC borrowed_num
执行结果:
使用T-SQL语句创建表
格式:
CREATE PROC 过程名
@形参名类型
@变参名类型 OUTPUT
AS SQL语句
例9-2:创建一个多表查询的存储过程。
USE Library
GO
CREATE PROCEDURE borrowed_book1
AS
SELECT ,,,,
FROM reader r INNER JOIN borrow b
ON = INNER JOIN book k
ON =k. BID
WHERE Rname='程鹏'
执行存储过程:
borrowed_book1 或
EXEC borrowed_book1
执行结果:
存储过程的参数
输入参数(值参)
例9-3:输入参数为某人的名字。
USE Library
GO
CREATE PROCEDURE borrowed_book2
***@name varchar(10) --形式参数
As
SELECT ,,,,
FROM reader r INNER JOIN borrow b
ON = INNER JOIN book k
ON =k. BID
WHERE Rname=***@name
GO
执行存储过程:
直接传值:
EXEC borrowed_book2 '程鹏' --实参表
变量传值:
DECLARE ***@temp1 char(20)
SET ***@temp1='杨树华'
EXEC borrowed_book2 ***@temp1 --实参表
执行结果:
例9-4:使用默认参数
USE Library
GO
CREATE PROCEDURE borrowed_book3
***@name varchar(10)=NULL --默认参数
AS
IF ***@name IS NULL
SELECT ,,,,
FROM reader r INNER JOIN borrow b
ON = INNER JOIN book k
ON =
ELSE
SELECT ,,,,
FROM reader r INNER JOIN borrow b
ON = INNER JOIN book