As we all know, the stored procedure only appears after mysql 5. For these usage, we need to use if else while in SQL to use the 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)