MySQL储存过程编程教程－第二章

drekey

第二章 MySQL储存过程编程指南

MySQL储存过程编程是一个复杂的主题，我们将在本章中为你提供完成基本任务的内容，其中包括：

• 怎样创建储存程序
• 储存程序怎样进行输入输出
• 怎样和数据库交互
• 怎样用MySQL储存编程语言创建过程，函数和触发器

我们不会在本章中对主题进行深入，这些内容的主要不敌仅仅是让你对储存过程程序有一个大体的映像，在随后的章节中，我们将对本章的内容进行升华

drekey

2.8 和数据库交互

大多数储存过程包含了各种和数据库表的交互，它们包括四种主要的交互：

• 将一个SQL表达式所返回的单个记录放入本地变量中
• 创建一个“游标”来迭代SQL表达式所返回的结果集
• 执行一个SQL表达式，将执行后的结果集返回给它的调用程序
• 内嵌一个不返回结果集的SQL表达式，如INSERT, UPDATE, DELETE等

我们暂时来大致的看一下这几种和数据库交互的情况。

为了能运行本节的示例，你必须安装和本书配套的sample数据库，这个可以在本书的网站找到（详见前言）。

2.8.1对本地变量使用SELECT INTO

当需要在单个记录数据中获取查询信息，你就可以使用SELECT INTO语法（无论是使用单个记录，多个记录的混合数据，还是多个表连接）。在这种情况下，你可以在SELECT表达式中跟随一个INTO子句，告诉MySQL得到的查询数据返回给谁
Figure 2-10 演示了更具customer ID的不同来获取和显示销售量的储存过程。 Figure 2-6 是执行结果

Figure 2-10在储存过程中使用SELECT INTO表达式

Example 2-6执行包含SELECT INTO表达式的储存过程

1. mysql> CALL customer_sales(2) $$
   
2. +--------------------------------------------------------------+
   
3. | CONCAT('Total sales for ',in_customer_id,' is ',total_sales) |
   
4. +--------------------------------------------------------------+
   
5. | Total sales for 2 is 7632237                                 |
   
6. +--------------------------------------------------------------+
   
7. 1 row in set (18.29 sec)
   
8. 
   
9. Query OK, 0 rows affected (18.29 sec)
   
10. 
    

复制代码

2.8.2 使用游标

SELECT INTO定义了单记录查询，但是很多应用程序要求查询多记录数据，你可以使用MySQL中的游标来实现这一切，游标允许你将一个或更多的SQL结果集放进储存程序变量中，通常用来执行结果集中各个但记录的处理。
在Figure 2-11中，储存程序使用游标来捕获所有employees表的记录
下面是对于代码的详细解释

Figure 2-11. 在储存过程中使用游标

行号	解释
8-12	声明本地变量，前面的三个是用来存放SELECT表达式的结果。第四个（done）能让我们确认所有的记录行都已被读取
14-16	定义我们的游标，这是基于一个简单SELECT表达式从employees表中所返回的结果集
18	声明一个“handler”，它定义了当我们无法从SELECT表达式得到更多记录时的行为。handler可以用来捕获所有的错误类型，但是像示例中所演示的handler只是在我们需要的时候警告我们已经没有更多的记录可以被读取而已
20	打开游标
21-26	用一个简单循环来从游标中获取所有的记录
22	用FETCH子句将从游标中获取单个记录，然后放进我们的本地变量中。
23-25	检测变量done的值，如果它被设置成1，那么就说明我们已经获取了最后一个数据，那么我们就用LEAVE表达式来终止循环。

2.8.3 返回结果集的储存过程

在这本书的前些部分，我们已经在和储存过程和数据库的交互中使用过一些个并不包含INTO子句和游标的沉长的SELECT表达式，它们被用于在储存过程中返回一些状态数据和结果集，迄今为止，我们只使用过单记录结果集，但是你也可以在储存过程中包含一些复杂的SQL表达式来返回多个结果。

如果我们在MySQL命令行中执行这样的储存过程，结果集将像我们执行SELECT和SHOW表达式一个被返回。Figure 2-12向我们展示了包含了沉长的SELECT表达式的储存过程

