PL syntax

The syntax of stored programs includes the BEGIN ... END block, DECLARE statements, 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.

A 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]

Here, statement_list represents a list of one or more statements, each ending with a semicolon (;). statement_list is optional, so an empty compound statement BEGIN END is valid.

The BEGIN ... END block supports nesting. Additionally, BEGIN ... END can be labeled, as described in Statement labels.

The default delimiter for PL stored programs is "/". Users can also use the delimiter command to customize the delimiter, allowing the entire definition to be passed to the server as a single statement. The delimiter can consist of one or more characters and should avoid using the backslash (\) character, which is typically used as an escape character. Here is an example:

DELIMITER $$

DELIMITER //

DECLARE statement

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

The DECLARE statement can only be used within a BEGIN ... END block and must appear 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 within a 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 usage rules for these statements are as follows:

• A colon must follow begin_label.

• begin_label can be provided without end_label. If end_label is provided, it must match begin_label.

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

• 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 demonstrates using labels to continue iteration or terminate a loop:

obclient> DELIMITER //

obclient> 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 information about declaring exception handlers within a block, see DECLARE ... HANDLER.