使用存储过程
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包含以下执行选择:
- 参数可选,具体不提供参数时的默认值
- 不按次序给出参数,以“参数-值”的方式给出参数值
- 输出参数,允许存储过程在执行的应用程序中更新所用的参数
- 用SELECT语句检索数据
- 返回代码,允许存储过程返回一个值到正在执行的应用程序
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语句显示返回的值。