使用存储过程
1存储过程
2为什么要使用存储过程
3执行存储过程
4创建存储过程
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' );
1
2
3
4
5
分析:这里执行一个名为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;
1
2
3
4
5
6
7
分析
此存储过程没有参数。调用 程序检索SQL Server的返回代码提供的值。其中DECLARE语句声明了一个名为@cnt的局部变量(SQL Server中所有局部变量都以@ 起头);然后在SELECT语句中使用变量,让它包含COUNT()函数返回的值;最后,用RETURN@cnt语句将计数返回给调用程序。
调用SQL Server例子可以像下面这样
DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount;
SELECT @ReturnValue;
1
2
3
分析:这段代码声明了一个变量来保存存储过程返回的任何值,然后执行存储过程,在使用SELECT语句显示返回的值