9.2.1 存储过程概述

存储过程是SQL语句和流程控制语句的预编译集合，是以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序调用执行，而且允许用户声明变量、有条件地执行其他强大的编程功能。用户通过指定存储过程的名字并给出参数（如果该存储过程有参数）来执行。存储过程是数据库的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

1. 存储过程的优点

(1) 存储过程允许标准组件式编程。

存储过程在创建后可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。数据库专业人员可随时对应用程序进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含调用存储过程的语句），从而极大地提高了程序的可移植性。

(2) 存储过程能够实现较快的执行速度。

如果某个操作包含大量的SQL语句或被多次执行，那么存储过程要比SQL语句批处理的执行速度快很多。因为存储过程是预编译的，所以在首次运行存储过程时，查询优化器会对其进行分析、优化，并将得到的执行计划存储在系统表中。而批处理的SQL语句在每次运行时都要进行编译和优化，速度相对较慢。

(3) 存储过程能够减少网络流量。

对于同一个针对数据库对象的操作（如查询、修改），如果这一操作所涉及的SQL语句被组织成一个存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是调用存储过程的语句，而不是多条SQL语句。

(4) 存储过程可以作为一种安全机制来被充分利用。

数据库系统管理员可以对某一存储过程的权限进行限制，从而实现对相应数据访问权的限制，避免非授权用户对数据的访问，保证数据的安全。

2. 存储过程的分类

SQL Server 2012中的存储过程可以分为三类。

1）系统存储过程

系统存储过程是在SQL Server 2012安装成功后，就已经存储在master数据库中，这些存储过程都是以sp_为前缀命名的。它们提供了有效的查询系统表的方法，以及许多系统管理功能，如sp_help、sp_databases、sp_helpdatabases、sp_tables、sp_helpconstraint等。

尽管这些系统存储过程被放在master数据库中，但仍然可以在其他数据库中直接调用，调用时，不需要在存储过程前面加上数据库名，因为在创建新数据库时，一些系统存储过程会在新数据库中被自动创建。常用的系统存储过程如表9-12所示。

(1) sp_helpdb。

语法格式为：

sp_helpdb [ [ @dbname= ] '数据库名' ]

如果没有指定数据库名，则sp_helpdb报告master.dbo.sysdatabases中的所有数据库。

例如，返回pub数据库的信息，语句为：

exec sp_helpdb pubs

(2) sp_tables。

语法格式为：

sp_tables [ @table_type=" '类型' " ]

其中：类型包括TABLE（用户表）、SYSTEM TABLE（系统表）和 VIEW（视图）。

例如，返回在当前环境中可查询的对象的列表，语句为：

EXEC sp_tables

例如，返回当前数据库中的所有用户表，语句为：

EXEC sp_tables @table_type="'TABLE'"

(3) sp_helptext。

语法格式为：

sp_helptext存储过程数据库

2）用户自定义存储过程

用户自定义存储过程是由用户创建并能完成某一特定的功能（如查询用户），是封装了可重用代码的SQL语句模块。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据库定义语言（DDL）和数据库操作语言（DML）语句，以及返回输出参数。

存储过程虽然既有参数，又有返回值，但是它与函数不同，存储过程的返回值只是指明执行是否成功，且它不能像函数那样被直接调用，即在调用存储过程时，必须在存储过程前加EXEC保留字。本章主要介绍用户自定义存储过程的创建和管理，在SQL Server 2012的用户自定义存储过程里有T-SQL或者CLR两种类型，如表9-13所示。

3）扩展存储过程

扩展存储过程是通过在SQL Server环境外执行的动态链接库来实现的，可以加载到SQL Server实例运行的地址空间。扩展存储过程可以使用SQL Server 扩展存储过程API完成编程。扩展存储过程以前缀“xp_”来标识，对用户来说，扩展存储过程和普通存储过程一样，可以采用相同的方式执行。