Help Center/ GaussDB/ Centralized_3.x/ Stored Procedure/ Arrays, Collections, and Records/ Collections/ Use of Collection Types
Updated on 2024-05-07 GMT+08:00
View PDF

Use of Collection Types

Before the use of collections, a collection type must be defined.

Define a collection type immediately after the AS keyword in a stored procedure. The definition method is as follows:

In the preceding information:

  • table_type: indicates the name of the collection type to be defined.
  • TABLE: indicates the collection type to be defined.
  • data_type: indicates the type of members in the collection to be created.
  • indexby_type: indicates the type of the index set to be created.

Nest-Table Collection Type

Members of a specified data type are stored in a variable-length array. You can use the extend function to expand the storage space and use the trim function to release the storage space. For a collection variable x with 10 storage units whose member type is int, members are stored as shown in the following figure.

Members x(2), x(5), and x(8) are invalid, but their storage space is still reserved. You can assign values to them later without re-allocating space.

After a collection type is defined, use table_type as the type name to declare the variable.

var_name table_type [:= table_type([v1[,...]])];

You can use a type constructor to initialize the variable during or after declaration. If the variable is not initialized, the value of var_name is NULL.

After the variable is declared and initialized, you can access collection members through an index set or assign values to members. The index range is [1,upper]. The value of upper is the size of the current space. If you attempt to access a deleted member, the error message "no data found" will be returned.

  • In a non-A compatible mode (the value of sql_compatibility is not A), the collection type cannot be created.
  • In GaussDB, a nest-table collection does not automatically increase, and an error is reported when you attempt to access the index set out of the specified range.
  • Collections can be defined in schemas, anonymous blocks, stored procedures, user-defined functions, and packages, among which the scopes of the collection types vary.
  • NOT NULL has no function but only takes effect in the syntax.
  • When data_type is set to a type that can define the length and precision, such as varchar and numeric, you need to enable the tableof_elem_constraints parameter to verify the length of elements in the collection or convert elements to the corresponding precision.
  • If data_type is of the array type, the element length verification or precision conversion of the array type is also affected by whether the tableof_elem_constraints parameter is enabled.
  • The value of the collection type converted from the array type does not support element length verification or precision conversion.
  • The value of data_type can be a base data type or a record type, collection type, or array type defined in a stored procedure. The ref cursor type is not supported.
  • Variables of a collection type cannot be assigned a value of another collection type, even if their member types are the same. For example, t1 and t2 are of different collection types defined by TYPE t1 IS TABLE OF int and TYPE t2 IS TABLE OF int, respectively. A value of the collection type defined by TYPE t1 IS TABLE OF int cannot be assigned to t2, and vice versa. (This restriction may not take effect when the variables are of the member types because the value assignment logic of variables is affected by the parent type.)
  • Only the equal (=) and non-equal operations (<> or !=) between collections are supported. Other relational and arithmetic operations are not supported.
  • Use IS [ NOT ] NULL to compare a collection type with NULL. The result of comparison with NULL using the equal operator (=) is inaccurate.
  • Variables of the collection type can be used as parameters and return values of functions. In this case, the type of the parameters or return values must have been defined in a schema or package.
  • When a nest-table collection is used as the input parameter of a function, an array with elements of the same type can be transferred as the input parameter. Multi-dimensional arrays are not supported, and the array index set must start from 1. (This function is outdated and not recommended.) You can run the set behavior_compat_options = 'disable_rewrite_nesttable' command to disable the function.
  • Operations on XML data are not supported.
  • When creating a table, do not use the collection type or any type containing a collection as a column in the table.
  • Constructors of the collection type do not support floating point numbers and expressions as indexes.
  • For a collection type defined in an anonymous block, after ROLLBACK is executed or EXCEPTION occurs in an anonymous block, the collection type cannot be used.
  • After enable_recordtype_check_strict is enabled, if the member is of the record type and a column of the record type has the not null or default attribute, an error is reported during stored procedure or package compilation.

GaussDB supports access to collection elements by using parentheses, and it also supports the extend, count, first, last, prior, next, and delete functions.

The collection functions support multiset union, intersect, except all, and distinct.

Index-By Table Collection Type

