Mysql tutorial loop and condition judgment SQL query statement writing, we all know that only mysql 5 appears after the stored procedure, these usage, then we need to use if else while in SQL, you have to use a stored procedure or function for instance.
Mysql> DELIMITER //
Mysql> create function myFunction (quantity INT (10) returns int (10)
-> BEGIN
->
-> WHILE quantity MOD 12> 0 DO
-> SET quantity = quantity + 1;
-> End while;
->
-> RETURN quantity;
->
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
Mysql> DELIMITER;
Mysql>
Mysql> select myFunction (10 );
+ ---------------- +
| MyFunction (10) |
+ ---------------- +
| 12 |
+ ---------------- +
1 row in set (0.00 sec)
Mysql>
Mysql> select myFunction (24 );
+ ---------------- +
| MyFunction (24) |
+ ---------------- +
| 24 |
+ ---------------- +
1 row in set (0.00 sec)
Instance 2
Mysql> delimiter //
Mysql> create procedure test_while (IN in_count INT)
-> BEGIN
-> Declare count INT default 0;
->
-> While count <10 do
-> Set count = count + 1;
-> End while;
->
-> Select count;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
Mysql>
Mysql> delimiter;
Mysql>
Mysql> call test_while (10 );
+ ------- +
| Count |
+ ------- +
| 10 |
+ ------- +
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Conditional judgment
Mysql> delimiter $
Mysql>
Mysql> create procedure myProc ()
-> BEGIN
->
-> DECLARE I int;
-> SET I = 1;
-> Loop1: WHILE I <= 10 DO
-> If mod (I, 2) <> 0 THEN/* Even number-try again */
-> Select concat (I, "is an odd number ");
-> End if;
-> SET I = I + 1;
-> End while loop1;
-> END $
Query OK, 0 rows affected (0.00 sec)
Mysql>
Mysql> delimiter;
Mysql> call myProc ();
+ ------------------------------- +
| CONCAT (I, "is an odd number") |
+ ------------------------------- +
| 1 is an odd number |
+ ------------------------------- +
1 row in set (0.02 sec)
+ ------------------------------- +
| CONCAT (I, "is an odd number") |
+ ------------------------------- +
| 3 is an odd number |
+ ------------------------------- +
1 row in set (0.02 sec)
+ ------------------------------- +
| CONCAT (I, "is an odd number") |
+ ------------------------------- +
| 5 is an odd number |
+ ------------------------------- +
1 row in set (0.02 sec)
+ ------------------------------- +
| CONCAT (I, "is an odd number") |
+ ------------------------------- +
| 7 is an odd number |
+ ------------------------------- +
1 row in set (0.02 sec)
+ ------------------------------- +
| CONCAT (I, "is an odd number") |
+ ------------------------------- +
| 9 is an odd number |
+ ------------------------------- +
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.38 sec)