Labels

Syntax

label: <construct>
[label]

Labels are MariaDB/MySQL identifiers which can be used to identify a BEGIN ... END construct. They have a maximum length of 12 characters and can be quoted with backticks.

Labels have a start part and a final part. The start part must precede the portion of code it refers to, it must be followed by a colon (;) and can be on the same line or on another line. The final part is optional and adds nothing, but can make the code more readable. If used, the final part must precede the last statement delimiter (;). Constructs identified by a label can be nested. Each construct can be identified by only 1 label.

LEAVE and ITERATE statements can be used to exit or repeat a portion of code identified by a label. LEAVE and ITERATE must be in the same Stored Routine, Trigger of Event which contains the target label.

Examples

A simple label which is used to exit a LOOP:

CREATE PROCEDURE `test_sp`()
BEGIN
	`my_label`:
	LOOP
		SELECT 'looping';
		LEAVE `my_label`;
	END LOOP;
	SELECT 'out of loop';
END;

The following label is used to exit a procedure, and has a final part:

CREATE PROCEDURE `test_sp`()
`my_label`:
BEGIN
	IF @var = 1 THEN
		LEAVE `my_label`;
	END IF;
	DO something();
END `my_label`;