Figure 2-12 包含沉长SELECT表达式的储存过程

如果我们在执行这个储存过程时为其参数提供适当的值，那么包含SELECT的储存过程将被返回，在Figure 2-13中我们将看到用MySQL Query Browser执行的包含SELECT表达式的储存过程将结果返回的状况。

Figure 2-13 包含沉长SELECT表达式的储存程序的结果返回状况

注意：储存过程调用可能返回多个结果集，这给程序调用带来了挑战，我们将在第13章到第17章中的每个部分中分别来介绍相关的情况

2.8.4 内建不返回结果的SQL表达式

不返回结果集的“简单”的SQL表达式也可以被嵌入储存程序中，包含DML（数据操纵语言）如UPDATE,INSERT和DELETE以及DDL（数据定义语言）如CREATE TABLE等都可以被包括在内，当然，某些特定的用来创建和控制储存程序的表达式并不允许被使用，这将在第5章阐述。

Figure 2-14演示了包含更新操作的储存过程，其中的UPDATE表达式被某些验证逻辑所封装来达到阻止非有效数据输入的目的

Figure 2-14 内嵌UPDATE的储存过程

drekey

2.5 条件执行

你可以用IF或者CASE表达式来控制储存程序的执行流程。它们具有相同的功能，因此，我们只用示例演示了IF（CASE的功能是相同的）。

Figure2-8 演示了通过购买量的多少来计算出贴现率的储存程序，Example2-5演示了它的执行结果，购买量超过$500可以返还20%，购买量超过$100可以返还10%。

Figure 2-8使用IF表达式的条件执行

Example 2-5 创建和执行包含IF表达式的储存过程

1. mysql> SOURCEdiscounted_price.sql
   
2. Query OK, 0 rows affected (0.01 sec)
   
3. 
   
4. Query OK, 0 rows affected (0.00 sec)
   
5. 
   
6. mysql> CALLdiscounted_price(300,@new_price) $$
   
7. Query OK, 0 rows affected (0.00 sec)
   
8. 
   
9. mysql> SELECT@new_price$$
   
10. +------------+
    
11. | @new_price |
    
12. +------------+
    
13. | 270.0      |
    
14. +------------+
    
15. 1 row in set (0.00 sec)
    
16. 
    

复制代码

IF表达式允许你测试表达式的真实性（就像normal_price > 500），并且基于表达式的结果执行一定的行为，作为一种编程语言，ELSEIF可以被用来作为IF起始循环的条件转移，ELSE字句将在IF和ELSEIF字句的布尔表达式为假时执行。

CASE具有相同的功能，并且当你对单个表达式进行对比是可以获得更清晰的值，这两个条件表达式将在第4章做更为细致的探究和比对。

drekey

2.1 你所需要的工具

下面的工具是你运行本书中的示例代码所需要的工具：

• MySQL 5 server
• 一个文本编辑器（vi, emacs或者 notepad）
• MySQL Query Browser（MySQL 官方GUI TOOLS内的查询工具）

您能够在http://dev.mysql.com上面得到MySQL Server 及 MySQL Query Browser

drekey

2.2 第一个储存过程

开始，我们将创建一些非常简单的储存过程，你将需要一个编辑环境来写储存过程，还有一些工具把你的储存过程请求发送给MySQL服务器。

你可以使用任何文本编辑器，下面提供了一些MySQL的代码提交工具：

• MySQL命令行客户端
• MySQL Query Browser
• 第三方工具，例如Toad for MySQL

在这片文章中，我们假定你没有安装任何工具，所以我们是用了古老却又经典的MySQL命令行客户端。
让我们用root帐户登录localhost的3306端口，我们将在Example 2-1使用MySQL预安装的“test”数据库。

Example 2-1 连接MySQL命令行客户端

1. [gharriso@guyh-rh4-vm2 ~]$mysql -uroot -psecret -hlocalhost
   
2. Welcome to the MySQL monitor.  Commands end with ; or \g.
   
3. Your MySQL connection id is 1 to server version: 5.0.16-nightly-20051017-log
   
4. 
   
5. Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   
6. 
   
