MySQL Leave

The MySQL LEAVE statement is used within the context of a stored program (such as a stored procedure, function, or trigger) to exit the program prematurely. It provides a way to terminate the execution of a program block and return control to the calling program.

The LEAVE statement is particularly useful in conditional flow control scenarios where you want to exit the program based on certain conditions. By using the LEAVE statement, you can break out of a loop or exit a procedure without completing the remaining code.

Syntax

Here is the basic syntax of the LEAVE statement in MySQL:

LEAVE label;

The label is an identifier that represents a specific point in the program where you want to exit. The label is defined using a colon (:) followed by the label name. For example:

mylabel: LEAVE mylabel;

When the LEAVE statement is executed, the program flow jumps to the statement immediately following the labeled point. This allows you to skip the execution of any remaining code within the program block.

Example

Here’s an example to illustrate the usage of the LEAVE statement in a stored procedure:

CREATE PROCEDURE calculate_average()
BEGIN
  DECLARE total INT;
  DECLARE counter INT;
  DECLARE average FLOAT;
  
  SET total = 0;
  SET counter = 0;
  
  myloop: LOOP
    SET total = total + counter;
    SET counter = counter + 1;
    
    IF counter > 10 THEN
      LEAVE myloop;
    END IF;
  END LOOP myloop;
  
  SET average = total / counter;
  
  SELECT average;
END;

In this example, the stored procedure calculate_average calculates the average of numbers from 0 to 10 using a loop. The LEAVE statement is used to exit the loop when the counter exceeds 10. The average is then calculated and returned as the result of the procedure.

By strategically placing the LEAVE statement within your program, you can control the flow of execution and handle various conditions or situations more efficiently.

It’s important to note that the LEAVE statement can only be used within the context of a stored program. It cannot be used in regular SQL statements or outside of a program block.

Overall, the MySQL LEAVE statement provides a way to exit a stored program prematurely, allowing you to customize the flow of execution and handle specific conditions within your programs more effectively.