MySQL procedure stored procedure loop, conditional judgment instance

MySQL Tutorial procedure stored procedure loop, conditional judgment instance

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 |
+---------+
| 9 2
+---------+
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)

Example Two

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

With conditions

Mysql> CREATE procedure increment (in In_count INT)
-> BEGIN
-> declare count INT default 0;
->
-> Increment:loop
-> Set count = Count + 1;
-> if Count < 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)