PL syntax

The syntax of a stored program includes the BEGIN ... END block, the DECLARE statement, and statement labels.

BEGIN ... END block

The BEGIN ... END block supports compound statements and is used to write stored programs, such as stored procedures, stored functions, and triggers.

The BEGIN ... END compound statement can contain multiple statements, which are enclosed by the BEGIN and END keywords. The syntax is as follows:

[begin_label:] BEGIN
    [statement_list]
END [end_label]

In this syntax, statement_list is a list of one or more statements, each of which ends with a semicolon (;). statement_list is optional, so an empty compound statement BEGIN END is valid.

The BEGIN ... END block supports nesting. The BEGIN ... END block can also be labeled. For more information, see Statement labels.

The default delimiter for PL stored programs is "/". You can use the delimiter command to define a custom delimiter so that the entire definition can be passed to the server as a single statement. The delimiter can be one or more characters. Avoid using the backslash () character, which is typically used as an escape character. Here is an example:

DELIMITER $$

DELIMITER //

info

When you develop stored programs in seekdb, you usually need to define a custom delimiter. If the PL block does not contain statements separated by ;, you do not need to use a delimiter.

DECLARE statement

The DECLARE statement is used to define local variables, exception handling, and cursors in a stored program. For more information, see Variables in stored programs, Exception handling, and Cursors.

The DECLARE statement can be used only in the BEGIN ... END block and must be placed at the beginning, before any other statements.

The declaration order must follow these rules:

• Cursor declarations must appear before exception handler declarations.

• Variable and condition declarations must appear before cursor or exception handler declarations.

Statement labels

Labels can be used in the LOOP, REPEAT, and WHILE statements of the BEGIN ... END block. The syntax is as follows:

[begin_label:] BEGIN
    [statement_list]
END [end_label]

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

The following rules apply to labels of these statements:

• A colon (:) must follow begin_label.

• begin_label can be specified without end_label. If end_label is specified, it must be the same as begin_label.

• If begin_label is not specified, end_label cannot be specified.

• Labels at the same nesting level must be different.

• The maximum length of a label is 16 characters.

To reference a constructed label, use the ITERATE or LEAVE statement. The following example shows how to use labels to continue iteration or terminate a loop:

DELIMITER //

CREATE PROCEDURE doiterate()
    BEGIN
      DECLARE p1 INT DEFAULT 0; 
      label1: LOOP
        SET p1 = p1 + 1;
        IF p1 < 10 THEN ITERATE label1; END IF;
        LEAVE label1;
      END LOOP label1;
    END //
Query OK, 0 rows affected

For more information about declaring exception handlers in a block, see DECLARE ... HANDLER.