Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Use of Collection Types

Updated on 2024-05-07 GMT+08:00

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.

NOTE:
  • 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.

NOTE:
  • 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

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback