存储过程编程4 - 军军小站|张军博客

一个快速教程

让我们来看几个例子，来演示 MYSQL 存储程序语言结构和功能的关键要素。对于一个完整的例子，请参阅第二章。

1.2.1 和 SQL 的集成

MYSQL 存储程序语言的最重要方面是它和 SQL 的紧密集成。你不需要依靠诸如 ODBC （开放式数据库连接）或者 JDBC （ Java 数据库连接）等中间软件“胶水”，在你的存储程序语言程序中，来构建和执行 SQL 语句。相反，你只需简单的在你的代码中直接编写 UPDATE 、 INSERT 、 DELETE 和 SELECT 语句，如例 1-1 所示

Example1-1 在存储程序中嵌入 SQL

1 CREATE PROCEDURE example1( )

2 BEGIN

3 DECLARE l_book_count INTEGER;

5 SELECT COUNT(*)

6 INTO l_book_count

7 FROM books

8 WHERE author LIKE '%HARRISON,GUY%';

10 SELECT CONCAT('Guy has written (or co-written) ',

11 l_book_count ,

12 ' books.');

14 -- Oh, and I changed my name, so...

15 UPDATE books

16 SET author = REPLACE (author, 'GUY', 'GUILLERMO')

17 WHERE author LIKE '%HARRISON,GUY%';

19END

让我们在下表中更加详细的看一下这些代码：

行号	解释
1	本段是程序的头部，定义了我们存储程序的名称 (example1) 和类型 (PROCEDURE)
2	BEGIN 关键字表示程序体的开始。程序体包括了过程中的声明和可执行代码。如果程序体包含的语句多余 1 条 ( 就像这个程序 ) ，那么多条语句应该括在一个 BEGIN-END 块中
3	这里，我们声明了一个整数变量，来保存下面我们将要执行的数据库查询返回的结果
5-8	我们运行了一个查询，来确定由 Guy 撰写或者合编的书籍总数。需要特别注意第 6 行：在 SELECT 语句中出现的 INTO 子句充当了从数据库到本地存储程序变量之间的“桥梁”。
10-12	我们使用了一个简单 SELECT 语句 ( 例如，没有 FROM 子句 ) 来显示书籍的数量。当我们发出一个不带 INTO 的 SELECT 语句时，它返回的结果集就会直接返回到调用它的程序。这是一个 non-ANSI 扩展，来允许存储程序轻松的返回结果集（使用 SQLServer 和其他 RDBMS 工作时的一个常见场景）。
14	这是一个单行注释，来解释 UPDATE 语句的目的
15-17	Guy 已经决定把他名字拼写成” Guillermo” 。他可能被 Oracle 书籍爱好者追踪，所以我们对这个书籍表发出了一个 UPDATE 命令。我们使用内置的 REPLACE 函数来找到所有” GUY” 的实例，并使用” GUILLERMO” 来更换它。

控制和条件逻辑

当然，现实世界中的应用程序充满了复杂的条件和特殊情况，因此你不大可能只简单的执行一系列的 SQL 语句。存储程序语言提供了完备的控制和条件语句，所以在一个给定的环境下，我们能够控制我们程序的执行路径。这些包括：

IF 和 CASE 语句

这两个语句使用不同的结构都实现了条件逻辑。它们允许你像这样描述逻辑“如果一本书的页数大于 1000 ，那么 ......”

完整的循环和迭代控制

它们包含了简单循环、 while 循环、 repeatuntil 循环。

例 1-2 是一个账户的支付票据 (paysout the balance of an account to cover outstandingbills) 的过程，来演示 MYSQL 的控制语句。

1 CREATE PROCEDURE pay_out_balance

2 (account_id_in INT)

3

4 BEGIN

5

6 DECLARE l_balance_remaining NUMERIC(10,2);

7

8 payout_loop:LOOP

9 SET l_balance_remaining = account_balance(account_id_in);

10

11 IF l_balance_remaining < 1000 THEN

12 LEAVE payout_loop;

13

14 ELSE

15 CALL apply_balance(account_id_in, l_balance_remaining);

16 END IF;

17

18 END LOOP;

19

20END

让我们在下表中更加详细的看一下这些代码：

