在数据库中如何使用存储过程

1存储过程

使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。那怎么编写代码呢？

可以创建存储过程。存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

2为什么要使用存储过程

• 通过把处理封装在容易使用的单元中，简化复杂的操作（正如前面例子所述）。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。

这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。

• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。

• 提高性能。因为使用存储过程比使用单独的SQL语句要快。
• 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码（在下一章的例子中可以看到。）

总的来说，存储过程有3个主要的好处，简单、安全、高性能。

在将SQL代码转换为存储过程前，也必须知道它的一些缺陷

• 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
• 不同的DBMS的存储过程语法有所不同。

3执行存储过程

存储过程的执行远比其定义更经常遇到，因此，我们将从执行存储过程开始介绍。然后再介绍创建和使用存储过程。
执行存储过程的SQL语句很简单，即EXECUTE。EXECUTE接受存储过程名和需要传递给它的任何参数。
实例说话：

EXECUTE AddNewProduct( 'JTS01',  
                       'Stuffed Eiffel Tower',  
                       6.49, 
                       'Plush stuffed toy with the text La 
➥Tour Eiffel in red white and blue' );

分析：这里执行一个名为AddNewProduct的存储过程，将一个新产品添加到Products表中。AddNewProduct有四个参数，分别是：供应商ID（Vendors表的主键）、产品名、价格和描述。这四个参数匹配存储过程中四个预期变量（定义为春初过程自身的组成部分）。此存储过程将新行添加到Products 表中，并将传入的属性赋给相应的列。

在Products表中还有另一个需要值得列prod_id列，它是这个表的主键。
因为要保证恰当的生成此ID，最好是生成此ID的过程自动化（而不依赖于最终用户的输入），所以这个值不作为属性传递给存储过程。

以下是存储过程完成的工作：
1. 验证传递的数据，保证传递的四个参数都有值。
2. 生成用作主键的唯一ID。
3. 将新产品插入Products表，在合适的列中存储生成的主键和传递的数据。

这就是存储过程执行的基本形式。

具体的DBMS包含以下执行选择：

1. 参数可选，具体不提供参数时的默认值
2. 不按次序给出参数，以“参数-值”的方式给出参数值
3. 输出参数，允许存储过程在执行的应用程序中更新所用的参数
4. 用SELECT语句检索数据
5. 返回代码，允许存储过程返回一个值到正在执行的应用程序

4创建存储过程

存储过程编写很重要，先看一个简单的例子，对邮件发送清单中具有邮件地址的顾客进行计数。
以下是SQL Server的版本

CREATE PROCEDURE MailingListCount
AS 
DECLARE @cnt INTEGER 
SELECT @cnt = COUNT(*) 
FROM Customers 
WHERE NOT cust_email IS NULL; 
RETURN @cnt; 

分析
此存储过程没有参数。调用 程序检索SQL Server的返回代码提供的值。其中DECLARE语句声明了一个名为@cnt的局部变量（SQL Server中所有局部变量都以@ 起头）；然后在SELECT语句中使用变量，让它包含COUNT()函数返回的值；最后，用RETURN@cnt语句将计数返回给调用程序。

调用SQL Server例子可以像下面这样

DECLARE @ReturnValue INT 
EXECUTE @ReturnValue=MailingListCount; 
SELECT @ReturnValue; 

分析：这段代码声明了一个变量来保存存储过程返回的任何值，然后执行存储过程，在使用SELECT语句显示返回的值。