Scope and 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:
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 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 */