Scope and structure

PL/vSQL uses block scope, where a block has the following structure:.

PL/vSQL uses block scope, where a block has the following structure:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
    ...
END [ label ];

Declarations

Variable declarations in the DECLARE block are structured as:

variable_name [ CONSTANT ] data_type [ NOT NULL ] [:= { expression | statement } ];
variable_name

Variable names must meet the following requirements:

CONSTANT Defines the variable as a constant (immutable). You can only set a constant variable's value during initialization.
data_type

The variable data type. PL/vSQL supports non-complex data types, with the following exceptions:

  • GEOMETRY

  • GEOGRAPHY

You can optionally reference a particular column's data type:

variable_name table_name.column_name%TYPE;

NOT NULL Specifies that the variable cannot hold a NULL value. If declared with NOT NULL, the variable must be initialized (otherwise, throws ERRCODE_SYNTAX_ERROR) and cannot be assigned NULL (otherwise, throws ERRCODE_WRONG_OBJECT_TYPE).
:= expression

Initializes a variable with expression or statement.

If the variable is declared with NOT NULL, expression is required.

Variable declarations in a given block execute sequentially, so old declarations can be referenced by newer ones. For example:

DECLARE
    x int := 3;
    y int := x;

Default (uninitialized): NULL

Aliases

Aliases are alternate names for the same variable. An alias of a variable is not a copy, and changes made to either reference affect the same underlying variable.

new_name ALIAS FOR variable;

Here, the identifier y is now an alias for variable x, and changes to y are reflected in x.

DO $$
DECLARE
    x int := 3;
    y ALIAS FOR x;
BEGIN
    y := 5; -- since y refers to x, x = 5
    RAISE INFO 'x = %, y = %', x, y;
END;
$$;

INFO 2005:  x = 5, y = 5

BEGIN and nested blocks

BEGIN contains statements. A statement is defined as a line or block of PL/vSQL.

Variables declared in inner blocks shadow those declared in outer blocks. To unambiguously specify a variable in a particular block, you can name the block with a label (case-insensitive), and then reference the variable declared in that block with:

label.variable_name

For example, specifying the variable x from inside the inner block implicitly refers to inner_block.x rather than outer_block.x because of shadowing:


<<outer_block>>
DECLARE
    x int;
BEGIN
    <<inner_block>>
    DECLARE
        x int;
    BEGIN
        x := 1000; -- implicitly specifies x in inner_block because of shadowing
        OUTER_BLOCK.x := 0; -- specifies x in outer_block; labels are case-insensitive
    END inner_block;
END outer_block;

NULL statement

The NULL statement does nothing. This can be useful as a placeholder statement or a way to show that a code block is intentionally empty. For example:

DO $$
BEGIN
    NULL;
END;
$$

Comments

Comments have the following syntax. You cannot nest comments.

-- single-line comment

/* multi-line
comment
*/