How to loop thrugh a stored procedure in MySQL?

MySQLMySQLi Database

Let us see how to loop through a stored procedure in MySQL

mysql> DELIMITER //
mysql> CREATE PROCEDURE do_WhileDemo(LastValue INT)
   -> BEGIN
      -> SET @loop = 0;
      -> REPEAT
         -> SET @loop= @loop+ 1;
         -> select @loop;
         -> UNTIL @loop >LastValue
      -> END REPEAT;
   -> END //
Query OK, 0 rows affected (0.17 sec)
mysql> DELIMITER ;

Now call the stored procedure with the help of CALL command.

The query is as follows

mysql> call do_WhileDemo(10);

The following is the output

+-------+
| @loop |
+-------+
| 1     |
+-------+
1 row in set (0.00 sec)
+-------+
| @loop |
+-------+
| 2     |
+-------+
1 row in set (0.01 sec)
+-------+
| @loop |
+-------+
| 3     |
+-------+
1 row in set (0.02 sec)
+-------+
| @loop |
+-------+
| 4     |
+-------+
1 row in set (0.03 sec)
+-------+
| @loop |
+-------+
| 5     |
+-------+
1 row in set (0.04 sec)
+-------+
| @loop |
+-------+
| 6     |
+-------+
1 row in set (0.04 sec)
+-------+
| @loop |
+-------+
| 7     |
+-------+
1 row in set (0.05 sec)
+-------+
| @loop |
+-------+
| 8     |
+-------+
1 row in set (0.06 sec)
+-------+
| @loop |
+-------+
| 9     |
+-------+
1 row in set (0.07 sec)
+-------+
| @loop |
+-------+
| 10    |
+-------+
1 row in set (0.08 sec)
+-------+
| @loop |
+-------+
| 11    |
+-------+
1 row in set (0.09 sec)
Query OK, 0 rows affected (0.10 sec)
raja
Published on 29-Mar-2019 14:49:19
Advertisements