mysql procedure存储过程循环，条件判断实例

mysql教程 procedure存储过程循环，条件判断实例

mysql> delimiter $$
mysql> CREATE PROCEDURE myProc()
-> DETERMINISTIC
-> BEGIN
-> DECLARE counter INT DEFAULT 0;
->
-> simple_loop: LOOP
-> SET counter=counter+1;
-> select counter;
-> IF counter=10 THEN
-> LEAVE simple_loop;
-> END IF;
-> END LOOP simple_loop;
-> SELECT 'I can count to 10';
-> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> call myProc();
+---------+
| counter |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)

+---------+
| counter |
+---------+
| 2 |
+---------+
1 row in set (0.02 sec)

+---------+
| counter |
+---------+
| 3 |
+---------+
1 row in set (0.02 sec)

+---------+
| counter |
+---------+
| 4 |
+---------+
1 row in set (0.02 sec)

+---------+
| counter |
+---------+
| 5 |
+---------+
1 row in set (0.02 sec)

+---------+
| counter |
+---------+
| 6 |
+---------+
1 row in set (0.02 sec)

+---------+
| counter |
+---------+
| 7 |
+---------+
1 row in set (0.02 sec)

+---------+
| counter |
+---------+
| 8 |
+---------+
1 row in set (0.02 sec)

+---------+
| counter |
+---------+
| 9 |
+---------+
1 row in set (0.33 sec)

+---------+
| counter |
+---------+
| 10 |
+---------+
1 row in set (0.33 sec)

+-------------------+
| I can count to 10 |
+-------------------+
| I can count to 10 |
+-------------------+
1 row in set (0.33 sec)

Query OK, 0 rows affected (0.33 sec)

实例二

mysql> CREATE PROCEDURE myProc()
-> BEGIN
-> DECLARE i int;
-> SET i=1;
-> myloop: LOOP
-> SET i=i+1;
-> IF i=10 THEN
-> LEAVE myloop;
-> END IF;
-> END LOOP myloop;
-> SELECT 'I can count to 10';
->
-> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> call myProc();
+-------------------+
| I can count to 10 |
+-------------------+
| I can count to 10 |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec

带有条件

mysql> create procedure increment (IN in_count INT)
-> BEGIN
-> declare count INT default 0;
->
-> increment: loop
-> set count = count + 1;
-> if count < 20 then
-> iterate increment;
-> end if;
-> if count > in_count then
-> leave increment;
-> end if;
-> end loop increment;
-> select count;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call increment(3);
+-------+
| count |
+-------+
| 20 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)