MySQL5存储过程（三）
新SQL语句
Variables 变量
在复合语句中声明变量的指令是DECLARE。
(1) Example with two DECLARE statements
两个DECLARE语句的例子
WHILE ... END WHILE CREATE PROCEDURE p8 () BEGIN DECLARE a INT; DECLARE b INT; SET a = 5; SET b = 5; INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; // /* I won't CALL this */
在过程中定义的变量并不是真正的定义，你只是在BEGIN/END块内定义了而已（译注：也就是形参）。
注意这些变量和会话变量不一样，不能使用修饰符@你必须清楚的在BEGIN/END块中声明变量和它们的类型。
变量一旦声明，你就能在任何能使用会话变量、文字、列名的地方使用。
(2) Example with no DEFAULT clause and SET statement
没有默认子句和设定语句的例子
CREATE PROCEDURE p9 () BEGIN DECLARE a INT /* there is no DEFAULT clause */; DECLARE b INT /* there is no DEFAULT clause */; SET a = 5; /* there is a SET statement */ SET b = 5; /* there is a SET statement */ INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; // /* I won't CALL this */
有很多初始化变量的方法。如果没有默认的子句，那么变量的初始值为NULL。你可以在任何时候使用SET语句给变量赋值。
(3) Example with DEFAULT clause
含有DEFAULT子句的例子
CREATE PROCEDURE p10 () BEGIN DECLARE a, b INT DEFAULT 5; INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; //
我们在这里做了一些改变，但是结果还是一样的。在这里使用了DEFAULT子句来设定初始值，这就不需要把DECLARE和SET语句的实现分开了。
(4) Example of CALL
调用的例子
mysql> CALL p10() // +--------+ | s1 * a | +--------+ | 25 | | 25 | +--------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
结果显示了过程能正常工作
(5) Scope
作用域
CREATE PROCEDURE p11 () BEGIN DECLARE x1 CHAR(5) DEFAULT 'outer'; BEGIN DECLARE x1 CHAR(5) DEFAULT 'inner'; SELECT x1; END; SELECT x1; END; //
现在我们来讨论一下作用域的问题。例子中有嵌套的BEGIN/END块，当然这是合法的。同时包含两个变量，名字都是x1，这样也是合法的。内部的变量在其作用域内享有更高的优先权。当执行到END语句时，内部变量消失，此时已经在其作用域外，变量不再可见了，因此在存储过程外再也不能找到这个声明了的变量，但是你可以通过OUT参数或者将其值指派 给会话变量来保存其值。
调用作用域例子的过程：
mysql> CALL p11()// +-------+ | x1 | +-------+ | inner | +-------+ +-------+ | x1 | +-------+ | outer | +-------+
我们看到的结果时第一个SELECT语句检索到最内层的变量，第二个检索到第二层的变量
Conditions and IF-THEN-ELSE 条件式和IF-THEN-ELSE
1.
现在我们可以写一些包含条件式的例子：
CREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF; END; //
这里是一个包含IF语句的过程。里面有两个IF语句，一个是IF语句END IF，另一个是IF语句ELSE语句END IF。我们可以在这里使用复杂的过程，但我会尽量使其简单让你能更容易弄清楚。
2.
CALL p12 (0) //
我们调用这个过程，传入值为0，这样parameter1的值将为0。
3.
CREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; <-- IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF; END; //
这里变量variable1被赋值为parameter1加1的值，所以执行后变量variable1为1。
4.
CREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN <-- INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF; END; //
因为变量variable1值为1，因此条件"if variable1 = 0"为假，
IF
……
END IF
被跳过，没有被执行。
5.
CREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN <-- UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF; END; //
到第二个IF条件，判断结果为真，于是中间语句被执行了
6.
CREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; <-- ELSE UPDATE t SET s1 = s1 + 2; END IF; END; //
因为参数parameter1值等于0，UPDATE语句被执行。如果parameter1值为NULL，则下一条UPDATE语句将被执行现在表t中有两行，他们都包含值5，所以如果我们调用p12，两行的值会变成6。
7.
mysql> CALL p12(0)// Query OK, 2 rows affected (0.28 sec) mysql> SELECT * FROM t// +------+ | s1 | +------+ | 6 | | 6 | +------+ 2 rows in set (0.01 sec)
结果也是我们所期望的那样。
CASE 指令
1.
CREATE PROCEDURE p13 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO t VALUES (17); WHEN 1 THEN INSERT INTO t VALUES (18); ELSE INSERT INTO t VALUES (19); END CASE; END; //
如果需要进行更多条件真假的判断我们可以使用CASE语句。CASE语句使用和IF一样简单。
我们可以参考上面的例子。
2.
mysql> CALL p13(1)// Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t// +------+ | s1 | +------+ | 6 | | 6 | | 19 | +------+ 3 rows in set (0.00 sec)
执行过程后，传入值1，如上面例子，值19被插入到表t中。
问题
问题: CALL p13(NULL) //的作用是什么？
另一个：这个CALL语句做了那些动作？
你可以通过执行后观察SELECT做了什么，也可以根据代码判断，在5秒内做出。
答案
mysql> CALL p13(NULL)// Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t// +------+ | s1 | +------+ | 6 | | 6 | | 19 | | 19 | +------+ 4 rows in set (0.00 sec)
答案是当你调用p13时，MySQL插入了另一条包含数值19的记录。原因是变量variable1的值为NULL，CASE语句的ELSE部分就被执行了。希望这对大家有意义。如果你回答不出来，没有问题，我们可以继续向下走。
Loops 循环语句 WHILE ... END WHILE LOOP ... END LOOP REPEAT ... END REPEAT GOTO
下面我们将会创建一些循环。我们有三种标准的循环方式：
WHILE循环，LOOP循环以及REPEAT循环。还有一种非标准的循环方式：GO TO（译者语：最好不要用吧，用了就使流程混乱）。
CREATE PROCEDURE p14 () BEGIN DECLARE v INT; SET v = 0; WHILE v < 5 DO INSERT INTO t VALUES (v); SET v = v + 1; END WHILE; END; //
这是WHILE循环的方式。我很喜欢这种方式，它跟IF语句相似，因此不需要掌握很多新的语法。这里的INSERT和SET语句在WHILE和END WHILE之间，当变量v大于5的时候循环将会退出。使用
"SET v = 0;"
语句使为了防止一个常见的错误，如果没有初始化，默认变量值为NULL，而NULL和任何值操作结果都为NULL。
WHILE ... END WHILE example mysql> CALL p14()// Query OK, 1 row affected (0.00 sec)
以上就是调用过程p14的结果不用关注系统返回是"one row affected"还是"five rows affected"，因为这里的计数只对最后一个INSERT动作进行计数。
WHILE ... END WHILE example: CALL mysql> select * from t; // +------+ | s1 | +------+ .... | 0 | | 1 | | 2 | | 3 | | 4 | +------+ 9 rows in set (0.00 sec)
调用后可以看到程序向数据库中插入了5行。
REPEAT ... END REPEAT CREATE PROCEDURE p15 () BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v >= 5 END REPEAT; END; //
这是一个REPEAT循环的例子，功能和前面WHILE循环一样。区别在于它在执行后检查结果，而WHILE则是执行前检查。（译者语：可能等同于DO WHILE吧）
REPEAT ... END REPEAT: look at the UNTIL： UNTIL的作用 CREATE PROCEDURE p15 () BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v >= 5 <-- END REPEAT; END; //
注意到UNTIL语句后面没有分号，在这里可以不写分号，当然你加上额外的分号更好。
REPEAT ... END REPEAT: calling ：调用
mysql> CALL p15()// Query OK, 1 row affected (0.00 sec) mysql> SELECT COUNT(*) FROM t// +----------+ | COUNT(*) | +----------+ | 14 | +----------+ 1 row in set (0.00 sec)
我们可以看到调用p15过程后又插入了5行记录
LOOP ... END LOOP CREATE PROCEDURE p16 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //
以上是LOOP循环的例子。
LOOP循环不需要初始条件，这点和WHILE循环相似，同时它又和REPEAT循环一样也不需要结束条件。
LOOP ... END LOOP: with IF and LEAVE 包含IF和LEAVE的LOOP循环
CREATE PROCEDURE p16 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN <-- LEAVE loop_label; END IF; END LOOP; END; //
在循环内部加入IF语句，在IF语句中包含LEAVE语句。这里LEAVE语句的意义是离开循环。
LEAVE的语法是LEAVE加循环语句标号，关于循环语句的标号问题我会在后面进一步讲解。
LOOP ... END LOOP: calling ：调用
mysql> CALL p16()// Query OK, 1 row affected (0.00 sec) mysql> SELECT COUNT(*) FROM t// +----------+ | COUNT(*) | +----------+ | 19 | +----------+ 1 row in set (0.00 sec)
调用过程p16后，结果是另5行被插入表t中。
Labels 标号
CREATE PROCEDURE p17 () label_1: BEGIN label_2: WHILE 0 = 1 DO LEAVE label_2; END WHILE; label_3: REPEAT LEAVE label_3; UNTIL 0 =0 END REPEAT; label_4: LOOP LEAVE label_4; END LOOP; END; //
最后一个循环例子中我使用了语句标号。现在这里有一个包含4个语句标号的过程的例子。我们可以在BEGIN、WHILE、REPEAT或者LOOP语句前使用语句标号，语句标号只能在合法的语句前面使用。因此"LEAVE label_3"意味着离开语句标号名定义为label_3的语句或复合语句。
End Labels 标号结束符
CREATE PROCEDURE p18 () label_1: BEGIN label_2: WHILE 0 = 1 DO LEAVE label_2; END WHILE label_2; label_3: REPEAT LEAVE label_3; UNTIL 0 =0 END REPEAT label_3 ; label_4: LOOP LEAVE label_4; END LOOP label_4 ; END label_1 ; //
你也可以在语句结束时使用语句标号，和在开头时使用一样。这些标号结束符并不是十分有用。
它们是可选的。如果你需要，他们必须和开始定义的标号名字一样当然为了有良好的编程习惯，方便他人阅读，最好还是使用标号结束符。
LEAVE and Labels 跳出和标号
CREATE PROCEDURE p19 (parameter1 CHAR) label_1: BEGIN label_2: BEGIN label_3: BEGIN IF parameter1 IS NOT NULL THEN IF parameter1 = 'a' THEN LEAVE label_1; ELSE BEGIN IF parameter1 = 'b' THEN LEAVE label_2; ELSE LEAVE label_3; END IF; END; END IF; END IF; END; END; END;// LEAVE
语句使程序跳出复杂的复合语句。
ITERATE
迭代如果目标是ITERATE（迭代）语句的话，就必须用到LEAVE语句
CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // ITERATE
迭代）语句和LEAVE语句一样也是在循环内部的循环引用，它有点像C语言中的“Continue”，同样它可以出现在复合语句中，引用复合语句标号，ITERATE（迭代）意思是重新开始复合语句。
那我们启动并观察下面这个循环，这是个需要迭代过程的循环：
ITERATE: Walking through the loop
深入循环
CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP <-- IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //
让这个已经定义了标号的循环运行起来。
ITERATE: Walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN <-- SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //
v的值变成3，然后我们把它增加到4。
ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; <-- END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //
然后开始ITERATE（迭代）过程。
ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP <-- IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //
这里的ITERATE（迭代）让循环又回到了循环的头部。
ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; <-- END IF; END LOOP; END; //
当v的值变为5时，程序将执行LEAVE语句
ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // <--
LEAVE的结果就是跳出循环，使运行指令到达复合语句的最后一步。
GOTO CREATE PROCEDURE p... BEGIN ... LABEL label_name; ... GOTO label_name; ... END;
MySQL的存储过程中可以使用GOTO语句。虽然这不是标准SQL语句，而且在这里建立标号的方法也和惯例中的不一样。由于为了和其他DBMS兼容，这个语句会慢被淘汰，所以我们在MySQL参考手册中没有提及。
Grand combination
大组合
CREATE PROCEDURE p21 (IN parameter_1 INT, OUT parameter_2 INT) LANGUAGE SQL DETERMINISTIC SQL SECURITY INVOKER BEGIN DECLARE v INT; label goto_label; start_label: LOOP IF v = v THEN LEAVE start_label; ELSE ITERATE start_label; END IF; END LOOP start_label; REPEAT WHILE 1 = 0 DO BEGIN END; END WHILE; UNTIL v = v END REPEAT; GOTO goto_label; END;//
上面例子中的语句包含了我们之前讲的所有语法，包括参数列表，特性参数，BEGIN/END块复合语句，变量声明，IF，WHILE，LOOP，REPEAT，LEAVE，ITERATE，GOTO。这是一个荒谬的存储过程，我不会运行它，因为里面有无尽的循环。但是里面的语法却十分合法。这些是新的流程控制和变量声明语句。下面我们将要接触更多新的东西。