7. mysql>

复制代码

2.2.1 创建储存过程

你可以用CREATE PROCEDURE , CREATE FUNCTION ,或者CREATE TRIGGER 表达式来创建储存程序。可以直接把这些表达式直接输入MySQL命令行，但是对于一般的储存程序大小而言，这有些不太实际，所以我们建议你使用文本编辑器创建一个文本文件来容纳我们的储存程序，然后我们就可以使用命令行客户端和其他工具来递交这个文件。

我们将使用MySQL Query Browser作为文本编辑器，如果你没有这个工具，你可以从http://dev.mysql.com/downloads/得到，你也可以使用任何操作系统上的编辑器例如vi, emacs或者notepad，当然我们喜欢MySQL Query Browser的原因是它具备内建的帮助系统，语法高亮，执行SQL表达式的能力以及其他一些功能。
遵照下面的步骤

• 运行MySQL Query Browser,在Windows上，从开始菜单中选择程序->MySQL->MySQL Query Browser。在Linux上从终端中输入mysql-query-browser
• 从菜单中选择File->New Script tab来创建一个空白的脚本窗口
• 输入你的储存程序代码

Figure 2-1 显示了我们的第一个储存过程：
我们可以使用File->Sava As菜单来把我们的文件保存，这样就可以用mysql客户端来执行它

Figure 2-1 第一个储存程序


第一个储存过程非常的简单，但是还是让我们一行行的来解释确保你能够完整的理解他们

行号	解释
1	DELIMITER命令确保把‘$$’作为表达式的终结条件，通常，MySQL会把“；”作为表达式的终结，但是因为储存过程在其过程体中
3	DROP PROCEDURE IF EXISTS表达式用来确保在同名储存过程已经存在的情况下将其移除，如果我们不这样做，那么在同名储存过程已存在的情况下将收到一个MySQL的修改重复执行的错误
4	CREATE PROCEDURE表达式指示一个储存过程定义的开始，注意,储存过程名“HelloWorld”的后面跟这一对内容为空的圆括号“（ ）”。如果储存过程有任何参数，那么我们就可以把参数放在里面。但是如果没有参数，我们同样要把圆括号放上，否则，我们将会收到一个语法错误
5	BEGIN表达式指示了储存程序的开始，所有超过一个表达式的储存程序必须用至少一个BEGIN-END块来定义储存程序的开始和结束
6	这是储存过程中的一个单个表达式：一个SELECT表达式将“Hello World”返回给它的调用程序，马上将像我们看到的一样，储存程序中的SELECT能够向控制台和调用程序返回数据，就像我们直接把SELECT表达式输入MySQL命令行一样
7	END结束储存过程的定义。注意用$$来结束对储存过程的定义，这样MySQL就知道我们完整的结束了CREATE PROCEDURE表达式

随着对储存过程的定义结束，我们可以用mysql客户端创建并执行我们的HelloWorld储存过程，就像Example 2-2所展示的那样：
Example 2-2. 创建我们第一个储存过程

1. $ mysql -uroot -psecret -Dprod
   
2. Welcome to the MySQL monitor.  Commands end with ; or \g.
   
3. Your MySQL connection id is 16 to server version: 5.0.18-nightly-20051208-log
   
4. 
   
5. Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   
6. 
   
7. mysql> SOURCE HelloWorld.sql
   
8. Query OK, 0 rows affected, 1 warning (0.01 sec)
   
9. 
   
10. Query OK, 0 rows affected (0.00 sec)
    
11. 
    
12. mysql> CALL HelloWorld(  ) $$
    
13. +-------------+
    
14. | Hello World |
    
15. +-------------+
    
16. | Hello World |
    
17. +-------------+
    
18. 1 row in set (0.01 sec)
    
19. 
    
20. Query OK, 0 rows affected (0.01 sec)
    
21. 
    
22. mysql>

复制代码

下面是我们用于完成这一切MySQL命令的解释:

