Variable Definition Statements

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.

Examples

1 2 3 4 5 6 7

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; / ANONYMOUS BLOCK EXECUTE

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 firstname in employee, you can define the variable as follows:

my_name employee.firstname%TYPE
-- Example
DROP TABLE IF EXISTS employee;
NOTICE:  table "employee" does not exist, skipping
DROP TABLE
CREATE TABLE employee(firstname varchar,secondname varchar);
CREATE TABLE
DECLARE
    my_name employee.firstname%TYPE;
BEGIN
    my_name = 'abc';
    DBE_OUTPUT.PRINT_LINE(my_name);
END;
/
abc
ANONYMOUS BLOCK EXECUTE

In this way, you do not need to know the data type of firstname in employee. In addition, you do not need to change the data type of my_name again when the data type of firstname changes.

TYPE employee_record is record (id INTEGER, firstname VARCHAR2(20));
my_employee employee_record;
my_id my_employee.id%TYPE;
my_id_copy my_id%TYPE;
-- Example
DECLARE
    TYPE employee_record is record (id INTEGER, firstname VARCHAR2(20));
    my_employee employee_record;
    my_id my_employee.id%TYPE;
    my_id_copy my_id%TYPE;
BEGIN
    my_employee.id := 1;
    my_employee.firstname := 'ab2';
    my_id = 2;
    my_id_copy = 3;
    DBE_OUTPUT.PRINT_LINE(my_employee.id);
    DBE_OUTPUT.PRINT_LINE(my_employee.firstname);
    DBE_OUTPUT.PRINT_LINE(my_id);
    DBE_OUTPUT.PRINT_LINE(my_id_copy);
END;
/
1
ab2
2
3
ANONYMOUS BLOCK EXECUTE

%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
-- Example
DROP TABLE IF EXISTS employee;
DROP TABLE
CREATE TABLE employee(firstname varchar,secondname varchar);
DECLARE
    my_employee employee%ROWTYPE;
BEGIN
    my_employee.firstname := 'ab1';
    my_employee.secondname := 'ab2';
    DBE_OUTPUT.PRINT_LINE(my_employee.firstname);
    DBE_OUTPUT.PRINT_LINE(my_employee.secondname);
END;
/
ab1
ab2
ANONYMOUS BLOCK EXECUTE

The attribute can also be used on the cursor. The column names and column data types of this set of data are the same as those of the employee table. For the cursor in a package, %ROWTYPE can be omitted. %TYPE can also reference the type of a column in the cursor. You can define the data as follows:

cursor cur is select * from employee;
my_employee cur%ROWTYPE
my_name cur.firstname%TYPE
my_employee2 cur -- For the cursor defined in a package, %ROWTYPE can be omitted.
-- Example
set behavior_compat_options = 'allow_procedure_compile_check';
SET
DROP TABLE IF EXISTS employee;
DROP TABLE
CREATE TABLE employee(firstname varchar,secondname varchar);
CREATE TABLE
CREATE OR REPLACE procedure proc1()
IS
    cursor cur is select * from employee;
    my_employee cur%ROWTYPE;
    my_name cur.firstname%TYPE;
BEGIN
    my_employee.firstname := 'ab1';
    my_employee.secondname := 'ab2';
    my_name := 'ab3';
    DBE_OUTPUT.PRINT_LINE(my_employee.firstname);
    DBE_OUTPUT.PRINT_LINE(my_employee.secondname);
    DBE_OUTPUT.PRINT_LINE(my_name);
END;
/
CREATE PROCEDURE

• %TYPE can reference only the type of record variables across packages and cannot reference a column type of the cursor variable.
• If the GUC parameter behavior_compat_options is enabled and the value of this parameter contains allow_procedure_compile_check, %TYPE can reference a column type of the cursor variable.
• %ROWTYPE cannot reference the type of a composite variable or a record variable.
• The non-PACKAGE record%TYPE cannot be used as the variable type or input/output parameter type.
• View%ROWTYPE or SCHEMA.view%ROWTYPE cannot be used as the input/output parameter type.
• The PACKAGE.record%TYPE or SCHEMA.PACKAGE.record%TYPE cannot be used as the input/output parameter type.
• Table/View.column.column%TYPE or SCHEMA.Table/View.column.column%TYPE cannot be nested with one or more layers as variable types or input/output parameter type.
• Record.column.column%TYPE and PACKAGE.record.column.column%TYPE cannot be nested with a column type of records at one or more layers as the variable type or input/output parameter type.
• The cursor variable of more levels, such as PACKAGE.cursor%ROWTYPE and SCHEMA.PACKAGE.cursor%ROWTYPE, cannot be used as the parameter type or input/output parameter type.

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;
/
inner emp_id =7799
outer_var =6688
outer emp_id =7788
ANONYMOUS BLOCK EXECUTE