MySQL - Statement Labels

The BEGIN ... END Compound Statements in MySQL have multiple statements; each statement ends with a semi colon (or. the current delimiter). These statements start with BEGIN and ends with END.

Stored procedures and functions, triggers, and events are the compound statement in MySQL. A compound statement can contain other blocks such as variable declarations, conditions, cursors, including loops and conditional tests.

We can label the start and end statements of the LOOP, REPEAT and WHILE statements and BEGIN ... END blocks using this.

Syntax

Following is a syntax of the Statement Labels in MySQL −

begin_label: [LOOP | REPEAT |WHILE]
statement_list
[END LOOP | REPEAT |WHILE] end_label

You need to keep the following points in mind while using these statement labels.

• Usually, we have a begin label and end label for these statements.
• The label for begin and end labels must be same.
• Once we have a begin label it is not mandatory to end it.
• If you use nesting labels the names should be different.
• The length of these labels can be up to 16 characters.
• If you need to use a label with in another labeled block or, loop you need to use ITERATE or LEAVE statement.

Example 1

Following query demonstrates the usage of the statement labels with a procedure −

Delimiter //
CREATE procedure loopDemo()
   label:BEGIN
      DECLARE val INT ;
      DECLARE result VARCHAR(255);
      SET val =1;
      SET result = ''; 
      loop_label: LOOP
      IF val > 10 THEN
         LEAVE loop_label;
      END IF;
      SET result = CONCAT(result,val,',');
      SET val = val + 1;
      ITERATE loop_label;
   END LOOP;
   SELECT result;
   END//

You can call the above procedure as follows −

call loopDemo;//

Output

Following is the output of above query −

Example 2

Following is another example −

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; //
mysql> DELIMITER ;

You can call the above created function as shown below −

SELECT Sample(1000);

The above query produces the following output −