顺序结构
• 当“条件成立”时执行命令序列
• 否则,不执行任何操作
if 条件测试 then
代码......
.....
end if ;
当“条件成立”时执行代码 1
• 否则,执行代码 2
if 条件测试 then
代码 1 ......
.....
else
代码 2......
.....
end if;
例子:
mysql> delimiter //
mysql> create procedure showlines(in numbers int(2))
-> begin
-> select id,name,password from db9.user limit numbers;
-> end
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call showlines(3);
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | root | x |
| 2 | bin | x |
| 3 | daemon | x |
+----+--------+----------+
3 rows in set (0.00 sec)
mysql> call showlines(@s);
Empty set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set @x=4;
Query OK, 0 rows affected (0.00 sec)
mysql> call showlines(@x);
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | root | x |
| 2 | bin | x |
| 3 | daemon | x |
| 4 | adm | x |
+----+--------+----------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
例子2: 条件判断
mysql> delimiter //
mysql> create procedure showlines2(in numbers int(2))
-> begin
-> if numbers is null then
-> select id,name,password from db9.user limit 1;
-> else
-> select id,name,password from db9.user limit numbers;
-> end if;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call showlines2(@s); //数值为空就显示第一行
+----+------+----------+
| id | name | password |
+----+------+----------+
| 1 | root | x |
+----+------+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call showlines2(2);
+----+------+----------+
| id | name | password |
+----+------+----------+
| 1 | root | x |
| 2 | bin | x |
+----+------+----------+
2 rows in set (0.00 sec)
mysql> set @y=3;
mysql> select @y;
mysql> call showlines2(@y); //给数值定义一个变量
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | root | x |
| 2 | bin | x |
| 3 | daemon | x |
+----+--------+----------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
循环结构(有三种格式) 那一种理解好,就用那一种
需要重复执行的命令,就用循环结构
循环格式1:
条件式循环
– 反复测试条件,
– 只要成立就执行命令序列
while 条件判断 do
循环体(执行的命令)
.......
end while ;
例子:
mysql> delimiter //
mysql> create procedure prinum(in number int(2)) //展示数值变量
-> begin
-> declare x int(2) default 1;
-> while x <= number do
-> select x;
-> set x = x+1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call prinum(5);
mysql> call prinum(7);
mysql> call prinum(18);
例子2:
mysql> delimiter //
mysql> create procedure show2name(in numlines int(2)) //检测uid为偶数的值
-> begin
->
-> declare numlines int(2);
-> declare x int(2) default 1;
-> declare useruid int(2);
-> select count(*) into numlines from user;
-> while x <= numlines do
-> select uid into useruid from user where id = x;
-> if useruid % 2 = 0 then
-> select name,uid from user where id=x;
-> end if;
-> set x = x + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call show2name();
mysql> select db,name,type,body from mysql.proc \G; //查看存储mysql的所有存储过程
循环格式2(loop)
loop
执行sql命令
end loop;
死循环
db: db9
name: loop1
type: PROCEDURE
body: begin
declare x int(2) default 1;
loop
select x;
set x = x + 1;
end loop;
end
循环格式3:• 条件式循环
repeat
。。。执行命令
until 条件判断
end repeat ;
– until 条件判断,不成立时结束循
例子:
mysql> delimiter //
mysql> create procedure repeat2()
-> begin
-> declare x int(2) default 1;
-> repeat
-> select x;
-> set x = x + 1;
-> until x = 5
-> end repeat;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call repeat2();
总结:while 是先判断条件再执行命令,repeat 是执行完循环,再执行条件判断
流程控制函数:控制循环结构执行的函数
• 循环结构控制语句 , 控制循环结构的执行。
– LEAVE 标签名 // 跳出循环 (跟shell的break一样)
– ITERATE 标签名 / 放弃本次循环,执行下一次循环(跟shell的contine 是一样)
例子:
输出1~100 的5的倍数和含有5的个数
mysql> delimiter //
mysql> create procedure prinum3()
-> begin
-> declare x int(2) default 1;
-> load1:while x <= 100 do
-> if x % 5 = 0 or x regexp 5 then
-> select x;
-> else
-> set x = x + 1;
-> iterate load1;
-> end if;
-> set x = x + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call prinum3();