命令	解释
SOURCE HelloWorld.sql	从指定的文件中读取命令，在这个例子中，我们用MySQL Query Browser制定了我们所保存的文件，没有错误返回，这说明我们已经成功的创建了储存过程
CALL HelloWorld(  ) $$	执行储存过程，我们成功的执行了储存过程并且返回了“Hello World”作为结果集。我们用‘$$’来作为CALL命令的终结，这是因为DELIMITER的设置仍然在起作用

2.2.2 用MySQL Query Browser创建储存过程

在这个指南以及整本书中，我们要用一些过时的工具–MySQL命令行终端创建大多数的储存程序代码示例。而你要做的仅仅是复制这些代码片段。因此，你可以是用一些图形化的工具来创建储存程序：网上有大量使用的第三方MySQL图形化工具，其中一个很好的选择是MySQL Query Browser,你可以在此得到http://dev.mysql.com/downloads/

在这一章节中我们将给出如何是用MySQL Query Browser创建储存过程，MySQL Query Browser对于创建储存过程更为友好，虽然目前为止并非所有的操作系统平台都支持这一工具，所以你可以使用MySQL命令行或者其他第三方工具来代替
在Windows上，从开始菜单中选择程序->MySQL->MySQL Query Browser。在Linux上从终端中输入mysql-query-browser
当查询工具被打开，它会提示你输入MySQL服务器的连接信息，然后将显示一个空白的图形化窗口。你可以使用菜单项Script->Create Stored Procedure/Function菜单创建储存程序，它会提示你按照名称来创建储存程序，然后会显示一个空白的储存程序模板Figure 2-2显示了这样一个模板的例子:

Figure 2-2用MySQL Query Browser创建储存过程

你可以在适当的位置储存过程代码（在BEGIN和END表达式之间，光标将被自动的置于合适的位置方便你的输入）。当你输入完成，你可以简单的按下Execute按钮来执行储存过程，如果你的代码发生了错误，Query Browser将在底部显示错误并用高亮标识发生错误的行，否则，你将在左侧的Schemata选项卡中发现你的储存过程已被成功的创建

Figure 2-3用Execute按钮执行储存程序

我们希望这个清晰的示例对于你用MySQL Query Browser创建和执行储存程序起到帮助，Query Browser提供了一个简便的储存程序开发环境，但这一切都取决于你如何使用Query Browser，第三方工具和你喜欢的编辑器及MySQL命令行终端。

drekey

2.3 变量

本地变量可以用DECLARE表达式进行声明。变量名称必须遵循MySQL的列名规则，并且可以使MySQL内建的任何数据类型。你可以用DEFAULT字句给变量一个初始值，并且可以用SET语句给变量赋一个新值，就像Figure 2-5所展示的那样。

Figure 2-5.在储存过程中使用变量

drekey

2.4 参数
我们大多数所写的储存程序都会包括一两个参数。参数可以使我们的储存程序更为灵活，更为实用，下面，让我们创建一个包含参数的储存过程

Figure 2-4在Query Browser中执行储存过程

Figure 2-6的储存过程接受一个整型数input-number作为参数,并且计算出了这个数的平方根，计算出的结果作为返回的结果集。

把参数放置在紧随过程名的圆括号内，每一个参数都有自己的名称，数据类型还有可选的输入输出模式，有效的模式包括IN（只读模式），INOUT（可读写模式）和OUT（只写模式）。因为IN模式作为缺省的参数模式，所有没有出现在 Figure 2-6当中。

我们将通过示例对参数模式进行细致的观察。

此外，MySQL储存程序引入了两种有关参数的不同的特性：

DECLARE

一个用于创建储存程序内部使用的本地变量，在这个示例中，我们创建了一个名为l_sqrt的浮点数。

Figure 2-5在储存过程中使用变量

SET

一个用来给变量赋值的表达式，在这个示例中我们将参数的平方根（使用内置的SQRT函数）赋于那个用DECLARE命令声明的变量。

我们可以在MySQL客户端中执行并测试储存过程的运行结果，就像Example 2-3所做的那样

Example 2-3 创建并执行使用参数的储存过程

1. mysql> SOURCEmy_sqrt.sql
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. Query OK, 0 rows affected (0.00 sec)
   
5. 
   
6. mysql> CALLmy_sqrt(12)$$
   
