You can access variables declared in blocks, such as subprograms or anonymous blocks, from the executable section or the exception section of other blocks depending on their relative location.
Accessing a variable means being able to reference it in a SQL statement or an SPL statement as you can with any local variable.
Note
If the subprogram signature contains formal parameters, you can access these in the same way as local variables of the subprogram. All discussion related to variables of a subprogram also applies to formal parameters of the subprogram.
Accessing variables includes not only those defined as a data type but also includes others such as record types, collection types, and cursors.
At most one qualifier can access the variable. The qualifier is the name of the subprogram or labeled anonymous block in which the variable was locally declared.
Syntax
The syntax to reference a variable is:
If specified, qualifier is the subprogram or labeled anonymous block in which variable was declared in its declaration section (that is, it's a local variable).
Note
In EDB Postgres Advanced Server, in only one circumstance are two qualifiers are permitted. This scenario is for accessing public variables of packages where you can specify the reference in the following format:
For more information about supported package syntax, see Built-in packages.
Requirements
You can access variables in the following ways:
Variables can be accessed as long as the block in which the variable was locally declared is in the ancestor hierarchical path starting from the block containing the reference to the variable. Such variables declared in ancestor blocks are referred to as global variables.
If a reference to an unqualified variable is made, the first attempt is to locate a local variable of that name. If such a local variable doesn't exist, then the search for the variable is made in the parent of the current block, and so forth, proceeding up the ancestor hierarchy. If such a variable isn't found, then an error occurs when the subprogram is invoked.
If a reference to a qualified variable is made, the same search process is performed but searching for the first match of the subprogram or labeled anonymous block that contains the local variable. The search proceeds up the ancestor hierarchy until a match is found. If such a match isn't found, then an error occurs when the subprogram is invoked.
You can't access the following location of variables relative to the block from where the reference to the variable is made:
Variables declared in a descendent block
Variables declared in a sibling block, a sibling block of an ancestor block, or any descendants within the sibling block
Note
The EDB Postgres Advanced Server process for accessing variables isn't compatible with Oracle databases. For Oracle, you can specify any number of qualifiers, and the search is based on the first match of the first qualifier in a similar manner to the Oracle matching algorithm for invoking subprograms.
Accessing variables with the same name
This example shows similar access attempts when all variables in all blocks have the same name:
The following is the output showing the content of each variable when the procedure is invoked:
Using labels to qualify access to variables
You can also use the labels on anonymous blocks to qualify access to variables. This example shows variable access in a set of nested anonymous blocks:
The following is the output showing the content of each variable when the anonymous block is invoked:
Examples
Example: Accessing record types in parent blocks
This example is an object type whose object type method, display_emp, contains the record type emp_typ and the subprocedure emp_sal_query. The record variable r_emp declared locally to emp_sal_query can access the record type emp_typ declared in the parent block display_emp.
The following is the output displayed when an instance of the object type is created and the procedure display_emp is invoked:
Example: Accessing an upper-level procedure
This example is a package with three levels of subprocedures. A record type, collection type, and cursor type declared in the upper-level procedure can be accessed by the descendent subprocedure.
The following is the output displayed when the top-level package procedure is invoked:
Example: Accessing variables in blocks
This example shows how variables in various blocks are accessed, with and without qualifiers. The lines that are commented out show attempts to access variables that result in an error.
The following is the output showing the content of each variable when the procedure is invoked: