the new sql statements 新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中。
question
问题
问题: call p13(null) //的作用是什么？
另一个：这个call语句做了那些动作？
你可以通过执行后观察select做了什么，也可以根据代码判断，在5秒内做出。
answer
答案
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。这是一个荒谬的存储过程，我不会运行它，因为里面有无尽的循环。但是里面的语法却十分合法。这些是新的流程控制和变量声明语句。下面我们将要接触更多新的东西。