7. +-----------------+
   
8. | l_sqrt          |
   
9.     +-----------------+
   
10. | 3.4641016151378 |
    
11. +-----------------+
    
12. 1 row in set (0.12 sec)
    
13. 
    
14. Query OK, 0 rows affected (0.12 sec)

复制代码

Figure 2-6一个使用 参数的储存过程

2.4.1参数模式

MySQL的参数模式可以被定义为IN，OUT和INOUT。
IN

这是缺省的模式，它说明参数可以被传入储存程序内部，但是任何对于该参数的修改都不会被返回给调用它的程序。

OUT

这个模式意味着储存程序可以对参数赋值（修改参数的值），并且这个被修改的值会被返回给它的调用程序。

INOUT

这个模式意味着储存程序既可以读取传入的参数，而且任何对于该参数的修改对于它的调用程序而言都是可见的
你可以在储存过程的参数中使用上述所有的模式，但是对于储存函数而言，你只能使用IN模式（参考随后的“储存函数”章节）。

让我们改变这个平方根程序，使它将计算结果放到OUT值中去，就像Figure 2-7所做的

Figure 2-7 在储存过程中使用OUT参数

在MySQL客户端中，我们提供了一个用来保存值的OUT参数，当储存过程执行完毕，我们可以回头检验这个变量的输出情况，就像Example 2-4.

Example 2-4 创建和执行使用OUT参数的储存过程

1. mysql> SOURCEmy_sqrt2.sql
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. Query OK, 0 rows affected (0.02 sec)
   
5. 
   
6.     mysql> CALLmy_sqrt(12,@out_value) $$
   
7. Query OK, 0 rows affected (0.03 sec)
   
8. 
   
9. mysql> SELECT@out_value $$
   
10. +-----------------+
    
11. | @out_value      |
    
12. +-----------------+
    
13. | 3.4641016151378 |
    
14. +-----------------+
    
15. 1 row in set (0.00 sec)

复制代码

drekey

2.6 循环

循环允许在你的储存程序中重复性的执行某些行为，MySQL储存程序语言提供了三种类型的循环

• 使用LOOP和END LOOP字句的简单循环
• 当条件为真时继续执行的循环，使用WHILE和END WHILE字句
• 循环直至条件为真，使用REPEAT和UNTIL字句

在这三种循环中，你都可以使用LEAVE子句来终止循环
在三种循环都将在第4章详细解释；我们只会在这个指南中给出LOOP-LEAVE-END LOOP（简单循环）的例子
Figure 2-9 简单循环演示：

Figure 2-9.储存过程中的简单循环

下面是对于代码的详细解释

行号	解释
7	声明了一个名为counter，初始值为0的简单数字变量
9-14	简单循环，所有在LOOP和END LOOP之间的部分都将在LEAVE子句被执行后终止
9	LOOP表达式带有前缀为my_simple_loop的标签，LEAVE子句要求循环被标识，这样才能知道要退出哪个循环
10	给counter变量的值增加1
11-13	测试counter的值，如果值为10，则退出循环，否则，我们继续下一个迭代
15	我们骄傲的宣称我们可以数到10

drekey

2.7 错误处理

当储存程序发生错误时，MySQL默认的行为是终止程序的执行并把错误返回给它的调用程序。如果你需要以不同的方式来相应错误，你可以定义一个或多个可以被储存程序所响应的错误情况
如下两个相关联的情景被称为错误处理的定义：

• 如果你认为内嵌的SQL表达式会返回空记录，或者你想用游标捕获所有SELECT表达式所返回的记录，那么一个NOT FOUND错误处理可以防止储存程序过早的被终止
• 如果你认为SQL表达式可能返回错误（比如：违背约束条件），你可以创建一个错误处理来阻止程序终止。这个处理将代替你的默认错误处理并继续程序的执行。

第6章将详细解释错误处理，在下一节中我们将演示一个使用NOT FOUND错误处理并结合游标显示的例子。

drekey

2.9 在其他储存程序中调用储存程序

