в 13:44, 26 мая 2010

2010-05-26T13:44:56Z

Admin: 1 версия

2010-05-26T09:53:29Z

1 версия

Новая страница

== LOOP Statement with LEAVE==

The LOOP statement creates a loop that will run until the LEAVE statement is invoked.
Optional to the LOOP is a label.
A label is a name and a colon prefixed to the LOOP statement.



<source lang="sql">
mysql>
mysql> DELIMITER //
mysql> CREATE FUNCTION myFunction(quantity INT(10)) RETURNS INT(10)
-> BEGIN
->
-> increment: LOOP
->
-> IF quantity MOD 10 < 1 THEN
-> LEAVE increment;
-> END IF;
->
-> SET quantity = quantity - 1;
->
-> END LOOP increment;
->
-> RETURN quantity;
->
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> select myFunction(10);
+----------------+
| myFunction(10) |
+----------------+
| 10 |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)
mysql></source>

== LOOP with ITERATE==



<source lang="sql">
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc()
-> BEGIN
->
-> DECLARE i int;
-> SET i=0;
-> loop1: LOOP
-> SET i=i+1;
-> IF i>=10 THEN /*Last number - exit loop*/
-> LEAVE loop1;
-> ELSEIF MOD(i,2)=0 THEN /*Even number - try again*/
-> ITERATE loop1;
-> END IF;
->
-> SELECT CONCAT(i," is an odd number");
->
-> END LOOP loop1;
->
->
-> END$$
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> delimiter ;
mysql> call myProc();
+-------------------------------+
| CONCAT(i," is an odd number") |
+-------------------------------+
| 1 is an odd number |
+-------------------------------+
1 row in set (0.00 sec)
+-------------------------------+
| CONCAT(i," is an odd number") |
+-------------------------------+
| 3 is an odd number |
+-------------------------------+
1 row in set (0.00 sec)
+-------------------------------+
| CONCAT(i," is an odd number") |
+-------------------------------+
| 5 is an odd number |
+-------------------------------+
1 row in set (0.00 sec)
+-------------------------------+
| CONCAT(i," is an odd number") |
+-------------------------------+
| 7 is an odd number |
+-------------------------------+
1 row in set (0.01 sec)
+-------------------------------+
| CONCAT(i," is an odd number") |
+-------------------------------+
| 9 is an odd number |
+-------------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql></source>

== LOOP with LEAVE==



<source lang="sql">
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc()
-> BEGIN
-> DECLARE i int;
-> SET i=1;
-> myloop: LOOP
-> SET i=i+1;
-> IF i=10 THEN
-> LEAVE myloop;
-> END IF;
-> END LOOP myloop;
-> SELECT "I can count to 10";
->
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> call myProc();
+-------------------+
| I can count to 10 |
+-------------------+
| I can count to 10 |
+-------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql></source>

== Nesting if statement with LOOP statement==



<source lang="sql">
mysql>
mysql> delimiter //
mysql>
mysql> create procedure increment (IN in_count INT)
-> BEGIN
-> declare count INT default 0;
->
-> increment: loop
-> set count = count + 1;
-> if count < 20 then
-> iterate increment;
-> end if;
-> if count > in_count then
-> leave increment;
-> end if;
-> end loop increment;
-> select count;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call increment(3);
+-------+
| count |
+-------+
| 20 |
+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop procedure increment;
Query OK, 0 rows affected (0.00 sec)</source>

== Simple LOOP==



<source lang="sql">
[begin_label:] LOOP
statement_list
END LOOP [end_label]</source>

← Предыдущая	Версия 13:44, 26 мая 2010
(нет различий)

MySQL Tutorial/Procedure Function/LOOP - История изменений

в 13:44, 26 мая 2010

Admin: 1 версия