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;
Code language: SQL (Structured Query Language) (sql)
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$$
Code language: SQL (Structured Query Language) (sql)
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 $$inCustomerNumber int
)
sp: BEGIN
SELECT
COUNT(*)
INTO customerCount
FROM
customers
WHERE
customerNumber = inCustomerNumber;
— the stored procedure
IF customerCount = 0 THEN
LEAVE sp;
END IF;
— …
END$$
Code language: SQL (Structured Query Language) (sql)
Using LEAVE statement in loops
The LEAVE statement allows you to terminate a loop. The general syntax for the LEAVE statement when using 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];
Code language: SQL (Structured Query Language) (sql)
Using LEAVE with the REPEAT statement:
[label:] REPEAT
IF condition THEN
LEAVE [label];
END IF;
-- statements
UNTIL search_condition
END REPEAT [label];
Code language: SQL (Structured Query Language) (sql)
Using LEAVE with the WHILE statement:
[label:] WHILE search_condition DO
IF condition THEN
LEAVE [label];
END IF;
-- statements
END WHILE [label];
Code language: SQL (Structured Query Language) (sql)
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 integer with the number from 1 to a random number between 4 and 10:
DELIMITER $$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,‘,’);
IF counter = times THEN
LEAVE disp;
END IF;
SET counter = counter + 1;
END LOOP;
END$$
Code language: SQL (Structured Query Language) (sql)
This statement calls the LeaveDemo procedure:
CALL LeaveDemo(@result);
SELECT @result;
Code language: SQL (Structured Query Language) (sql)
Here is one of the outputs:
+------------------+
| @result |
+------------------+
| 1,2,3,4,5,6,7,8, |
+------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to the MySQL LEAVE statement to exit a stored program or terminate a loop.