在其他储存程序中调用储存程序是很简单的。就像你在MySQL命令行客户端中所做的一样，仅仅需要使用CALL表达式
Figure 2-15演示了一个通过输入参数来选择不同储存过程的简单的例子。储存过程的输出（l_bonus_amount是一个外界传入的OUT类型的参数）将被传递给第三个过程。

下面是对代码段的解释

行号	解释
11	判断employee是否是manager，如果他是manager，我们就调用储存过程calc_manager_bonus；如果他不是manager，那么我们就调用calc_minion_bonus
12和14	这两个储存过程都被传递入employee_id，并且提供了变量l_bonus_amount来接受储存过程的输出
16	用employee_id和bonus amount（用我们在12或14行计算出的值）作为参数调用储存过程grant_bonus

Figure 2-15 从另一个储存过程中调用储存过程

drekey

2.10 把所有的东西组装起来

Example 2-7 向我们演示了所有已经提到过的存储程序语言特性的例子

Example 2-7. 更为复杂的存储过程

1. 1 CREATE PROCEDURE putting_it_all_together(in_department_id INT)
   
2. 2 MODIFIES SQL DATA
   
3. 3 BEGIN
   
4. 4 DECLARE l_employee_id INT;
   
5. 5 DECLARE l_salary NUMERIC(8,2);
   
6. 6 DECLARE l_department_id INT;
   
7. 7 DECLARE l_new_salary NUMERIC(8,2);
   
8. 8 DECLARE done INT DEFAULT 0;
   
9. 9
   
10. 10 DECLARE cur1 CURSOR FOR
    
11. 11 SELECT employee_id, salary, department_id
    
12. 12 FROM employees
    
13. 13 WHERE department_id=in_department_id;
    
14. 14
    
15. 15
    
16. 16 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    
17. 17
    
18. 18 CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises
    
19. 19 (employee_id INT, department_id INT, new_salary NUMERIC(8,2));
    
20. 20
    
21. 21 OPEN cur1;
    
22. 22 emp_loop: LOOP
    
23. 23
    
24. 24 FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
    
25. 25
    
26. 26 IF done=1 THEN /* No more rows*/
    
27. 27 LEAVE emp_loop;
    
28. 28 END IF;
    
29. 29
    
30. 30 CALL new_salary(l_employee_id,l_new_salary); /*get new salary*/
    
31. 31
    
32. 32 IF (l_new_salary<>l_salary) THEN /*Salary changed*/
    
33. 33
    
34. 34 UPDATE employees
    
35. 35 SET salary=l_new_salary
    
36. 36 WHERE employee_id=l_employee_id;
    
37. 37 /* Keep track of changed salaries*/
    
38. 38 INSERT INTO emp_raises (employee_id,department_id,new_salary)
    
39. 39 VALUES (l_employee_id,l_department_id,l_new_salary);
    
40. 40 END IF;
    
41. 41
    
42. 42 END LOOP emp_loop;
    
43. 43 CLOSE cur1;
    
44. 44 /* Print out the changed salaries*/
    
45. 45 SELECT employee_id,department_id,new_salary from emp_raises
    
46. 46 ORDER BY employee_id;
    
47. 47 END;

复制代码

这是我们迄今写过的最为复杂的存储过程，所以让我们一行行的解释：

行号	解释
1	创建一个过程。它包括一个参数in_department_id。因为我们没有制定OUT或INOUT模式，所以输入参数是IN模式的（这意味着，调用程序不能读取任何参数在存储过程内的改动状况）。
4-8	声明在存储过程内部的本地变量。最后一个参数done给出了一个初始值0.
10-13	创建一个用于获取employees表中数据行的游标。存储程序只获取那些被参数department_id传入的员工
16	创建一个用于处理“not found”情况的错误处理，这样当最后一个数据行被游标捕获，程序就不会被错误所终止。
18	创建一个临时表来保存被存储过程所作用的数据列表。这个表就像所有的临时表一样被创建于会话中，也就是说会随着会话的结束所终结。
21	为返回的记录结果打开游标
22	创建一个循环来为每个由存储过程返回的结果执行行为，这个循环终止与42行
24	将游标中的结果集装入我们在存储过程内部预定义的本地变量。
26-28	声明一个IF条件以确保当变量done的值为1时终止循环（这个工作由“not found”处理来完成，意味着所有的行都已被捕获）。
30	调用存储过程new_salary来计算出员工的新薪水。它接受一个employee_id作为输入参数和一个接受新薪水（l_new_salary）的输出参数。
32	将30行中由调用过程计算出的员工新薪水和在第10行由游标返回的薪水值相比对。如果不同，则执行第32到40行的代码块
34-36	用过程new_salary计算出的新薪水值代替现有的员工薪水。
38和39	在临时表中插入数据行来记录薪水的调整状况（定义域21行）
43	在所有的数据行都被处理完毕后，关闭游标
45	用一个沉长的SELECT语句（比方说没有WHERE子句的）来作用于临时表，获取哪个员工的薪水被更新。因为这个SELECT表达式没有使用游标或INTO子句，所以数据行将被作为结果集返回给它的调用程序
47	终止存储过程