This collection type stores the index set and corresponding member values in a hash table as key-value pairs. All operations on variables of this type are actually operations on the hash table. Users do not need to expand or release storage space, but only need to assign values or delete members. The operations related to the collection type are described as follows:

  1. Type definition

    When defining the index-by table collection type, specify both the member type data_type and index set type indexby_type. The index set type can only be integer or varchar.

  2. Variable declaration and initialization

    After the index-by table collection type is declared, it can be initialized. There are three initialization scenarios: uninitialized, initialized to null, and initialized to specified index set and member values. The effect of uninitialized variables is the same as that of initializing variables to null. If a variable is not initialized or is initialized to null, the variable is not NULL. You can assign a value to the variable later. Initializing a variable to specified index set and member values will save the specified index set and member values to the variable as key-value pairs.

  3. Variable assignment

    Assignment to variables of the index-by table collection type is classified into member assignment and group assignment. Member assignment allows to assign a value to a member by specifying the index set. If the member does not have a value, the assigned value is used directly. If the member has a value, the member value is updated. Group assignment will clear the original members in the variable and save the new member values. In the group assignment scenario, NULL cannot be assigned to variables. Otherwise, an error is reported.

  4. Variable value

    You can specify an index set to obtain the member value of the corresponding index in the variable. If the member cannot be found based on the index set, the error message "no data found" is returned.

  • In a non-A compatible mode (the value of sql_compatibility is not A), no index-by table collection type can be created.
  • Index-by table collection types can be defined in anonymous blocks, stored procedures, user-defined functions, and packages, among which the scopes of the collection types vary. Index-by table collection types cannot be defined in schemas.
  • NOT NULL has no function but only takes effect in the syntax.
  • When data_type is set to a type that can define the length and precision, such as varchar and numeric, you need to enable the tableof_elem_constraints parameter to verify the length of elements in the collection or convert elements to the corresponding precision.
  • If data_type is of the array type, the element length verification or precision conversion of the array type is also affected by whether the tableof_elem_constraints parameter is enabled.
  • The value of the collection type converted from the array type does not support element length verification or precision conversion.
  • The value of data_type can be a base data type or a record type, collection type, or array type defined in a stored procedure. The ref cursor type is not supported.
  • The value of indexby_type can only be integer or varchar.
  • When indexby_type is set to varchar and tableof_elem_constraints is enabled, the length of the index value is verified when a value is assigned to an index-by table collection type. The verification behavior is not affected by whether char_coerce_compat is enabled. If the index length is greater than the defined length, an error is reported. If the tableof_elem_constraints parameter is disabled, the index value length is not verified.
  • An uninitialized variable of the index-by table collection type is not NULL.
  • NULL cannot be assigned to a variable of the index-by table collection type. Otherwise, an error is reported.
  • NULL and '' (single quotation marks) cannot be assigned to a variable of the index-by table collection type.
  • Variables of the index-by table collection type cannot be assigned values of another index-by table collection type, even if their member type and index set type are the same. For example, t1 and t2 are of different collection types defined by TYPE t1 IS TABLE OF int index by int and TYPE t2 IS TABLE OF int index by int, respectively. A value of the collection type defined by TYPE t1 IS TABLE OF int index by int cannot be assigned to t2, and vice versa. (This restriction may not take effect when the variables are of the member types because the value assignment logic of variables is affected by the parent type.)
  • An index-by table collection type does not support relational and arithmetic operations.
  • When a variable of the index-by table collection type is assigned by executing select... bulk collect into, the index set must be of the integer type. The index set type cannot be varchar.
  • Variables of the index-by table collection type can be used as the parameters and return values of functions. In this case, the type of the parameters or return values must be a collection type defined in the package.
  • When an index-by table collection is used as the input parameter of a function, an array with elements of the same type can be transferred as the input parameter. Multi-dimensional arrays are not supported, and the index type cannot be varchar. (This function is outdated and not recommended.) You can run the set behavior_compat_options = 'disable_rewrite_nesttable' command to disable the function.
  • Currently, the type constructor supports only the collection type and the maximum number of parameters is the same as that of user-defined function parameters. For the index-by table collection type, the index value can only be a constant when the constructor is used.
  • Operations on XML data are not supported.
  • When creating a table, do not use the collection type or any type containing a collection as a column in the table.
  • Constructors of the collection type do not support floating point numbers and expressions as indexes.
  • For a collection type defined in an anonymous block, after ROLLBACK is executed or EXCEPTION occurs in an anonymous block, the collection type cannot be used.

Examples

Example 1: nest-table collection type

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
-- Perform operations on a collection in the stored procedure.
gaussdb=# CREATE OR REPLACE PROCEDURE table_proc AS
DECLARE
       TYPE TABLE_INTEGER IS TABLE OF INTEGER;-- Define a collection type.
       TABLEINT TABLE_INTEGER := TABLE_INTEGER();  -- Declare the variable of the collection type.
BEGIN 
       TABLEINT.extend(10);  
       FOR I IN 1..10 LOOP  
           TABLEINT(I) := I; 
       END LOOP; 
       DBE_OUTPUT.PRINT_LINE(TABLEINT.COUNT);  
       DBE_OUTPUT.PRINT_LINE(TABLEINT(1));  
       DBE_OUTPUT.PRINT_LINE(TABLEINT(10)); 
