在 INSERT 语句内执行存储过程
语法：INSERT INTO 表名 EXEC[UTE] ……
将本地或远程存储过程返回的结果集插入本地表中
在 INSERT 语句内执行的存储过程必须返回关系结 果集
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
通过位置传递参数（顺序保持一致）
EXEC OverdueOrders2 1 , '1996-7-17'
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
20
使用输出参数返回值
输出参数：以 OUTPUT 关键字指定的变量
CREATE PROC proc1 @A int , @B int , @RESULT int OUTPUT AS SET @RESULT = @A * @B GO
用 ALTER PROCEDURE 中的定义取代现有存 储过程原先的定义，但保留权限分配
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
15
修改和删除存Leabharlann 过程（续）删除存储过程语法：DROP PROCEDURE {存储过程名} [,...n]
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
13
执行存储过程
单独执行存储过程
不带参数的情况：
[[EXEC[UTE] 存储过程名 [ WITH RECOMPILE]
USE Northwind GO EXEC OverdueOrders GO
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
11
创建存储过程（续）
查看存储过程的信息
查看所有类型存储过程的额外信息 系统存储过程 sp_help、sp_helptext、 sp_depends
显示数据库中的存储过程以及拥有者名字的列表 系统存储过程 sp_stored_procedures
AS SELECT CONVERT(char(8), OrderDate, 1) OrderDate,
OrderID, CustomerID, EmployeeID FROM Orders WHERE EmployeeID = @Employee_ID and OrderDate = @Order_date GO
用 DROP PROCEDURE 语句从当前数据库中移 除用户定义存储过程
删除存储过程的注意事项
在删除存储过程之前，执行系统存储过程 sp_depends 检查是否有对象依赖于此存储过程
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
16
得到存储过程的信息 查询系统表 sysobjects、syscomments、 sysdepends
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
12
创建存储过程的指导原则
避免出现存储过程的拥有者和底层对象的拥有者不同 的情况，建议由dbo用户拥有数据库中所有对象 每个存储过程完成单个任务 命名本地存储过程的时候，避免使用“sp_”前缀 尽量少使用临时存储过程，以避免频繁连接 tempdb 里的系统表 不要直接从 syscomments 系统表里删除项
第13章 实现存储过程
存储过程介绍 创建和管理存储过程 在存储过程中使用参数 系统和扩展存储过程 处理错误信息
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
17
在存储过程中使用参数
使用输入参数 使用输入参数执行存储过程 使用输出参数返回值 显式地重新编译存储过程
执行有输出参数的存储过程
DECLARE @answer int EXEC proc1 4, 7, @answer OUTPUT SELECT @answer as ANSWER
必须定义一个变量， 以接受返回值
写上OUTPUT,才 可以接收到返回值
Copyright@2006 College of ITSoft (HZIEE)
RULE、CREATE TRIGGER 和 CREATE VIEW
执行 CREATE PROCEDURE 语句的用户必须是 sysadmin、db_owner 或 db_ddladmin角色的成员， 或必须拥有 CREATE PROCEDURE 权限
依赖于可用内存，存储过程的最大大小为128 MB
存储过程可以嵌套32层。当前的嵌套层数存储在系统 函数 @@nestlevel 中。
Version No: 1.0
21
显式地重新编译存储过程
存储过程可以显式地重新编译，但应尽量少 做，仅当
存储过程所引用的表中的数据发生巨大的变化时 存储过程所引用的对象的架构发生变更时，如增加 删除列、规则、约束，或者为底层表增加了存储过 程可能从中受益的索引时
Copyright@2006 College of ITSoft (HZIEE)
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
19
使用输入参数执行存储过程
通过参数名传递值(顺序无所谓)
EXEC OverdueOrders2 @Employee_ID = 1 , @Order_date = '1996-7-17'
EXEC OverdueOrders2 @Order_date = '1996-7-17', @Employee_ID = 1
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
3
存储过程的优点
存储过程封装了商务逻辑，确保一致的数据访问和修改。若 规则或策略有变化，则只需要修改服务器上的存储过程，所 有的客户端就可以直接使用
屏蔽数据库模式的详细资料。用户不需要访问底层的数据库 和数据库内的对象
SELECT * FROM dbo.Orders WHERE RequiredDate < GETDATE() AND ShippedDate IS Null GO
只能在当前数据库内创建存储过程，除了临时存储过 程。临时存储过程总是创建在 tempdb 数据库中 存储过程可以引用表、视图、用户定义函数、其他存 储过程以及临时表
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
4
存储过程的初始处理
创建
执行
（初次或 重新编译）
解析 优化
项存入 sysobjects 和 syscomments 表
编译
编译过的执行计划 放入过程缓存
Copyright@2006 College of ITSoft (HZIEE)
第13章 实现存储过程
存储过程介绍 创建和管理存储过程 在存储过程中使用参数 系统和扩展存储过程 处理错误信息
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
1
存储过程介绍
定义存储过程 存储过程的优点 存储过程的初始化和后续处理
Copyright@2006 College of ITSoft (HZIEE)
14
修改和删除存储过程
修改存储过程
USE Northwind GO ALTER PROC dbo.OverdueOrders AS SELECT CONVERT(char(8), OrderDate, 1) OrderDate,
OrderID, CustomerID, EmployeeID FROM Orders WHERE RequiredDate < GETDATE() AND ShippedDate IS Null ORDER BY RequiredDate GO
不对该存储过程 计划进行高速缓存
EXECUTE [WITH RECOMPILE]
EXEC proc1 4, 7, @answer OUTPUT WITH RECOMPILE
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
22
显式地重新编译存储过程（续）
三种显式重新编译存储过程的方法
CREATE PROCEDURE [WITH RECOMPILE]
CREATE PROC proc1 @A int , @B int , @RESULT int OUTPUT WITH RECOMPILE AS SET @RESULT = @A * @B GO
Version No: 1.0
8
创建和管理存储过程
创建存储过程 执行存储过程 修改和删除存储过程
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
9
创建存储过程
创建存储过程
USE Northwind GO CREATE PROC dbo.OverdueOrders AS
Copyright@2006 College of ITSoft (HZIEE)
Version No: 1.0
6
存储过程的后续处理（续）
检索到的执行计划 执行计划