当这个存储过程被传入一个参数18，并在MySQL命令行客户端中执行时，一个更新的薪水列表将被打印，见Example 2-8
Example 2-8 样例“putting it all together”的输出情况

1. mysql> CALL cursor_example2(18) //
   
2. +-------------+---------------+------------+
   
3. | employee_id | department_id | new_salary |
   
4. +-------------+---------------+------------+
   
5. | 396 | 18 | 75560.00 |
   
6. | 990 | 18 | 118347.00 |
   
7. +-------------+---------------+------------+
   
8. 2 rows in set (0.23 sec)
   
9. 
   
10. Query OK, 0 rows affected (0.23 sec)

复制代码

drekey

2.11 存储函数

存储函数和存储过程很相像：它们都是包含一个或多个MySQL表达式的被命名程序单元。和存储过程不同的地方有以下几点：

• 函数的参数列表中模式只能为IN。OUT和INOUT参数不被允许。制定IN关键字是被允许也是缺省的
• 函数必须返回一个值，它的类型被定义于函数的头部
• 函数能被SQL表达式所调用
• 函数可能不返回任何结果集

大体来说，在程序的真正目的是比对值和需要返回值时或者你希望在SQL表达式中创建用户自定义函数的时候更多的考虑使用存储函数，而不是存储过程。

Figure 2-16 演示了用一个存储函数来实现我们在早些章节中提到过的discount_price 存储过程所有的一些功能

Figure 2-16. 存储函数

下表揭示了一些在这个存储函数中和存储过程相等价的事物：

行号	解释
7	作为函数的定义，制定RETURNS子句。它制定了函数的返回类型
8	MySQL相较于存储过程，对于存储函数有更严格的规则。函数必须声明不修改SQL（使用NO SQL或者READS SQL DATA子句）或者声明为DETERMINISTIC（如果服务器被允许开启二进制日志）。这个限制是为了防止当函数返回不确定值时，数据库同步复制的不一致性（详见第10章），我们的样例例程使用了“deterministic”，这样我们就能确保在提供了相同的参数的情况下返回相同的值
21	用RETURN表达式返回由IF表达式计算出的折扣率

Example 2-9 展示了在SQL表达式中调用这个函数

Example 2-9 在SQL表达式中调用函数

1. mysql> SELECT f_discount_price(300) $$
   
2. +-----------------------+
   
3. | f_discount_price(300) |
   
4. +-----------------------+
   
5. |                 270.0 |
   
6. +-----------------------+
   
7. 
   

复制代码

我们可以在其他存储程序中（过程，函数和触发器）或者任何我们可以使用MySQL内建函数的地方。

drekey

2.12 触发器

触发器是一种在数据库表被INSERT，UPDATE或者DELETE等(DML)表达式所作用时所激活的特殊的存储程序。只要表发生改变就会激活触发器的功能，因为触发器直接依附于表，所以程序代码无法绕过数据库触发器（没有办法让触发器失效）。

通常，触发器被用来实现严格的商业逻辑，高效的反向格式化数据和审核表的更改状况。触发器可以被定义触发于特定的DML表达式执行之前或之后。

在Figure 2-17，我们创建了一个在对sales表进行INSERT操作完成前的触发器。它将自动对指定值进行零售和折扣率的计算

