Assignment Statements

Variable Syntax

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

Figure 1 assignment_value::=

The syntax is described as follows:

• variable_name specifies 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.

Variable Value Assignment Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

DECLARE
    emp_id  INTEGER := 7788; -- Assign a value.
BEGIN
    emp_id := 5; -- Assign a value.
    DBE_OUTPUT.PRINT_LINE(emp_id);
    emp_id := 5*7784;
    DBE_OUTPUT.PRINT_LINE(emp_id);
END;
/
-- The result is as follows:
5
38920
ANONYMOUS BLOCK EXECUTE

INTO/BULK COLLECT INTO

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

Syntax

SELECT select_expressions INTO [STRICT] target FROM ...
SELECT INTO [STRICT] target [FROM ..]
EXECUTE [IMMEDIATE] select_expressions BULK COLLECT INTO target ...

The syntax is described as follows:

• select_expressions: SQL statement for query. You can use basic SQL commands and INTO clauses to assign the result of a single row or multiple columns to a variable (such as record, row, or scalar variable list).
• target: target variable, which can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields.
• STRICT (Optional): If the GUC parameter behavior_compat_options is set to 'select_into_return_null' (disabled by default) and the STRICT option is specified, the query must return a non-empty result set. Otherwise, an error is reported: "NO_DATA_FOUND", "TOO_MANY_ROWS" or "QUERY_RETURNED_NO_ROWS". If the option is not specified, the empty result set can be returned.

• BULK COLLECT INTO can assign values to arrays or collection in batches. Use the LIMIT column properly to prevent performance deterioration caused by excessive data operations.
• INTO and BULK COLLECT INTO support only direct nesting of record type value with less than four layers.
• To return an empty result set, you need to enable PG-compatible mode during database initialization. Set the GUC parameter behavior_compat_options to 'select_into_return_null', the compatibility mode is enabled. If the GUC parameter behavior_compat_options is not set, the compatibility mode is disabled.
• For array variables, elements in parentheses () are preferentially identified as index sets. Therefore, expressions with parentheses cannot be written after array variables. For example, SELECT (1+3) INTO va(5) cannot be written as SELECT INTO va(5) (1+3) or SELECT INTO va[5] (1+3).
• BULK COLLECT INTO can be used only in the ORA-compatible database.
• The IMMEDIATE keyword is used only for syntax compatibility and has no actual meaning.

Examples

gaussdb=# DROP TABLE IF EXISTS customers;
gaussdb=# CREATE TABLE customers(id int,name varchar);
gaussdb=# INSERT INTO customers VALUES(1,'ab');
gaussdb=# DECLARE
    my_id integer;
BEGIN
    select id into my_id from customers limit 1; -- Assign a value.
END;
/
ANONYMOUS BLOCK EXECUTE
gaussdb=# 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;
/
ANONYMOUS BLOCK EXECUTE
gaussdb=#  DECLARE
    TYPE id_list IS varray(6) OF customers.id%type;
    id_arr id_list;
    sql_qry varchar2(150);
BEGIN
    sql_qry := 'SELECT id FROM customers ORDER BY id DESC LIMIT 20';
    EXECUTE IMMEDIATE sql_qry BULK COLLECT INTO id_arr; -- Assign values in batches.
END;
/
ANONYMOUS BLOCK EXECUTE