Labels

You are viewing an old version of this article. View the current version here.

Syntax

label: <construct>
[label]

Labels are MariaDB/MySQL identifiers which can be used to identify a BEGIN ... END construct or a loop. They have a maximum length of 16 characters and can be quoted with backticks (`).

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

Labels must be unique in the stored program they belong to, unless they are nested in different BEGIN ... END blocks, because that case no ambiguity is possible. Generally, if you try to define two labels with the same name, the following error will be produced:

ERROR 1309 (42000): Redefining label <label_name>

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 or 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 an end part:

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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.