第20学时存储过程
存储过程是SQL Server上的一个非常有用的特性。存储过程由被编译在一起的一组S Q L语
句组成，并可以通过调用单个命令来执行。它们对于维护那些在SQL Server上运行应用程序
时所需的代码是非常有用的。SQL Server也预安装了许多系统存储过程，这些系统存储过程
在服务器上运行，用于收集关于服务器的专门信息。
本学时的要点包括：
• 什么是存储过程
• 存储过程的用途
• 创建和修改存储过程
• 运行存储过程
• 删除存储过程
20.1 什么是存储过程
正如我们前面提到的，存储过程只是一个S Q L语句组合，它们被编译在一起，能通过单
个命令而被执行。存储过程能用于向用户返回数据、在表中插入新数据、修改数据、执行系
统函数和管理任务。总之，它们是SQL Server中功能非常强大的一部分，它能使你的工作更
轻松。存储过程具有能自动地在SQL Server上存储你的应用程序的业务逻辑和函数功能，这
样在服务器级上就可以对数据做出有关决策，而不是把结果返回给客户机。在过去几年里，
关于如何用存储过程支持这种功能，出现了两个主要的阵营。
第一个阵营认为，在存储过程中加入的功能越多越好。他们相信存储过程应能在数据
库的插入与更新中提供一切，以保持数据的有效性。赞成者的观点是，如果业务规则变化，
所有要做的仅仅是在服务器级更改存储过程，而不必在客户机级进行修改。反对者的观点是，
这种解决方案等于把所有的应用程序逻辑放进数据库，会使数据库本身的速度减慢。许多人
正在从这种解决方案转向下一种。
第二个阵营认为，所有由实际应用程序来处理的功能都应放在存储过程之外，并在数据
库之外进行处理。他们基本上认为应该放入存储过程的东西仅仅是实际的更新、插入和删除。
任何业务逻辑，比如数据有效性的校验，都应放在中间层或放在实际应用程序级。赞成这种
解决方案的是那些对速度要求高而且其数据库仅仅是为了存储数据的人。反对者的观点是，
如果你所有的业务逻辑都存储在前端的应用程序中，任何业务逻辑的变化都将导致程序的重
写、重编译以及重新分发。另一个主要问题是，当所有的应用程序逻辑都存储在数据库本身
时，很难把数据库从一个平台移植到另一个。通常通过使用包含所有业务逻辑的中间层应用
程序来解决这个问题。
SQL Server存储过程能通过下列方法之一向用户返回数据:
• 从任何包含在存储过程中的S E L E C T语句中返回的结果集。
• 整数值形式的返回代码。
• 输出参数，其中包含能被用户应用程序获得的整数或字符数据。
• 一个可以在存储过程外引用的游标。
在SQL Server以前的版本中，存储过程是一种加速S Q L语句执行的方法。这些语句包含
在存储过程里，它通过部分地编译执行计划来实现加速。存当储过程创建后，部分被编译的
执行计划就被存储起来。当存储过程执行时，它们将被检索出来并获得优化。当存储过程被
执行和优化后，执行计划就被存储在高速缓存中。当其他的处理器或用户运行该存储过程时，
计划被简单地从高速缓存中恢复并执行。
在此新版本的SQL Server中，存储过程通过一种完全不同的方法得到处理。SQL Server
7 . 0不再为存储过程存储任何执行计划，而是当过程被执行时， SQL Server检索代码、编译、
优化并且执行它。这时执行计划才被存储在高速缓存里，以便下一步的执行。
20.2 存储过程的用途
存储过程的用途几乎是无限的。从返回S E L E C T语句的结果并用于用户报表到执行复杂的
数据有效性校验，你可以用存储过程来做任何事。至于说存储过程到底能为你做什么，部分
地取决于你是属于前面列出的两个阵营中的哪一个。如果你是坚信所有的应用程序功能都应
包含在存储过程中的那种人，让存储过程做任何事就成为可能的了。如果你认为SQL Server
只应该用于存储数据，这也是一种选择。以下是存储过程的一些用途：
• 模块化的程序设计—在你创建了一个存储过程之后，它就会存储在数据库中并能通过
你的应用程序多次调用它。这些过程能封装数据库功能，也能被应用程序代码独立地修
改，只要应用程序总是知道被返回的数据将要做什么。
• 快速执行—当存储过程被编译并存储在过程高速缓冲中时，它能以相当高的效率执行。
这是因为SQL Server无需不断地重复编译存储过程。
• 网络通信量—你时常会有长达数百行的S Q L语句。如果能把那些功能封装进存储过程，
就可以通过向SQL Server发送一行代码起到成百行的语句的作用，从而节省大量的网络
带宽。当仅有少量用户运行应用程序时，你的感受也许不深；但是如果有成千上万的用
户使用这个应用程序时，你就会深有感触。
• 安全性—存储过程能作为一种安全性机制使用。当你授权许可一个用户或一组用户使
用存储过程，他们将能够执行这个存储过程而不必有访问在存储过程中被访问的下层对
象的许可。例如，如果你创建了一个存储过程，用于返回p u b s数据库中a u t h o r s表的所有
行，然后许可一个名叫乔的人去执行那个存储过程，他就能从a u t h o r s表中检索出所有行，
而不必直接地授予他对a u t h o r s表的访问许可。
现在你已经知道存储过程能为你做些什么，让我们再来看看如何创建和修改存储过程。
20.3 创建和修改存储过程
在能够在你的用户应用程序上实际使用一个存储过程之前，你必须创建一个存储过程。
这个创建过程事实上是相当简单的。需要做的第一件事是写一个你将要在存储过程内部运行
的查询。例如，如果你打算创建一个存储过程用于向用户返回复合S E L E C T语句的结果，首先
要做的是编写和测试S E L E C T语句。在已经创建并测试了S E L E C T语句后，就能够用C R E AT E
P R O C E D U R E语句实际创建这个存储过程。
第2 0学时存储过程1 9 5
下载
1 9 6 SQL Server 7 24学时教程
20.3.1 创建存储过程
C R E ATE PROCEDURE语句用于从已测试过的S Q L语句来实际创建一个存储过程。事实
上这是一个相当简单的过程。
C R E ATE PROCEDURE语句的语法如下:
选项如下:
选项描述
< p r o c e d u r e n a m e > 这是你想创建的存储过程的名字。我们稍后将介绍命名存储过程
的约定
< @ p a r a m e t e r > 如果你想向存储过程传递参数，你必须在存储过程的声明部分定
义它们。声明包括参数名、参数的数据类型以及一些其他的特殊选
项
< d a t a t y p e > 如果你想指定一个参数，你必须声明它的数据类型。它可以是任
何有效的数据类型，包括文本和图像类型
[ VA RY I N G ] 当你把游标作为参数返回时，要指定该选项。这个选项告诉S Q L
S e r v e r对于返回游标的行集合将会发生改变
[= default] 这个选项用于指定特定参数的缺省值。如果过程被执行的时候这
个参数没有赋值，将使用本缺省值来取代。本值可以是N U L L值，
或是其他符合该数据类型的合法值。对于字符串数据，如果该参数
是与L I K E参数联合使用的话，该值可以包括含通配符
[ O U T P U T ] 这一可选关键字用于指定该参数是输出参数。当过程执行完成后，
该参数值能被返回到正在执行的过程里。文本或图像数据类型不能
作为输出参数使用
[，⋯] 这一符号指明你可以在一个存储过程中指定多个参数。SQL Server
在单个存储过程中最多可有1 024个参数
WITH RECOMPILE 这个选项强制SQL Server在每一次执行存储过程时都重新编译。当
使用临时值和对象时，你应该使用它
WITH ENCRY P T I O N 这一选项强制SQL Server对存储在系统备注表中的存储过程文本
进行加密。这就允许你创建和重新分布数据库，而不用担心用户
会获得存储过程的原始代码
WITH RECOMPILE，E N C RY P T I O N 这一选项强制SQL Server重新编译和加密存储过程
A S 表明存储过程的定义将要开始
< s q l s t a t e m e n t s > 它是组成存储过程的不同语句
在我们更进一步讨论创建存储过程之前，需要回过头来看看存储过程的命名约定。所有
SQL Server附带的存储过程都有s p 前缀。这一规则是为系统存储过程保留的。但这并不意味
着SQL Server将阻止你创建带有s p 前缀的用户存储过程。它将仍然允许你创建它们，但是可
能会在使用第三方工具时出问题。大多数第三方工具将忽略全部系统对象，并且因为S Q L
S e r v e r系统存储过程有s p 前缀，这些工具也将忽略他们。经验告诉我们，命名用户存储过程
时，应当使用其他的前缀。在这一学时里，我们将把u s p 前缀指定给用户存储过程。
现在，你已经知道了创建一个存储过程所需要的命令，让我们来看看实际上是如何做的。
我们需要使用一些S E L E C T语句，这些语句在第1 7学时的“数据查询”中已有说明。在程
序清单2 0 - 1所示的例子中，我们将通过查询获得所有作者的名字和电话号码，并把它们转化
下载
为一个名叫usp_get_phone_list 的存储过程。
程序清单20-1 创建简单的存储过程
这将会产生一个名叫usp_get_phone_list 的存储过程。为了运行并检测这个存储过程，打
开另一个查询窗口，键入存储过程名，然后执行它。执行结果如图2 0 - 1所示，正和直接运行
S E L E C T语句的结果一样。
既然存储过程的运行非常有用，那么你可以用存储过程多做点事。例如，如果想通过某
一位作者的姓查知他的电话号码，你该如何做呢? 我们可以传递一个参数到存储过程之中。在
程序清单2 0 - 2中，我们将创建一个名为usp_get_phone 的用户存储过程，使用一个名为
@ l a s t n a m e能够传送进存储过程的参数。我们就可以通过这个参数在a u t h o r s表中找到他的电
话号码。
程序清单20-2 向存储过程传递参数
在这个存储过程中我们所做的是，当SQL Server工作时，告诉SQL Server我们将要传送作
者的姓给它，让它用S E L E C T语句中的一个W H E R E子句去找该作者的电话号码。当运行这个
存储过程时，必须传递作者的姓，否则SQL Server将会返回出错信息，告诉你需要传递参数。
第2 0学时存储过程1 9 7
下载
图20-1 程序清单2 0 - 1的查询
结果
执行结果如图2 0 - 2所示。
在大多数情况下，这是一个非常有用的查询方式。比如说，当我们不知道该如何拼写作
者的姓时，可以通过在S E L E C T查询中输入一个L I K E子句来获取。我们将不得不对用户已传
递到存储过程中的查询字符串做一点控制。所要做的是在这类字符串结尾处加上百分号(％)，
以便SQL Server把它作为通配符。程序清单2 0 - 3就显示了这个存储过程。
程序清单20-3 存储过程中的通配符
1 9 8 SQL Server 7 24学时教程
下载
图20-2 程序清单2 0 - 2的查询
结果
图20-3 程序清单2 0 - 3的查询
结果
这个存储过程允许我们以从姓的第一个字母开始的任何一个部分查询作者姓名，图2 0 - 3
就是查询以D开头的作者姓名的存储过程运行结果。
至此为止，我们已经学习了创建从表中检索数据的存储过程。但是，如果想创建一个将
数据插入表中的存储过程，该怎样做呢？让我们来创建一个向a u t h o r s表里插入数据的存储过
程。在这个存储过程中，我们要求用户提供作者的标识、名和姓。如果用户传递了另外的信
息，会被插入到表中。如果用户没有传递任何其他信息，我们将通过在存储过程的参数中设
置缺省值来处理。程序清单2 0 - 4是创建的一个叫作u s p i n s e r t n e w a u t h o r的存储过程的情况。
程序清单20-4 使用存储过程插入数据
在这个存储过程中我们所要做的是创建所有需要的参数。任何我们不强迫用户插入表的
参数就用缺省值N U L L创建。c o n t r a c t列是用缺省值0创建的。当执行这个存储过程时必须传递
作者的标识、姓名，其他的列则是可选择的。我们可以看到图2 0 - 4中已经执行了这个过程在
第2 0学时存储过程1 9 9
下载
图20-4 程序清单2 0 - 4的查询
结果
表中插入名叫Bill Jones的作者，然后通过运行u s p g e t a u t h o r p h o n e 2可以看到插入到表中的
内容。
也许你会说，你只了解了存储过程的基本知识。是的，但经过大量的练习，我们会熟练
掌握的。
20.3.2 修改存储过程
创建了存储过程之后，不可避免地，根据不同的情况需要对它做出相应的修改。在S Q L
S e r v e r以前的版本中，当需要修改存储过程时，不得不清除该过程再重新创建。当清除存储过
程时，既会失去所有赋予原过程的许可又会丢失所有的关联信息，所谓关联信息就是S Q L
S e r v e r收集的数据库中所有对象的信息，如果存储过程引用了一个表，就说它与这个表关联。
想知道更多有关关联信息的内容，可以在SQL Server Books Online中查询s p d e p e n d s。
微软在SQL Server 7.0中加入了一个非常有用的命令，它可以使用户避免丢失所有这些信
息。这就是A LTER PROCEDURE命令。为了使用这个命令，用户需要重新创建所有与存储过
程有关的代码，但不会丢失许可和关联信息。
一个系统存储过程会帮助用户重新创建与存储过程有关的代码，这个系统存储过程叫
s p _ h e l p t e x t。假设存储过程未加密，这个系统存储过程将返回所有与存储过程有关的代码。
这个命令的语法是：
sp_helptext
这里 就是用户需要从中获取代码的存储过程的名字。从图2 0 - 5可以看
到这个命令的一个例子的输出结果。
现在你有了存储过程中的代码，你能很容易地对其进行修改， A LTER PROCEDURE命令
的语法如下：
2 0 0 SQL Server 7 24学时教程
下载
图20-5 s p h e l p t e x t的运行
结果
第2 0学时存储过程2 0 1
以下是对上面语句选项的解释：
选项描述
< p r o c e d u r e n a m e > 这是你打算修改的存储过程名
< @ p a r a m e t e r > 如果你想向存储过程传递参数，必须先在存储过程的声明部分定
义它们，该声明包括参数名、参数的数据类型和其他使用中所需
的特殊选项
< d a t a t y p e > 如果你指定一个参数，必须指定参数的数据类型，它可以是任何
有效的数据类型，包括文本和图像
[ VA RY I N G ] 当你将游标作为参数返回时，这个选项应被指定
[＝d e f a u l t ] 这个选项用于指定特定参数的缺省值。如果过程在执行的时候这
个参数没有赋值，将使用本缺省值来取代。本值可以是N U L L值，
或是其他符合该数据类型的合法值。对于字符串数据，如果该参
数是与L I K E参数联合使用的话，该值可以包括含通配符
[ O U T P U T ] 这一可选关键字用于指定该参数是输出参数。当过程执行完成后，
该参数值能被返回到正在执行的过程里。文本或图像数据类型不能
作为输出参数使用
[，⋯] 这一符号指明你可以在一个存储过程中指定多个参数。SQL Server
在单个存储过程中最多可有1 024个参数
WITH RECOMPILE 这个选项强制SQL Server在每一次执行存储过程时都重新编译。当
使用临时值和对象时，你应该使用它
WITH ENCRY P T I O N 这一选项强制SQL Server对存储在系统备注表中的存储过程文本
进行加密。这就允许你创建和重新分布数据库，而不用担心用户
会获得存储过程的原始代码
WITH RECOMPILE，E N C RY P T I O N 这一选项强制SQL Server重新编译和加密存储过程
A S 表明存储过程的定义将要开始
< s q l s t a t e m e n t s > 它是组成存储过程的不同语句
例如，要修改我们前面创建的u s p i n s e r t n e w a u t h o r存储过程，将C A作为缺省值插入到
s t a t e列中，可以运行程序清单2 0 - 5中的查询。
程序清单20-5 修改存储过程
这样不需要修改任何关联或许可就可以改变存储过程了。现在我们已经创建了一些存储
过程，让我们来看看它们的运行情况。
20.4 运行存储过程
正如我们所看到的，运行存储过程实际上是很简单的。你可以采用两种方法。第一，可
下载
2 0 2 SQL Server 7 24学时教程
以通过在SQL Server Query Analyzer的查询窗口键入存储过程名来执行。如果仅仅运行存储过
程的话，这种方法是可取的。但是如果在批中用这种方法执行多个存储过程，就会出现问题，
那么我们就必须使用E X E C U T E语句。E X E C U T E语句的语法如下：
E X E C U T E < p r o c e d u r e n a m e >
这里< p r o c e d u r e n a m e >是你所要执行的存储过程的名称。
20.5 删除存储过程
总有一天，你需要将大部分的存储过程从系统中删除。这可能是因为你的生意蒸蒸日上，
超出了原存储过程所提供的功能，或者是因为这个功能已经不再需要了。如果你确定不再需
要由存储过程执行这一功能，就可以把该过程从系统中删除。注意，确定到底还需不需要这
个存储过程所提供的功能，这一点十分重要。因为如果你确实仍需要使用的话，将不得不重
建这个存储过程。删除存储过程可以使用DROP PROCEDURE 命令，其语法如下：
DROP PROCEDURE
这里< p r o c e d u r e - n a m e >是需要从系统中删除的存储过程的名称。记住，这种删除是永久
的，如果你后来发现仍需要使用它的话，则必须重新创建。
20.6 课时小结
如果能够正确地安排，存储过程将成为SQL Server的一个强大特征。存储过程可以根据
你的设置完成从简单的查询到应用程序逻辑控制的任何事情。在这一学时里，我们首先学习
了用C R E ATE PROCEDURE命令创建存储过程和用A LTER PROCEDURE命令修改存储过程，
接着学习了运行存储过程。最后学习了用DROP PROCEDURE命令删除旧的存储过程。
20.7 专家答疑
问题：数据库中有多少存储空间可以用来存储每一个存储过程？
解答：SQL Server并未直接在服务器里给存储过程分配存储空间。你可以根据创建存储
过程所需脚本的大小来估计需要多少存储空间。
问题：在存储过程中放置应用程序逻辑是很糟糕的事情吗？
解答：这负面影响并不是很大。但是在大多数情况下，我们应该将逻辑放置在中间层或
应用程序自身中(也就是说，通常只用SQL Server来存储数据)。
20.8 课外作业
这些思考题和练习题是供你加深理解用的。答案可以在附录“答案”中找到。
20.8.1 思考题
1) 什么是存储过程？
2) 什么命令用来创建存储过程？
3) 可以利用什么在存储过程中插入数据？
4) 可以用什么命令来修改存储过程？
5) 同删除和重建存储过程相比，修改存储过程有什么好处？
下载
6) 什么是关联？
7) 用什么命令从系统中删除存储过程？
20.8.2 练习题
创建一个存储过程，当操作者运行它并传递作者的姓的任一部分后，返回所有关于这个
作者的地址信息。