MySQL LEAVE

 MySQL LEAVE

Summary: in this tutorial, you will learn how to the MySQL LEAVE statement to exit a stored program or terminate a loop.

Introduction to MySQL LEAVE statement

The LEAVE statement exits the flow control that has a given label.

The following shows the basic syntax of the LEAVE statement:

LEAVE label;

In this syntax, you specify the label of the block that you want to exit after the LEAVE keyword.

Using the LEAVE statement to exit a stored procedure

If the label is the outermost of the stored procedure  or function block, LEAVE terminates the stored procedure or function.

The following statement shows how to use the LEAVE statement to exit a stored procedure:

CREATE PROCEDURE sp_name()
sp: BEGIN
    IF condition THEN
        LEAVE sp;
    END IF;
    -- other statement
END$$

For example, this statement creates a new stored procedure that checks the credit of a given customer in the customers table from the sample database:

DELIMITER $$

CREATE PROCEDURE CheckCredit(
    inCustomerNumber int
)
sp: BEGIN
    
    DECLARE customerCount INT;

    -- check if the customer exists
    SELECT 
        COUNT(*)
    INTO customerCount 
    FROM
        customers
    WHERE
        customerNumber = inCustomerNumber;
    
    -- if the customer does not exist, terminate
    -- the stored procedure
    IF customerCount = 0 THEN
        LEAVE sp;
    END IF;
    
    -- other logic
    -- ...
END$$

DELIMITER ;

Using LEAVE statement in loops

The LEAVE the statement allows you to terminate a loop. The general syntax for the LEAVE statement when used in the LOOP, REPEAT and WHILE statements.

Using LEAVE with the LOOP statement:

[label]: LOOP
    IF condition THEN
        LEAVE [label];
    END IF;
    -- statements
END LOOP [label];

Using LEAVE with the REPEAT statement:

[label:] REPEAT
    IF condition THEN
        LEAVE [label];
    END IF;
    -- statements
UNTIL search_condition
END REPEAT [label];

Using LEAVE with the WHILE statement:

[label:] WHILE search_condition DO
    IF condition THEN
        LEAVE [label];
    END IF;
    -- statements
END WHILE [label];

The LEAVE causes the current loop specified by the label to be terminated. If a loop is enclosed within another loop, you can break out of both loops with a single LEAVE statement.

Using LEAVE statement in a loop example

The following stored procedure generates a string of integers with the number from 1 to a random number between 4 and 10:

DELIMITER $$

CREATE PROCEDURE LeaveDemo(OUT result VARCHAR(100))
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE times INT;
    -- generate a random integer between 4 and 10
    SET times  = FLOOR(RAND()*(10-4+1)+4);
    SET result = '';
    disp: LOOP
        -- concatenate counters into the result
        SET result = concat(result,counter,',');
        
        -- exit the loop if counter equals times
        IF counter = times THEN
            LEAVE disp; 
        END IF;
        SET counter = counter + 1;
    END LOOP;
END$$

DELIMITER ;

This statement calls the LeaveDemo procedure:

CALL LeaveDemo(@result);
SELECT @result;

Here is one of the outputs:

+------------------+
| @result          |
+------------------+
| 1,2,3,4,5,6,7,8, |
+------------------+
1 row in set (0.00 sec)

In this tutorial, you have learned how to the MySQL LEAVE statement to exit a stored program or terminate a loop.