MySQL cycle and conditional judgment SQL query syntax

MySQL tutorial cycle and conditional judgment SQL query Writing, we all know that only the MySQL 5 after the stored procedures, these usages, then we want in the SQL with if else while these, we have to use the stored procedure or function to instance.

Mysql> DELIMITER//
Mysql> CREATE FUNCTION myfunction (quantity int) RETURNS int (10)
-> BEGIN
->
-> while quantity MOD > 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)

Example Two

Mysql> delimiter//
Mysql> CREATE PROCEDURE Test_while (in In_count INT)
-> BEGIN
-> declare count INT default 0;
->
-> while Count < ten 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)

With conditional judgment.

Mysql> delimiter $$
Mysql>
mysql> CREATE PROCEDURE myProc ()
   -> BEGIN
&N bsp;  
   ->     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 a 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 a odd number") |
+-------------------------------+
| 1 is a odd number |
+-------------------------------+
1 row in Set (0.02 sec)

+-------------------------------+
| CONCAT (i, "is a odd number") |
+-------------------------------+
| 3 is a odd number |
+-------------------------------+
1 row in Set (0.02 sec)

+-------------------------------+
| CONCAT (i, "is a odd number") |
+-------------------------------+
| 5 is a odd number |
+-------------------------------+
1 row in Set (0.02 sec)

+-------------------------------+
| CONCAT (i, "is a odd number") |
+-------------------------------+
| 7 is a odd number |
+-------------------------------+
1 row in Set (0.02 sec)

+-------------------------------+
| CONCAT (i, "is a odd number") |
+-------------------------------+
| 9 is a odd number |
+-------------------------------+
1 row in Set (0.02 sec)

Query OK, 0 rows affected (0.38 sec)