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
On this page

Sets

Updated on 2023-10-23 GMT+08:00

Use of Set Types

Before the use of sets, a set type needs to be defined.

Define a set 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 set type to be defined.
  • TABLE: indicates the set type to be defined.
  • data_type: indicates the types of members in the set to be created.
  • indexby_type: indicates the type of the set index to be created.
NOTE:
  • In GaussDB, a set automatically increases. If an access violation occurs, a null value is returned, and no error message is reported.
  • The scope of a set type defined in a stored procedure takes effect only in this stored procedure.
  • The index can only be of the integer or varchar type. The length of the varchar type is not restricted.
  • NOT NULL has no function but only takes effect in the syntax.
  • data_type can also be the record type or set type defined in a stored procedure (anonymous blocks are not supported), but cannot be the array type.
  • Variables of the nested set type cannot be used across packages.
  • Variables of the TABLE OF index by type cannot be nested in a record as the input and output parameters of a stored procedure.
  • Variables of the TABLE OF index by type cannot be used as input and output parameters of functions.
  • The RAISE INFO command cannot be used to print the entire nested TABLE OF variable.
  • The TABLE OF variable cannot be transferred across autonomous transactions.
  • The input and output parameters of a stored procedure cannot be defined as the nested TABLE OF type.

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

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

NOTE:
  • An expression can contain only one variable of the TABLE OF index by type.
  • Exercise caution when using the DELETE statement to delete a single element. Otherwise, the element sequence may be incorrect.

Examples

 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
-- Perform operations on a set in the stored procedure.
openGauss=# CREATE OR REPLACE PROCEDURE table_proc AS
DECLARE
       TYPE TABLE_INTEGER IS TABLE OF INTEGER;-- Define the set type.
       TABLEINT TABLE_INTEGER := TABLE_INTEGER();  -- Declare the variable of the set 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;  
/

-- Invoke the stored procedure.
openGauss=# CALL table_proc();

-- Delete the stored procedure.
openGauss=# DROP PROCEDURE table_proc;

-- Perform operations on a nested table in the stored procedure.
openGauss=# CREATE OR REPLACE PROCEDURE nest_table_proc AS
DECLARE
       TYPE TABLE_INTEGER IS TABLE OF INTEGER;-- Define the set type.
       TYPE NEST_TABLE_INTEGER IS TABLE OF TABLE_INTEGER;-- Define the set type.
       NEST_TABLE_VAR NEST_TABLE_INTEGER; -- Declare a variable of the nested table 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;  
/

-- Invoke the stored procedure.
openGauss=# CALL nest_table_proc();

-- Delete the stored procedure.
openGauss=# DROP PROCEDURE nest_table_proc;

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