• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Stored Procedures
  5. Basic Statements
  6. Variable Definition Statement

Variable Definition Statement

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::=

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.

Example:

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;
/

Scope of a Variable

The scope of a variable indicates the accessibility and availability of a 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:

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
        dbms_output.put_line('inner emp_id ='||emp_id); -- Display the value as 7799.
        dbms_output.put_line('outer_var ='||outer_var); -- Cite variables of an outer block.
    END;
    dbms_output.put_line('outer emp_id ='||emp_id); -- Display the value as 7788.
END;
/