Help Center/ GaussDB/ Developer Guide(Centralized_2.x)/ Stored Procedure/ Basic Statements/ Assignment Statements
Updated on 2023-10-23 GMT+08:00
View PDF
Share

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.

Example:

1
2
3
4
5
6
7
 
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.

Example:

1
2
3
4
5
6
7
8
9
 
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;
/
  • 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.

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.

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

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

Parent topic: Basic Statements