The syntax of stored programs mainly consists of three parts: the BEGIN ... END block, DECLARE statements, and statement labels.
BEGIN ... END blocks
The BEGIN ... END block supports compound statements and is used to write stored programs (stored procedures, stored functions, and triggers).
A BEGIN ... END compound statement can contain multiple statements, 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 (;). The statement_list is optional, so the empty compound statement BEGIN END is valid.
BEGIN ... END blocks can be nested. BEGIN ... END blocks can also be labeled. For more information, see Statement labels.
The default delimiter for PL stored programs is "/". You can also use the delimiter command to customize the delimiter so that the entire definition can be passed to the server as a single statement. The delimiter can consist of a single character or multiple characters. Avoid using the backslash (\) character, as it is typically used as an escape character. Example:
DELIMITER $$
DELIMITER //
Note
When developing with OceanBase AI Database, you generally need to customize the delimiter. If the PL block definition does not contain statements separated by ;, then a delimiter is not required.
DECLARE Statement
The DECLARE statement is used to define local variables, exception handlers, and cursors in a program. For more information, see Variables of stored programs, Overview of exception handling, and Cursor overview respectively.
The DECLARE statement can only be used within a BEGIN ... END block and must appear at the beginning of the block, before any other statements.
Declarations must follow this order:
Cursor declarations must appear before exception handler declarations.
Variable and condition declarations must appear before cursor or exception handler declarations.
Statement Tags
Labels can be used in 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 tags for these statements follow these rules:
A colon must follow
begin_label.The
begin_labelcan be specified without anend_label. If anend_labelis present, it must match thebegin_label.If
begin_labelis absent,end_labelcannot be specified.Tags at the same nesting level must be distinct.
A tag can contain up to 16 characters.
To reference a constructed label, use the ITERATE or LEAVE statement. The following examples show how to use 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 more information about declaring exception handlers within blocks, see DECLARE ... HANDLER.