SQL/MySQL/Procedure Function/While

IF statement in a While loop

   <source lang="sql">

mysql> 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) mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql>

       </source>
   
  

While loop

   <source lang="sql">

mysql> mysql> 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) mysql> mysql> drop procedure test_while; Query OK, 0 rows affected (0.00 sec)

       </source>
   
  

WHILE Statement

   <source lang="sql">

mysql> mysql> 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) mysql> mysql> mysql> drop function myFunction; Query OK, 0 rows affected (0.00 sec) mysql>

       </source>