SQLserver2012第11章存储过程的创建与管理

4）命名本地存储过程时应避免使用“sp_”前缀，目的是便于区分系 统存储过程，并避免对master数据库进行不必要的搜索。当调用名称以 “sp_”开头的存储过程时，SQL Server先搜索master数据库，然后搜索 本地数据库。 5）尽可能减少临时存储过程的使用，以避免对tempdb中系统表的争 用，这种情况可能对性能有不利影响。
所不能比拟的。这些优点包括： 1）通过本地存储、代码预编译和缓存技术实现高性能的数据操作。
2）通过通用编程结构和过程重用实现编程框架。如果业务规则发生
了变化，可以通过修改存储过程来适应新的业务规则，而不必修改客户端 应用程序。
来自百度文库
3）通过隔离和加密的方法提高了数据库的安全性。数据库用户可以
通过得到权限来执行存储过程，而不必给予用户直接访问数据库对象的权 限。另外，存储过程可以加密，这样用户就无法阅读存储过程中的 Transact-SQL命令。
[ { @parameter data_type }
[ VARYING ] [ = default ] [ [ OUTPUT ] ] [ , …n ] [ WITH [ ENCRYPTION | RECOMPILE ] ]
2）在创建存储过程时的定义中指定WITH RECOMPILE选项，指
明SQL Server 2012将不为该存储过程执行缓存计划，在每次执行该
存储过程时都对其重新编译。此外，当存储过程的参数值在各次执行 时都有较大差异，导致每次均需创建不同的执行计划时，也可以使用
WITH RECOMPILE选项。该选项并不常用，因为每次执行存储过程
时都必须对其重新编译，这样会导致存储过程的执行速度变慢。 3）可以通过指定WITH RECOMPILE选项，强制在执行存储过程
时对其重新编译，仅当所提供的参数是非典型参数，或自创建该存储
过程后数据发生显著变化时，才应使用此选项。
11.2 存储过程的创建及执行
11.2.1 存储过程的创建
创建存储过程的基本语法格式如下： CREATE PROCEDURE [schema_name.] procedure_name
SQL Server 2012
数据库管理教程
第11章
第11章 存储过程的创建与管理
存储过程的概述
存储过程的创建及执行
修改和删除存储过程
系统存储过程和扩展存储过程
11.1 存储过程的概述
存储过程是一组Transact-SQL语句集合，它提供了一种封装任务的方
法。SQL Server中的存储过程与其他编程语言中的过程类似，因此可以：
1）包含可在数据库中执行操作的语句，包括调用其他存储过程等。 2）接收输入参数。
3）将状态值返回发起调用的存储过程或批处理，以指示成功或失败。
4）以输出参数的形式将多个值返回发起调用的存储过程或客户端应用 程序。
11.1.1 存储过程的优点
存储过程具有很多优点，这些优点是仅仅使用Transact-SQL代码查询
当存储过程所基于的基表发生结构变化时，该存储过程会自动优化；但 是当添加了新的索引或更新了某些列数值之后，该存储过程将不自动执行优 化。直到下一次SQL Server 2012服务器重新启动后再运行该存储过程时为 止，可以强制在下次启动服务器前重新编译该存储过程，以更新原有的执行 计划。SQL Server 2012提供了以下三种方式。 1）使用sp_recompile系统存储过程强制在下次执行存储过程时对其重新 编译，其调用语法格式如下： sp_recompile[@objname=] 'object' 其中，object为存储过程名。
11.1.2 存储过程与视图的比较
存储过程与视图的区别如下：
1）可以在单个存储过程中执行一系列Transact-SQL语句。存储过程 可包含程序流、逻辑以及对数据库查询的Transact-SQL语句，而视图中
只能是SELECT语句。
2）视图不能接收参数，只能返回结果集；而存储过程可以接收参数， 包括输入参数、输出参数，并能返回单个或多个结果集以及返回值，这
样一来大大提高了应用的灵活性。
一般，人们将经常用到的多个表达式连接查询定义为视图，而存储过 程完成复杂的一系列处理。在存储过程中也经常会用到视图。
11.1.3 创建存储过程的准则
创建存储过程时应考虑以下准则： 1）用相应的架构名称限定存储过程所引用的对象名称，从而确保从 存储过程中访问来自不同架构的表、视图或其他对象。如果被引用的对象 名称未加限定，则默认情况下将搜索存储过程的架构。 2）设计每个存储过程以完成单项任务。 3）在服务器上创建、测试存储过程，并对其进行故障诊断，然后在 客户端上进行测试。
11.1.4 存储过程的执行过程和重编译
存储过程在第一次执行时，需要经过语法分析、解析、编译和执行四个
阶段。
1）语法分析阶段：指在创建存储过程时，系统检查其语句正确与否。如 果有错，系统会提示错误信息，并宣告创建失败；如果程序通过检查，则系
统自动将该存储过程保存在当前数据库的sys.sql_modules目录视图中。
2）解析阶段：又称为延迟阶段，是指查询处理器从sys.sql_modules目 录视图中读取该存储过程脚本，并检查该存储过程引用对象名称是否存在的
过程，也即系统允许在创建存储过程时，引用的对象可以不存在（只适用于
表对象），但这些存储过程在执行时必须存在。
3）编译阶段：指分析存储过程并生成存储过程执行计划的过程。执行计 划是描述存储过程执行最快的方法，其生成过程取决于表中的数据量、表的索 引特性、WHERE子句使用的条件以及是否使用了UNION，GROUP BY， ORDER BY子句等。查询优化器在分析完存储过程的这些因素后，将生成的执 行计划置于高速缓冲存储区中。该缓冲区是SQL Server 2012用来存储已经编 译的查询规划，以便执行存储过程的内存区域。 4）执行阶段：指执行驻留在高速缓冲存储区中存储过程执行计划的过程。 在以后的执行过程中，如果现有的执行计划仍然驻留在高速缓冲存储区中， SQL Server 2012将重用现有的计划；如果没有，则需要创建新的执行计划。