sqlserver24学时教程连载20-第20学时 存储过程

    第20学时 存储过程

    存储过程是SQL Server上的一个非常有用的特性。存储过程由被编译在一起的一组 SQL语

    句组成，并可以通过调用单个命令来执行。它们对于维护那些在 SQL Server上运行应用程序

    时所需的代码是非常有用的。 SQL Server也预安装了许多系统存储过程，这些系统存储过程

    在服务器上运行，用于收集关于服务器的专门信息。

    本学时的要点包括：

    什么是存储过程

    存储过程的用途

    创建和修改存储过程

    运行存储过程

    删除存储过程

    20.1 什么是存储过程

    正如我们前面提到的，存储过程只是一个 SQL语句组合，它们被编译在一起，能通过单

    个命令而被执行。存储过程能用于向用户返回数据、在表中插入新数据、修改数据、执行系

    统函数和管理任务。总之，它们是 SQL Server中功能非常强大的一部分，它能使你的工作更

    轻松。存储过程具有能自动地在 SQL Server上存储你的应用程序的业务逻辑和函数功能，这

    样在服务器级上就可以对数据做出有关决策，而不是把结果返回给客户机。在过去几年里，

    关于如何用存储过程支持这种功能，出现了两个主要的阵营。

    第一个阵营认为，在存储过程中加入的功能越多越好。他们相信存储过程应能在数据

    库的插入与更新中提供一切，以保持数据的有效性。赞成者的观点是，如果业务规则变化，

    所有要做的仅仅是在服务器级更改存储过程，而不必在客户机级进行修改。反对者的观点是，

    这种解决方案等于把所有的应用程序逻辑放进数据库，会使数据库本身的速度减慢。许多人

    正在从这种解决方案转向下一种。

    第二个阵营认为，所有由实际应用程序来处理的功能都应放在存储过程之外，并在数据

    库之外进行处理。他们基本上认为应该放入存储过程的东西仅仅是实际的更新、插入和删除。

    任何业务逻辑，比如数据有效性的校验，都应放在中间层或放在实际应用程序级 .赞成这种

    解决方案的是那些对速度要求高而且其数据库仅仅是为了存储数据的人。反对者的观点是，

    如果你所有的业务逻辑都存储在前端的应用程序中，任何业务逻辑的变化都将导致程序的重

    写、重编译以及重新分发。另一个主要问题是，当所有的应用程序逻辑都存储在数据库本身

    时，很难把数据库从一个平台移植到另一个 .通常通过使用包含所有业务逻辑的中间层应用

    程序来解决这个问题。

    SQL Server存储过程能通过下列方法之一向用户返回数据 ：

    从任何包含在存储过程中的 SELECT语句中返回的结果集。

    整数值形式的返回代码。

    195第20学时 存 储 过 程

    输出参数，其中包含能被用户应用程序获得的整数或字符数据。

    一个可以在存储过程外引用的游标。

    在SQL Server以前的版本中，存储过程是一种加速 SQL语句执行的方法。这些语句包含

    在存储过程里，它通过部分地编译执行计划来实现加速。存当储过程创建后，部分被编译的

    执行计划就被存储起来。当存储过程执行时，它们将被检索出来并获得优化。当存储过程被

    执行和优化后，执行计划就被存储在高速缓存中。当其他的处理器或用户运行该存储过程时，

    计划被简单地从高速缓存中恢复并执行。

    在此新版本的SQL Server中，存储过程通过一种完全不同的方法得到处理。 SQL Server

    7.0不再为存储过程存储任何执行计划，而是当过程被执行时， SQL Server检索代码、编译、

    优化并且执行它。这时执行计划才被存储在高速缓存里，以便下一步的执行。

    20.2 存储过程的用途

    存储过程的用途几乎是无限的。从返回 SELECT语句的结果并用于用户报表到执行复杂的

    数据有效性校验，你可以用存储过程来做任何事。至于说存储过程到底能为你做什么，部分

    地取决于你是属于前面列出的两个阵营中的哪一个。如果你是坚信所有的应用程序功能都应

    包含在存储过程中的那种人，让存储过程做任何事就成为可能的了。如果你认为 SQL Server

    只应该用于存储数据，这也是一种选择。以下是存储过程的一些用途：

    模块化的程序设计—在你创建了一个存储过程之后，它就会存储在数据库中并能通过

    你的应用程序多次调用它。这些过程能封装数据库功能，也能被应用程序代码独立地修

    改，只要应用程序总是知道被返回的数据将要做什么。

    快速执行—当存储过程被编译并存储在过程高速缓冲中时，它能以相当高的效率执行。

    这是因为SQL Server无需不断地重复编译存储过程。

    网络通信量—你时常会有长达数百行的 SQL语句。如果能把那些功能封装进存储过程，

    就可以通过向SQL Server发送一行代码起到成百行的语句的作用，从而节省大量的网络

    带宽。当仅有少量用户运行应用程序时，你的感受也许不深；但是如果有成千上万的用

    户使用这个应用程序时，你就会深有感触。

    安全性—存储过程能作为一种安全性机制使用。当你授权许可一个用户或一组用户使

    用存储过程，他们将能够执行这个存储过程而不必有访问在存储过程中被访问的下层对

    象的许可。例如，如果你创建了一个存储过程，用于返回 pubs数据库中authors表的所有

    行，然后许可一个名叫乔的人去执行那个存储过程，他就能从authors表中检索出所有行，

    而不必直接地授予他对authors表的访问许可。

    现在你已经知道存储过程能为你做些什么，让我们再来看看如何创建和修改存储过程。

    20.3 创建和修改存储过程

    在能够在你的用户应用程序上实际使用一个存储过程之前，你必须创建一个存储过程。

    这个创建过程事实上是相当简单的。需要做的第一件事是写一个你将要在存储过程内部运行

    的查询。例如，如果你打算创建一个存储过程用于向用户返回复合 SELECT语句的结果，首先

    要做的是编写和测试SELECT语句。在已经创建并测试了 SELECT语句后，就能够用CREATE

    PROCEDURE语句实际创建这个存储过程。

   

[1] [2] [3] 下一页