Saturday, 23 August 2014

Use of loops in MySQL stored procedures

Loops -> Repetitive tasks

3 types of loops in MySQL stored program:
- Simple loop using LOOP and END LOOP clauses
- WHILE and END WHILE clauses that runs while a condition is true
- REPEAT  and UNTIL clauses that runs until a condition is true



Termination of loop -> LEAVE statement.

I will demonstrate the use of loops the simple loop way.

Simple counter from 1 to 10:

Store below stored procedure in a file named my_loop.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS my_loop$$

CREATE PROCEDURE my_loop()
BEGIN
    DECLARE counter INT DEFAULT 0;

    my_loop: LOOP
        SET counter = counter + 1;

        IF counter = 10 THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;

    SELECT CONCAT('Done counting up to ', counter) AS result;

END$$

DELIMITER ;

Execute the stored procedure:

mysql> SOURCE my_loop.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL my_loop();
+------------------------+
| result                 |
+------------------------+
| Done counting up to 10 |
+------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Explaining simply, we are defining a counter that starts with 0 and a LOOP labelled my_loop that increments from 0 onward and a condition inside to check for counter value of 10, when the loop terminates through the use of LEAVE clause. You can give a try to the other loop clauses :)

3 comments:

  1. Being new to web development, have not come yet to any need, so as to use them. But nice to know about it, thanks.

    ReplyDelete