行号	解释
1-3	这是我们的过程的头部；第 2 行包括了该过程的参数列表，在本例中，包含了一个输入参数（帐号的标识符编号）
6	声明了一个变量来保存帐号余额
8-18	这个简短循环 ( 这样称呼是因为它以关键字 LOOP 开始，而不是以 WHILE 或 REPEAT) 一直迭代，直到账户余额低于 1000. 在 MYSQL 中，我们可以对循环进行命名 ( 第 8 行 ,payout_loop) ，这就允许我们使用 LEAVE 语句 ( 见第 12 行 ) 终止特定循环。当离开一个循环后， MYSQL 引擎接下来会执行 ENDLOOP 语句（第 18 行）后的下一个可执行语句。
9	调用 account_balance 函数（必须在先前已经定义）来查询该账户余额。 MYSQL 允许你在另一个存储程序中调用一个存储程序，从而达到代码的重用。因为这个程序 (account_balance) 是一个函数，它返回一个值，并且能够被 MysqlSET 调用并进行赋值。
11-16	如果账户余额低于 $1000 ，这个 IF 语句将终止循环。否则（ ELSE 子句）它将应用于下一个支付。你也可以使用 ELSEIF 子句来构建更加复杂的布尔表达式。
15	调用 apply_balance 过程。这是一个代码重用的例子；我们调用一个例程，而不是把 apply_balance 的逻辑在本过程中重写。

存储函数

存储函数是只返回一个单一值的存储程序，它可以被使用于内建函数可以使用的任何地方，例如，在 SQL 语句中。例 1-3 在提供了生日后，返回一个人的年龄。

Example1-3 根据出生日期计算年龄的存储函数

1CREATE FUNCTION f_age (in_dob datetime) returns int

2 NO SQL

3BEGIN

4 DECLARE l_age INT;

5 IF DATE_FORMAT(NOW( ),'00-%m-%d') >=DATE_FORMAT(in_dob,'00-%m-%d') THEN

6 -- This person has had a birthday this year

7 SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y');

8 ELSE

9 -- Yet to have a birthday this year

10 SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;

11 END IF;

12 RETURN(l_age);

END;

让我们在下表中逐步看一下这些代码：

行号	解释
1	定义一个函数：它的名称、输入参数（一个日期），和返回值（一个整数）
2	这个函数中不包含 SQL 语句。对于使用这个子句有一些争议，第 3 章和第 10 章有更多的讨论。
4	声明了一个本地变量来保存我们计算的年龄结果。
5-11	IF-ELSE-ENDIF 块来检验今年的生日是否已经过了的问题。
7	如果今年生日已经过了，我们可以使用今年减去出生日期来简单的来计算生日。
10	否则 ( 也就是今年的生日还没过 ) 我们需要在我们计算的年龄中减去附加的 1 年。
12	向调用程序返回计算的年龄。

在任何内置函数可以使用的地方，我们都可以使用我们的存储函数。在另一个存储程序中、在一个 SET 语句中，或者像例 1-4 显示的那样，在一个 SQL 语句中

例 1-4. 在一个 SQL 语句中使用存储函数（续）

mysql>SELECT firstname,surname, date_of_birth, f_age(date_of_birth)AS age

-> FROM employees LIMIT 5;

+-----------+---------+---------------------+------+

+-----------+---------+---------------------+------+

|LUCAS | FERRIS | 1984-04-17 07:04:27 | 21 |

|STAFFORD | KIPP | 1953-04-22 06:04:50 | 52 |

|GUTHREY | HOLMES | 1974-09-12 08:09:22 | 31 |

|TALIA | KNOX | 1966-08-14 11:08:14 | 39 |

|JOHN | MORALES | 1956-06-22 07:06:14 | 49 |

+-----------+---------+---------------------+------+

当事情出错 (WhenThings Go Wrong)

即使我们程序已经经过了测试，并且没有 BUG ，用户输入也会引起出错。 MYSQL 存储程序语言提供了一个很强大的机制来处理错误。在例 1-5 中，我们创建了一个过程，该过程创建一个新的产品编号，或者如果这个产品编号已经存在，那么就为它更新一个新名字。这个过程使用异常处理来检测试图插入一个重复值错误。如果尝试插入失败，错误将会被捕获，同时一个 UPDATE 语句将代替 INSERT 被发出。如果没有异常处理器，存储程序执行会被终止，异常将会原封不动的返回给调用程序。

Example1-5 存储程序中的错误处理

1 CREATE PROCEDURE sp_product_code

