SQL/MySQL/Procedure Function/While — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16:46, 26 мая 2010
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>