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