END;  
/
CREATE PROCEDURE

-- Call the stored procedure.
gaussdb=# CALL table_proc();

10
1
10
 table_proc 
------------

(1 row)

-- Drop the stored procedure.
gaussdb=# DROP PROCEDURE table_proc;
DROP PROCEDURE

-- Perform operations on a nest-table collection in the stored procedure.
gaussdb=# CREATE OR REPLACE PROCEDURE nest_table_proc AS
DECLARE
       TYPE TABLE_INTEGER IS TABLE OF INTEGER;-- Define a collection type.
       TYPE NEST_TABLE_INTEGER IS TABLE OF TABLE_INTEGER;-- Define a collection type.
       NEST_TABLE_VAR NEST_TABLE_INTEGER := NEST_TABLE_INTEGER(); --Declare a variable of the nest-table collection type.
BEGIN 
       NEST_TABLE_VAR.extend(10);
       FOR I IN 1..10 LOOP  
           NEST_TABLE_VAR(I) := TABLE_INTEGER();
           NEST_TABLE_VAR(I).extend(10);
           NEST_TABLE_VAR(I)(I) := I; 
       END LOOP; 
       DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR.COUNT);  
       DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR(1)(1));  
       DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR(10)(10)); 
END;  
/
CREATE PROCEDURE

-- Call the stored procedure.
gaussdb=# CALL nest_table_proc();
10
1
10
 nest_table_proc 
-----------------

(1 row)
-- Drop the stored procedure.
gaussdb=# DROP PROCEDURE nest_table_proc;
DROP PROCEDURE

Example 2: index-by table collection type

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
 
-- Perform operations on an index-by table collection in the stored procedure.
gaussdb=# CREATE OR REPLACE PROCEDURE index_table_proc AS
DECLARE
       TYPE TABLE_INTEGER IS TABLE OF INTEGER INDEX BY INTEGER; -- Define a collection type.
       TYPE TABLE_VARCHAR IS TABLE OF INTEGER INDEX BY VARCHAR; -- Define a collection type.
       TABLEINT_01 TABLE_INTEGER;                               -- Declare a variable of the collection type, which is not initialized.
       TABLEINT_02 TABLE_INTEGER := TABLE_INTEGER();            -- Declare a variable of the collection type. The initial value is null.
       TABLEINT_03 TABLE_INTEGER := TABLE_INTEGER(2=>3,3=>4);   -- Declare a variable of the collection type and initialize it to the specified value.
       RES INTEGER;
BEGIN     
       FOR I IN 1..10 LOOP  
           TABLEINT_01(I) := I;     -- Assign values to members.
           TABLEINT_02(I) := I + 1; -- Assign values to members.
       END LOOP; 
       TABLEINT_01 := TABLEINT_02;  -- Group assignment
       RES := TABLEINT_03(2);       -- Return the collection values.
       DBE_OUTPUT.PRINT_LINE(RES);  
       DBE_OUTPUT.PRINT_LINE(TABLEINT_01(1));  
       DBE_OUTPUT.PRINT_LINE(TABLEINT_01(10)); 
END;  
/
CREATE PROCEDURE
-- Call the stored procedure.
gaussdb=# CALL index_table_proc();
3
2
11
 index_table_proc 
------------------

(1 row)
-- Drop the stored procedure.
gaussdb=# DROP PROCEDURE index_table_proc;
DROP PROCEDURE

-- Perform operations on a nest-table collection in the stored procedure.
gaussdb=# CREATE OR REPLACE PROCEDURE nest_table_proc AS
DECLARE
       TYPE TABLE_INTEGER IS TABLE OF INTEGER INDEX BY INTEGER; -- Define a collection type.
       TYPE NEST_TABLE_INTEGER IS TABLE OF TABLE_INTEGER INDEX BY INTEGER;-- Define a collection type.
       NEST_TABLE_VAR NEST_TABLE_INTEGER;                                 -- Declare variables of the nest-table collection type.
BEGIN 
       FOR I IN 1..10 LOOP  
               NEST_TABLE_VAR(I)(I) := I; 
       END LOOP; 
       DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR.COUNT);  
       DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR(1)(1));  
       DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR(10)(10)); 
END;  
/

CREATE PROCEDURE

-- Call the stored procedure.
gaussdb=# CALL nest_table_proc();

10
1
10
 nest_table_proc 
-----------------

(1 row)

-- Drop the stored procedure.
gaussdb=# DROP PROCEDURE nest_table_proc;
DROP PROCEDURE
Parent topic: Collections