Proposed SQLite Procedural Language

The basic unit of procedural code is a block. A block can contain a declarative part, an execution part and an exception handling part. Declarations and exception handling are optional, a block can be as simple as a BEGIN – END pair around one or more statements. Blocks can be nested, in which case a sub-block appears as a statement inside the enclosing block.

Note: Blocks should not be confused with transactions, and the SQL statements BEGIN and END (i.e. commit). A single transaction can span many blocks, and a block can span many transactions. Blocks can only appear after an EXEC statement, or as the body of a stored procedure.

DISCUSSION

Introduction

The basic structure of a block is as follows:

BEGIN
  statement;
  statement;
  ...
END

This form will typically only appear as a top level block, because statements like IF and WHILE allow for multiple statements in their bodies, thus eliminating the need to enclose them in BEGIN – END pair.

Local variables

The DECLARE keyword signals the start of the declarative part of a block, which contains the local declarations. Items declared locally exist only within the current block and all its sub-blocks and are not visible to enclosing blocks. The declarative part of a block is optional. It is terminated implicitly by the keyword BEGIN, which introduces the executable part of the block.

DECLARE
  i INTEGER;
  name TEXT;
  r1 REAL NOT NULL;
  d INTEGER DEFAULT 3;
BEGIN
  ...
END

Each variable definition is the name of the variable followed by the datatype for that variable, then an optional NOT NULL constraint, and finaly an optional default value.

• The datatype for the variable does not restrict what data may be put in that variable. This is the same as for the datatype of column definitions in tables.
• The default value specifies the initial value of the variable when the block first begins executing. If a default value is not provided, the initial value is null.The default value may be NULL, a string constant or a number.

Variable scope and extent

References to a variable are limited by its scope and its extent. The scope of a variable is the visibility of that variable. A variable is visible only in those regions of the block where it is not obscured by a redeclaration of the same identifier in an inner block. The extent of a variable is its lifetime, i.e. the time that its value will be preserved. A variable’s value remains in existence as long as the top most block is executing.

Variables declared in a block are considered local to that block and global to all its sub-blocks. If a global identifier is redeclared in a sub-block, both variables remain in extent. Within the sub-block, however, only the local variable is visible.

Although you cannot declare a variable two times in the same block, you can declare the same identifier in two different blocks. The two variables are distinct, and any change in one does not affect the other. However, a sub-block cannot reference variables declared in other sub-blocks at the same level because those variables are neither local nor global to that block.

The example below illustrates the scope rules. Notice that the identifiers declared in one sub-block cannot be referenced in the other sub-block. That is because a block cannot reference identifiers declared in other blocks nested at the same level.

DECLARE
  a TEXT;
  b INTEGER;
BEGIN
  variables available here: a, b
  DECLARE
    a INTEGER;
    c REAL;
  BEGIN
    variables available here: a (integer) , b, c
  END;
  DECLARE
    d TEXT;
  BEGIN
    variables available here: a, b, d
  END;
  variables available here: a, b
END

Exceptions

The EXCEPTION keyword signals the start of the exception-handling part of a block. When an exception is raised, normal execution of the block stops and control transfers to the appropriate exception handler. After the exception handler completes, execution proceeds with the statement following the block.

If there is no exception handler for the raised exception in the current block, control passes to the enclosing block. This process repeats until an exception handler is found or there are no more enclosing blocks. If no exception handler for the exception is found, execution stops and an error is returned to the caller.