存储过程编程4

2. 一个快速教程

   让我们来看几个例子，来演示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;

   4

   5 SELECT COUNT(*)

   6 INTO l_book_count

   7 FROM books

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

   9

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

   11 l_book_count ,

   12 ' books.');

   13

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

   15 UPDATE books

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

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

   18

   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”来更换它。

2. 控制和条件逻辑

   当然，现实世界中的应用程序充满了复杂的条件和特殊情况，因此你不大可能只简单的执行一系列的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的逻辑在本过程中重写。

2. 存储函数

   存储函数是只返回一个单一值的存储程序，它可以被使用于内建函数可以使用的任何地方，例如，在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;

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

   |firstname | surname | date_of_birth | age |

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

   |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 |

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

3. 当事情出错(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))

   4

   5 BEGIN

   6

   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;

   10

   11 INSERT INTO product_codes (product_code, product_name)

   12 VALUES (in_product_code, in_product_name);

   13

   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;

   19

   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存储程序中各种错误处理机制。

4. 触发器

   触发器是一个存储程序，它响应于数据库内部的一个事件而被自动调用。在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以及和同行交流的，不作为商业用途。