Variable Definition Statements

Updated on 2024-05-07 GMT+08:00

View PDF

This section describes the declaration of variables in the PL/SQL and the scope of this variable in codes.

Variable Declaration

For details about the variable declaration syntax, see Figure 1.

Figure 1 declare_variable::=
Click to enlarge

The above syntax diagram is explained as follows:

variable_name indicates the name of a variable.
type indicates the type of a variable.
value indicates the initial value of the variable. (If the initial value is not given, NULL is taken as the initial value.) value can also be an expression.

Examples

    
       gaussdb=# DECLARE
    emp_id  INTEGER := 7788; -- Define a variable and assign a value to it.
BEGIN
    emp_id := 5*7784; -- Assign a value to the variable.
END;
/

In addition to the declaration of basic variable types, %TYPE and %ROWTYPE can be used to declare variables related to table columns or table structures.

%TYPE Attribute

%TYPE declares a variable to be of the same data type as a previously declared variable (for example, a column in a table). For example, if you want to define the my_name variable whose data type is the same as the data type of the firstname column in the employee table, you can define the variable as follows:

my_name employee.firstname%TYPE

In this way, you can declare my_name without the need of knowing the data type of firstname in employee, and the data type of my_name can be automatically updated when the data type of firstname changes.

%ROWTYPE Attribute

%ROWTYPE declares data types of a set of data. It stores a row of table data or results fetched from a cursor. For example, if you want to define a set of data with the same column names and column data types as the employee table, you can define the data as follows:

my_employee employee%ROWTYPE

NOTE:

If multiple CNs are used, the %ROWTYPE and %TYPE attributes of temporary tables cannot be declared in a stored procedure, because a temporary table is valid only in the current session and is invisible to other CNs in the compilation phase. Therefore, if there are multiple CNs, the system displays a message indicating that the temporary table does not exist.

Scope of a Variable

The scope of a variable indicates the accessibility and availability of the variable in code block. In other words, a variable takes effect only within its scope.

To define a function scope, a variable must declare and create a BEGIN-END block in the declaration section. The necessity of such declaration is also determined by block structure, which requires that a variable has different scopes and lifetime during a process.
A variable can be defined multiple times in different scopes, and inner definition can cover outer one.
A variable defined in an outer block can also be used in a nested block. However, the outer block cannot access variables in the nested block.

Example

    
     
       
       gaussdb=# DECLARE
    emp_id  INTEGER :=7788; -- Define a variable and assign a value to it.
    outer_var  INTEGER :=6688; -- Define a variable and assign a value to it.
BEGIN
    DECLARE        
        emp_id INTEGER :=7799; -- Define a variable and assign a value to it.
        inner_var  INTEGER :=6688; -- Define a variable and assign a value to it.
    BEGIN
        dbe_output.print_line('inner emp_id ='||emp_id); -- Display the value 7799.
        dbe_output.print_line('outer_var ='||outer_var); -- Reference a variable of an outer block.
    END;
    dbe_output.print_line('outer emp_id ='||emp_id); -- Display the value 7788.
END;
/

      

    
   