Mysql procedure Stored procedure loop, condition judgment instance

Mysql tutorial procedure Stored procedure loop, condition 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 |
+ --------- +
| 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)

Instance 2

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

Conditional

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)