BEGIN ... END

BEGIN ... END delimits the executable part of a scripting block.

A scripting block is the basic unit of a stored procedure. It can also be used as an anonymous block. It groups related declarations and statements.

An anonymous block is an executable block without a name and is not stored in the database. It is often used for one-time tasks or tests.

A scripting block consists of three main parts:

• Declaration part (optional): This is where variables and other necessary components are defined.
• Execution part (required): This part contains the executable statements and must contain at least one statement.
• Exception handling part (optional): This is where exceptions that may occur during execution are handled.

All declarations are local to the block and exist only during the execution of the block. Blocks can also be nested.

See also CREATE PROCEDURE.

See also DECLARE and EXCEPTION for other sections of a script block.

Syntax

[DECLARE]  
  [{ variable | resultset | exception };]
  [{ variable | resultset | exception }; ...]
BEGIN 
  ... statements; 
  [... statements];
[EXCEPTION  except_handler]
END;

DECLARE and EXCEPTION sections are optional in a block.

Arguments

statements: One or more statements of the following types:

• A single SQL statement
• A control flow statement (loop or branching, etc.)
• A nested block
• A call to a stored procedure using CALL

---

except_handler (optional): Specifies how exceptions should be handled.

Usage Notes

• After the keyword END, a semicolon or a label followed by a semicolon must follow.
• After the keyword BEGIN, no semicolon should follow.
• A BEGIN ... END block can be the "top-level" construct within an anonymous block or used within another construct (e.g., in a loop, branching, or stored procedure).
• BEGIN...END blocks can be nested.
• All database objects created within a block between BEGIN and END are available outside the block.
• Variables declared in the DECLARE section are not available outside the block.

Examples

Example 1 | Simple Anonymous Block

BEGIN
    CREATE TABLE mkw_doku.parent (ID INTEGER);
    CREATE TABLE mkw_doku.child (ID INTEGER, parent_ID INTEGER);
END;

Table 'mkw_doku.child' was created

Example 2 | Nested Anonymous Block

BEGIN
  CREATE TABLE mkw_doku.parent (ID INTEGER);
  BEGIN
    CREATE TABLE mkw_doku.child (ID INTEGER, parent_ID INTEGER);
  END;
END;

Table 'mkw_doku.child' was created