Syntax of PL stored programs

The syntax of a PL stored program contains three parts: the BEGIN ... END block, the DECLARE statement, and the statement label.

BEGIN ... END block

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

The BEGIN ... END block can contain multiple statements that are enclosed by using the BEGIN and END keywords. The syntax is as follows:

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

In the preceding syntax, statement_list specifies a list of one or more statements to be executed. Each statement in the list must end with a semicolon (;). statement_list is optional. Therefore, the empty compound statement BEGIN END is valid.

BEGIN ... END blocks can be nested. A BEGIN ... END block can also be labeled. For more information, see Statement labels.

The default delimiter of PL stored programs is "/". You can also use the delimiter command to define a delimiter so that the entire definition is transferred to the server as a single statement. A delimiter can consist of a single character or multiple characters. Do not use the backslash (\) as a delimiter because it is usually considered as an escape character. Here is an example:

DELIMITER $$

DELIMITER //

DECLARE statement

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

DECLARE can be used only in a BEGIN ... END block and must be used at the beginning of the block or prior to other statements.

Sequence of declarations:

• Cursor declarations must be prior to exception handler declarations.

• Variable and condition declarations must be prior to the cursor or exception handler declarations.

Statement labels

Labels can be used in the LOOP, REPEAT, and WHILE statements of 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]

Observe the following rules for using the labels of these statements:

• begin_label must be followed with a colon (:).

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

• end_label cannot be provided without begin_label.

• Labels of the same nesting level must be unique.

• A label can contain a maximum of 16 characters in length.

To reference a built label, use the ITERATE or LEAVE statement. The following example uses a labeled statement to continue iteration or end the 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 more information about the declaration of in-block exception handlers, see DECLARE ... HANDLER.