Assignment Statements

Syntax

Figure 1 shows the syntax diagram for assigning a value to a variable.

Figure 1 assignment_value::=

The above syntax diagram is explained as follows:

• variable_name indicates the name of a variable.
• value can be a value or an expression. The type of value must be compatible with the type of variable_name.

Examples

openGauss=# DECLARE
    emp_id  INTEGER := 7788; -- Assignment
BEGIN
    emp_id := 5; -- Assignment
    emp_id := 5*7784;
END;
/

Nested Value Assignment

Figure 2 shows the syntax diagram for assigning a nested value to a variable.

Figure 2 nested_assignment_value::=

The syntax in Figure 2 is described as follows:

• variable_name: variable name
• col_name: column name
• subscript: subscript, which is used for an array variable. The value can be a value or an expression and must be of the int type.
• value: value or expression. The type of value must be compatible with the type of variable_name.

Examples

openGauss=#CREATE TYPE o1 as (a int, b int);
openGauss=# DECLARE
    TYPE r1 is VARRAY(10) of o1;
    emp_id  r1;
BEGIN
    emp_id(1).a := 5;-- Assign a value.
    emp_id(1).b := 5*7784;
END;
/

NOTICE:

• In INTO mode, values can be assigned only to the columns at the first layer. Two-dimensional or above arrays are not supported.
• When a nested column value is referenced, if an array subscript exists, only one parenthesis can exist in the first three layers of columns. You are advised to use square brackets to reference the subscript.

Assignment Of Variables With Type Names

In addition to the above, openGauss supports assignment methods with type names (including RECORD, VARRAY, TABLE OF types and types created by CREATE TYPE). For compatibility with historical versions, such type names are usually ignored and treated as normal arrays or records. Only when enable_pltype_name_check switch is turned on will throw an error if the type name is different.

Examples

set enable_pltype_name_check = on; -- Turn on the type name detection switch (default is off)

DECLARE
  TYPE t_rec IS RECORD (val1 VARCHAR2(10), val2 VARCHAR2(10));
  TYPE t_rec2 IS RECORD (val1 VARCHAR2(10), val2 VARCHAR2(10));
  l_rec t_rec;
BEGIN
  l_rec := t_rec2('ONE', 'TWO'); -- Assignment of variables with type names
  raise info 'l_rec is %', NVL(l_rec.val1,'NULL');
END;
/

ERROR:  "t_rec2" cannot be used to assign "l_rec"

INTO/BULK COLLECT INTO

INTO and BULK COLLECT INTO store values returned by statements in a stored procedure to variables. BULK COLLECT INTO allows some or all returned values to be temporarily stored in an array.

Examples

openGauss=# DECLARE
    my_id integer;
BEGIN
    select id into my_id from customers limit 1; -- Assign a value.
END;
/

openGauss=# DECLARE
    type id_list is varray(6) of customers.id%type;
    id_arr id_list;
BEGIN
    select id bulk collect into id_arr from customers order by id DESC limit 20; -- Assign values in batches.
END;
/

NOTICE: BULK COLLECT INTO can only assign values to arrays in batches. Use LIMIT properly to prevent performance deterioration caused by excessive operations on data.