2 (in_product_code VARCHAR(2),

3 in_product_name VARCHAR(30))

5 BEGIN

7 DECLARE l_dupkey_indicator INT DEFAULT 0;

8 DECLARE duplicate_key CONDITION FOR 1062;

9 DECLARE CONTINUE HANDLER FOR duplicate_key SETl_dupkey_indicator =1;

11 INSERT INTO product_codes (product_code, product_name)

12 VALUES (in_product_code, in_product_name);

14 IF l dupkey_indicator THEN

15 UPDATE product_codes

16 SET product_name=in_product_name

17 WHERE product_code=in_product_code;

18 END IF;

20END

让我们详细的看一下这些代码的错误处理部分：

行号	解释
1-4	存储过程头部，允许两个 IN 参数：产品编号和产品名称
7	声明一个变量，我们用它来检测重复键冲突的发生。这个变量被初始化为 0(false) ，随后的代码能够确保只有在重复键冲突发生时，它才会被设置为 1(true)
8	定义一个命名条件， duplicate_key ，让它关联 MYSQL 错误码 1062 。虽然这一步不是绝对必要，但是我们建议你这样定义条件，来提高代码的可读性（现在你可以引用错误名称，而不是错误码）
9	定义一个错误处理器，它将捕获重复键错误，然后，在后面的代码的任何地方，如果发生重复键冲突，则把变量 l_dupkey_indicator 设置为 1(true)
11-12	使用用户提供的编号和名称，插入一个新的产品
14	检查变量 l_dupkey_indicator 的值。如果它仍然为 0 ，那么 INSERT 操作已经成功，我们的任务已经完成。如果它的值已经被修改成了 1(true) ，我们知道，这里发生了重复键冲突，那么我们就运行第 15-17 行的 UPDATE 语句，来改变指定编号的产品名称。

错误处理是编写健壮的、可维护的 MYSQL 存储程序的重要方面。第 6 章将给你一个更加完善的例子，它包含了 MYSQL 存储程序中各种错误处理机制。

触发器

触发器是一个存储程序，它响应于数据库内部的一个事件而被自动调用。在 MYSQL5 的实现中，触发器只响应一个特定表上的 DML 动作而被调用。触发器可以自动计算衍生的或者不规范的数据。例 1-6 展示了一个触发器，它保持这样一个衍生值，当雇员的工资发生变动时， contrib_401K 列的值也会自动的设置为适当的值。

例 1-6 保持衍生值的触发器

1 CREATE TRIGGER employees_trg_bu

2 BEFORE UPDATE ON employees

3 FOR EACH ROW

4 BEGIN

5 IF NEW.salary <50000 THEN

6 SET NEW.contrib_401K=500;

7 ELSE

8 SET NEW.contrib_401K=500+(NEW.salary-50000)*.01;

9 END IF;

10 END

下表中说明了这个相当简短的触发器：

行号	解释
1	触发器有一个独一无二的名字。通常，你希望这样来命名触发器，从而解释其本质。例如，这个触发器名称中的” bu” 表示这是一个 BEFOREUPDATE 触发器。
2	定义触发器被激活的条件。在这个例子中，触发代码在对 employees 表执行 UPDATE 语句之前执行。
3	FOREACHROW 表示触发代码将会在由 DML 语句所影响的每一行上都执行一次。在目前的 MYSQL5 的触发器实现上，这一条款是强制性的。
4-10	BEGIN-END 块定义了触发器被触发时执行的代码
5-9	自动填充 employees 表的 contrib_401K 列。如果 salary 列的新数值小于 50000 ， contrib_401K 列被设置为 500 ，否则，该值如所示第 8 行的方式计算。

当然，关于 MYSQL 存储程序语言有更加多的多的东西，这就是为什么在本书中你有数百页材料来学习。但是，对于你将要使用存储程序语言来编写的这种代码，这些最初的例子应该给你一些好感，其最重要的语法元素和易用性，使得你可以读写这些存储程序语言代码。

--------------------------------------------------------------------------------

第一次尝试翻译一些东西，希望得到大家的支持。如果有什么错误，请和我交流。

本书书名：《 MySQLStored Procedure Programming 》，作者： StevenFeuerstein, Guy Harrison

这个翻译是供我学习 MYSQL 以及和同行交流的，不作为商业用途。

存储过程编程4

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义