Help Center> GaussDB> Centralized_2.x> Stored Procedure> Arrays, Sets, and Records> Sets
Updated on 2023-10-23 GMT+08:00
View PDF

Sets

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

  • 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;