MySQL - LEAVE Statement

---

---

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. These procedures contain IN and OUT parameters, or both. They may return result sets in case you use SELECT statements; they can return multiple result-sets. You can also create functions in MYSQL.

Similar to other programming languages MySQL provides support for the flow control statements such as IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT. You can use these statements in the stored programs (procedures), and RETURN in stored functions. You can use one Flow Control Statement with in another.

MySQL LEAVE Statement

The LEAVE statement in MySQL is used to exit the LOOP, REPEAT, WHILE statements or, BEGIN…END statements. It cannot be used outside these statements.

Syntax

Following is the syntax of the LEAVE statement is MySQL −

LEAVE label

Where, label is the label of the LOOP or, REPEAT or, WHILE statement.

Example 1

Following query demonstrates how to use the LEAVE statement with in a procedure −

Delimiter //
CREATE PROCEDURE demo()
   BEGIN
      DECLARE num INT;
      DECLARE str VARCHAR(50);
      SET num = 1;
      SET str = '';
      label: LOOP
      SET num = num + 1;
      IF num > 16 THEN
         LEAVE label;
      END IF;
      IF (num mod 2) THEN
         ITERATE label;
      ELSE 
         SET str = CONCAT(str, num, ',');
      END IF;
   END LOOP;
   SELECT str;
   END //

You can call the above procedure as shown below −

call demo;//

Output

The above mysql query generates the following output −

str
2,4,6,8,10,12,14,16,

Example 2

Following query demonstrates how to use the LEAVE statement with in a function −

DELIMITER //
CREATE FUNCTION Sample (bonus INT)
   RETURNS INT
   BEGIN
      DECLARE income INT;
      SET income = 0;
      myLabel: LOOP
         SET income = income + bonus;
         IF income < 10000 THEN
            ITERATE myLabel;
         END IF;
         LEAVE myLabel;
      END LOOP myLabel;
   RETURN income;
END; //
DELIMITER ;

You can call the above created function as shown below −

SELECT Sample(1000);

Output

Following is the output of the above program −

Sample(1000)
10000