Figure 2-17 一个数据库触发器

下面是对触发器定义的解释

行号	解释
5	定制触发器名称
6	指定触发器将于sales表的insert操作之前被激活
7	在当前的代码段中FOR EACH ROW子句说明触发器的表达式将对每个插入到sales表中的行发生作用
8	使用BEGIN来开始包含由触发器执行的代码
9-13	如果sale_value的值大于$500,则设置free_shipping列的值为‘Y’，否则，设置为‘N’。
15-19	如果sale_value的值大于$1000，则对该值进行15%的折扣率计算并插入到discount列中。否则设置折扣率为0.

触发器对于free_shipping和discount列值的作用会自动进行。考虑一下Example 2-10所展示的INSERT表达式
Example 2-10 对sales表的数据插入

1. INSERT INTO sales
   
2.       (customer_id, product_id, sale_date, quantity, sale_value,
   
3.        department_id, sales_rep_id)
   
4. VALUES(20,10,now(  ),20,10034,4,12)
   
5. 
   

复制代码

销售价为$10034,满足了15%零售和折扣率的条件。Example 2-11示范了触发器正确的设置了这些值。

Example 2-11 触发器自动设置了free_shipping 和 discount 列的值

1. mysql> SELECT sale_value,free_shipping,discount
   
2.     ->   FROM sales
   
3.     ->  WHERE sales_id=2500003;
   
4. +------------+---------------+----------+
   
5. | sale_value | free_shipping | discount |
   
6. +------------+---------------+----------+
   
7. |      10034 | Y             |     1505 |
   
8. +------------+---------------+----------+
   
9. 1 row in set (0.22 sec)
   
10. 
    

复制代码

使用触发器来维护free_shipping和discount列确保了列值的正确设置，无论这个SQL表达式是用PHP，C#或者Java还是MySQL命令行客户端执行的。

drekey

2.13 在PHP中调用存储过程

我们已经向大家展示了如何用MySQL命令行客户端，MySQL Query Browser和其他存储程序来调用存储程序。在实际的开发过程中，你更有可能要从别的语言环境中来调用我们的存储过程，例如PHP，Java，Perl或者.NET。我们将用专门的章节来讨论如何在这些环境中调用存储程序（第12章到第17章）。

现在，让我们看看如何用PHP，这种与MySQL关系最亲密的开发环境来调用存储过程。
当使用PHP和MySQL交互时，我们可以使用与数据库无关的PEAR:: DB扩展工具，mysqli（MySQL "improved"）或者是最近的POD（PHP数据对象）工具。在这个例子中，我们是用了mysqli。第13章将详细讲述这些扩展工具。
Figure 2-19 展示了在PHP中连接MySQL服务器和调用存储过程的代码，我们不会对代码进行深入，只是希望它能给你如何在WEB应用程序和别的应用中使用存储程序留下映像。

Figure 2-18. 在PHP中调用存储过程

Figure 2-19 PHP程序调用存储过程的示例

PHP程序提示用户指定部门ID；然后调用存储过程employee_list来获取该部门的员工名单 Figure 2-20展示了PHP + 存储过程例子的输出状况

Figure 2-20 PHP示例的输出状况

drekey

2.14 第二章结语

在这一章中，我们呈现了一个明朗的“基础指南”并介绍了一些基本的MySQL存储程序。我们向你展示了以下特点：

• 创建“Hello World”存储过程
• 定义本地变量和过程参数
• 用IF表达式进行条件执行
• 用简单循环进行迭代处理
• 包含SQL表达式的存储过程，包括如何使用游标对各个数据行进行处理
• 从其他存储程序中调用存储程序
• 创建存储函数（以及存储函数和存储过程的区别）
• 为表的反响格式化创建触发器
• 从PHP中调用存储过程

现在你也许已经可以放下书本开始写一些MySQL存储程序了，我们对于你急切的心情表示赞同。但是我们可不可以提一个小小的建议：你可不可以先花更多的时间来阅读接下来各个章节中有关功能的具体内容，这样，你可以写出更少错误，更高质量的代码。