21. 建立及管理预存程序
何谓预存程序？
建立预存程序
使用 T-SQL 管理预存程序
本章总结
本章将学习 Microsoft SQL Server 预存程序以及使用方法。首先会介绍预存程序的类型，接着学习建立及管理使用者自订的预存程序与定义参数及变量。建立预存程序的方法有四种，本章会叙述如何使用 Transact-SQL（T-SQL）、SQL Server Enterprise Manager 和建立预存程序精灵，第四种方法为使用 SQL Distributed Management Objects（SQL-DMO），因为该方法与应用程序设计相关，故在此将不作讨论。在操作的过程中可以发现，三种建立预存程序的方法都要利用到 T-SQL 程序代码，本章的 〈使用 CREATE PROCEDURE 陈述式〉 一节中会介绍到 T-SQL 程序代码，请特别留意 T-SQL 程序代码的用法。
何谓预存程序？
预存程序（stored procedure） 是 T-SQL 陈述式的集合，SQL Server 会将该集合中的陈述式编译成一个执行计划单位。当预存程序第一次执行时，经过编译的执行计划即储存在内存的程序快取区中，并且可以重复使用，这样 SQL Server 就不必在每一次执行相同的任务时重复分析语法是否正确。预存程序和其它程序语言的程序相似，可以接受传入的参数、将参数的值传回，或是传回成功或失败状态的讯息。当程序被呼叫时，所有在程序中的陈述式都会被执行。预存程序会执行某项任务所需用到的 T-SQL 陈述式及相关逻辑。由于预存程序可视为一个执行单位，因此可由不同的使用者，重复地执行在同样的任务上，甚至可跨多应用程序作业。预存程序也为作业提供了单一控制点，确保执行商业规则的正确性与一致性。
您的应用程序端可以透过两种方法与 SQL Server 通讯：您可以撰写应用程序以从客户端传送 T-SQL 陈述式到 SQL Server 中，或者自行建立预存程序以在服务器上储存及执行。如果您从客户端应用程序传送 T-SQL 陈述式到服务器，陈述式会透过网络传输，并在每次执行时进行编译。如果使用预存程序，您可以藉由一个陈述式，从应用程序中呼叫预存程序并执行该预存程序。如之前所提到的，SQL Server 会对第一次执行的预存程序进行编译，将此执行计划储存在内存中。当下一次再呼叫相同的执行计划，就不需再重复编译。当一项工作执行时需要多项 T-SQL 陈述式，或某些陈述式经常要执行，使用预存程序就不需要每次执行一项陈述式，就得透过网络从客户端传回结果，从而减少网络的流量和系统的负担。
预存程序也可透过其它方法提高执行效能。举例来说，透过预存程序可减少客户端和服务器端间传送的数据量，客户端所需处理的资料量相对减少，因此利用预存程序在服务器上执行，就是一种提高执行效能的方法。若是在预存程序内部测试条件判断式，可以在预存程序中应用条件陈述式（如在 第二十章 中讨论的 IF 和 WHILE 结构）。这种测试逻辑会透过预存程序在服务器上执行，无须将逻辑写入应用程序里，并且服务器不必立即传回结果到客户端以执行测试条件。您还可以从指令码、批处理，或利用 T-SQL 陈述式的交互式命命列来呼叫预存程序（本章稍后会介绍）。
预存程序还为使用者提供了简单的数据库存取方式。使用者不必知道数据表的结构详细信息就可以存取数据库，换句话说，他们只要执行所需的任务，而不需要直接进入数据表。预存程序即是以此方式确保商业规则。
预存程序可以接收输入参数，使用区域变量，以及传回数据。预存程序透过输出参数、回传SELECT陈述式执行的结果、或全域数据指针（global cursor）传回数据。除了使用全域数据指针，其它的技巧在本章稍后都会有范例。
________________________________________
相关信息
关于全域数据指针的数据，您可在《在线丛书》的 搜寻 标签页中，输入「指针」这个关键词，在 Transact-SQL程序语法参考说明 这个位置，选择 DECLARE CURSOR ，即可找到联机全域数据指针的相关数据。
________________________________________
预存程序共分三种类型： 系统预存程序 、 延伸预存程序 、 简易使用者自订预存程序 。 系统预存程序（system sotred procedure） 由SQL Server所内建，并且有前缀sp_，用于管理 SQL Server 和显示数据库和使用者信息，在 第十三章 中作过介绍； 延伸预存程序（extended sotred procedure） 属于动态链接库（DLLs），SQL Server 可以动态加载与执行，通常是使用 C 或 C++ 语言写成，包含前缀xp_； 简易使用者自订预存程序 由使用者自行建立自订执行使用者指定的任务。
________________________________________
说明
当建立简易使用者自订预存程序时，不应该使用sp_当前缀，因为当SQL Server 遇到有前缀为sp_的预存程序时，会先在 master 数据库中寻找预存程序。举例来说，如果在 MyDB 数据库中，将自订的简易使用者预存程序命名为sp_myproc，SQL Server 会先在 master 数据库中寻找该预存程序（当然是找不到），然后才会去使用者数据库寻找。因此将程序简单的命名为myproc会更有效率。
________________________________________
我们会先简单的介绍延伸预存程序，本章的重点则会放在简易使用者自订预存程序。延伸预存程序因为可利用诸如 C、C++ 等程序语言建立自己的外部例程，扩充了 SQL Server 环境的使用性及延展性，执行时和其它两种类型的预存程序相同，即可以传送参数给延伸预存程序，也可以传回结果集和状态。
如前所述，延伸预存程序属于动态链接库（DLLs），SQL Server 可以动态加载与执行，并直接在 SQL Sever 地址空间中执行。您可以使用 SQL Sever Open Data Services API 撰写程序。
延伸预存程序可以在 SQL Sever 以外环境撰写，当一个延伸预存程序撰写完成，可利用 T-SQL 命令或利用 Enterprise Manager 注册该延伸预存程序。
________________________________________
相关信息
在 SQL Sever《在线丛书》可找到更多延伸预存程序的使用范例。
________________________________________
建立预存程序
本节会介绍建立预存程序的三种方法：T-SQL 的 CREATE PROCEDURE 陈述式、Enterprise Manager 和建立预存程序精灵。
使用 CREATE PROCEDURE 陈述式
CREATE PROCEDURE 陈述式的基本语法如下：
CREATE PROCEDURE [procedure_name]
[{@parameter_name data_type}] [= default][OUTPUT]
[,...,n]
AS t-sql_statement(s)
首先我们建立一个简单的预存程序，此预存程序会在Orders数据表中的每一数据列，选择（并传回）三个数据行中的数据，当ShippedDate数据行中的日期晚于RequiredDate的日期，就会传回数据。请注意，预存程序只能建立在使用中的数据库上，所以必须先利用 USE 陈述式来指定数据库。在建立预存程序前，要先确定是否有重复的名称存在，我们必须用未存在的名称命名预存程序，或是先删除已经存在的名称后再重新命名。用于建立该程序的 T-SQL 程序代码如下：
USE Northwind
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = "LateShipments" AND
type = "P")
DROP PROCEDURE LateShipments
GO
