Assignment Statements
Syntax
Figure 1 shows the syntax diagram for assigning a value to a variable.
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.
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;
/
Feedback