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 :)

4 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
  2. Stored procedures in database management systems allow you to encapsulate a series of SQL statements into a single reusable unit. How Minecraft Activate While SQL is inherently a set-based language.

    ReplyDelete