集合类型的使用

在使用集合之前，需要自定义一个集合类型。

在存储过程中紧跟AS关键字后面定义集合类型。定义方法如下。

集合类型的使用 - 图1

其中：

  • table_type：要定义的集合类型名。
  • TABLE：表示要定义集合类型。
  • data_type：要创建的集合中成员的类型。
  • indexby_type： 创建集合索引的类型。

无索引的集合类型

以变长数组的方式存储指定数据类型的成员，用户可以通过extend函数扩展存储空间，通过trim函数释放存储空间。存储空间为10，成员类型为int的集合变量x，存储方式如下图所示：

集合类型的使用 - 图2

其中成员 x(2)，x(5)，x(8)三个成员是无效的，但是存储空间仍然保留，后续可以继续赋值，而不需要重新分配空间。

定义集合类型后，使用table_type作为类型名声明变量：

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

可在变量声明时或者声明后使用类型构造器对变量进行初始化。如未初始化，变量var_name的值为NULL。

变量声明和初始化后，可通过下标访问集合成员，或者对成员进行赋值。下标的范围为 [1, upper], upper 的值为当前空间的大小。如访问被删除的成员，会返回no data found的错误信息。

集合类型的使用 - 图3 说明：

  • 非兼容A模式下（参数sql_compatibility值不为A），不支持创建集合类型。
  • 在openGauss中，无索引的集合不会自动增长，访问下标越界时会报错。
  • 支持在schema、匿名块、存储过程、自定义函数、package中定义的集合类型，其作用域各不相同。
  • NOT NULL只支持语法不支持功能。
  • char、vachar、numeric、float、number等元素类型的范围约束语法创建集合类型，例如：“create type t1 is table of numeric(5); ”只支持语法创建，不支持元素的范围约束功能，功能等同于“create type t1 is table of numeric;”。
  • data_type可以为基础数据类型、或存储过程内定义的record类型、集合类型、数组类型，不支持ref cursor类型。
  • 不同的集合类型的变量不能相互赋值。即使成员类型相同，但集合类型名称不同，也是不同的集合类型。如TYPE t1 IS TABLE OF int;和TYPE t2 IS TABLE OF int;定义的两个集合类型，t1和t2是不同的集合类型，以其定义的变量不支持相互赋值。
  • 不支持数组成员的类型为集合类型，包括间接嵌套的场景，如数组成员为record类型，record类型某个成员的类型为集合的场景。
  • 只支持集合的等值(=)与非等值(<>或!=)比较，不支持其他关系运算和算数运算操作。
  • 集合类型与NULL比较时，请使用 IS [ NOT ] NULL，使用 = 操作符与NULL比较的结果不准确。
  • 支持集合类型变量作为函数的参数和返回值，此时要求参数或者返回值的类型是在schema或者package中定义的集合类型。
  • 无索引的集合作为函数入参时，可以传入对应子元素类型相同的数组类型作为入参，不支持多维数组，且要求数组下标从1开始（过时的方法，不建议使用该功能。可执行“set behavior_compat_options = ‘disable_rewrite_nesttable’;”禁用）。
  • 不支持对XML类型数据操作。
  • 集合类型的构造器不支持浮点数以及表达式作为下标。

openGauss支持使用圆括号来访问集合元素，且还支持一些特有的函数，如extend，count，first，last, prior, next, delete来访问集合的内容。

集合函数支持multiset union/intersect/except all/distinct函数。

带索引的集合类型

该集合类型将下标和对应成员值以键值对的方式存储在HASH表中，对该类型变量的所有操作实际就是对HASH表的操作。用户无需自行扩展或释放存储空间，仅需通过赋值或delete方式进行存储和删除成员。集合相关操作、说明如下：

  1. 类型定义

    索引集合类型定义需同时指定成员类型data_type和下标类型indexby_type，其中下标类型仅支持integer和varchar。

  2. 变量声明和初始化

    索引集合类型声明后存在3种初始化场景：未初始化、初始化为空、初始化指定下标和成员值。其中未初始化和初始化为空场景对变量的效果一致。未初始化或初始化为空后变量不为NULL，后续都可以对变量直接进行赋值。初始化指定下标和成员值场景会将指定的下标和成员值以键值对的形式保存到变量中。

  3. 变量赋值

    索引集合类型变量赋值分为两种：成员赋值和整体赋值。成员赋值可通过指定下标方式对某个成员赋值，若该成员不存在则直接赋值，若存在则刷新该成员值。整体赋值则会将被赋值变量中原有成员都清空后重新保存新的成员值。整体赋值场景不能给变量赋NULL值，否则报错。

  4. 变量取值

    通过指定下标方式可获取变量中对应下标的成员值，若通过下标找不到该成员则会返回no data found的错误信息。

集合类型的使用 - 图4 说明：

  • 非兼容A模式下（参数sql_compatibility值不为A），不支持创建带索引集合类型。
  • 支持在匿名块、存储过程、自定义函数、package中定义带索引集合类型，其作用域各不相同。不支持在schema中定义带索引集合类型。
  • NOT NULL只支持语法不支持功能。
  • char、vachar、numeric、float、number等元素类型的范围约束语法创建集合类型，例如：“type t1 is table of numeric(5) index by int; ”只支持语法创建，不支持元素的范围约束功能，功能等同于“create type t1 is table of numeric;”。
  • data_type可以为基础数据类型，或存储过程内定义的record类型，集合类型，数组类型，不支持ref cursor类型。
  • indexby_type仅支持integer和varchar，其中varchar的长度暂不约束。
  • 未初始化的带索引集合类型变量非NULL。
  • 带索引集合类型变量不能赋NULL值，否则报错。
  • 带索引集合类型变量作为入参不能赋NULL值或’’。
  • 不同的带索引集合类型的变量不能相互赋值。即使成员类型和下标类型相同，但集合类型名称不同，也是不同的集合类型。如 TYPE t1 IS TABLE OF int index by int; 和 TYPE t2 IS TABLE OF int index by int; 定义的两个集合类型，t1和t2是不同的集合类型，以其定义的变量不支持相互赋值。
  • 带索引集合类型不支持关系运算和算数运算操作。
  • select … bulk collect into 方式赋值带索引集合类型变量时，只支持下标为integer类型的集合类型，下标为varchar类型集合不支持。
  • 支持带索引集合类型变量作为函数的参数和返回值，此时要求参数或者返回值的类型是在package中定义的集合类型。
  • 带索引的集合作为函数入参时，可以传入对应子元素类型相同的数组类型作为入参，不支持多维数组，不支持索引类型为varcahr（过时的方法，不建议使用该功能。可执行“set behavior_compat_options = ‘disable_rewrite_nesttable’;”禁用）。
  • 类型构造器目前仅支持集合类型，其参数个数的上限与用户自定义函数参数个数上限相同。对于带索引的集合类型，构造器在使用时索引的值仅支持为常量。
  • 不支持对XML类型数据操作。

示例

示例1：无索引的集合类型。

  1. --演示在存储过程中对集合进行操作。
  2. openGauss=# CREATE OR REPLACE PROCEDURE table_proc AS
  3. DECLARE
  4.        TYPE TABLE_INTEGER IS TABLE OF INTEGER;--定义集合类型
  5.        TABLEINT TABLE_INTEGER := TABLE_INTEGER();  --声明集合类型的变量 
  6. BEGIN 
  7.        TABLEINT.extend(10);  
  8.        FOR I IN 1..10 LOOP  
  9.            TABLEINT(I) := I; 
  10.        END LOOP; 
  11.        DBE_OUTPUT.PRINT_LINE(TABLEINT.COUNT);  
  12.        DBE_OUTPUT.PRINT_LINE(TABLEINT(1));  
  13.        DBE_OUTPUT.PRINT_LINE(TABLEINT(10)); 
  14. END;  
  15. /
  17. --调用该存储过程。
  18. openGauss=# CALL table_proc();
  20. --删除存储过程。
  21. openGauss=# DROP PROCEDURE table_proc;
  23. --演示在存储过程中对嵌套集合进行操作。
  24. openGauss=# CREATE OR REPLACE PROCEDURE nest_table_proc AS
  25. DECLARE
  26.        TYPE TABLE_INTEGER IS TABLE OF INTEGER;--定义集合类型
  27.        TYPE NEST_TABLE_INTEGER IS TABLE OF TABLE_INTEGER;--定义集合类型
  28.        NEST_TABLE_VAR NEST_TABLE_INTEGER := NEST_TABLE_INTEGER(); --声明嵌套集合类型的变量
  29. BEGIN 
  30.        NEST_TABLE_VAR.extend(10);
  31.        FOR I IN 1..10 LOOP  
  32.            NEST_TABLE_VAR(I) := TABLE_INTEGER();
  33.            NEST_TABLE_VAR(I).extend(10);
  34.            NEST_TABLE_VAR(I)(I) := I; 
  35.        END LOOP; 
  36.        DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR.COUNT);  
  37.        DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR(1)(1));  
  38.        DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR(10)(10)); 
  39. END;  
  40. /
  42. --调用该存储过程。
  43. openGauss=# CALL nest_table_proc();
  45. --删除存储过程。
  46. openGauss=# DROP PROCEDURE nest_table_proc;

示例2：带索引的集合类型。

  1. --演示在存储过程中对带索引集合进行操作。
  2. openGauss=# CREATE OR REPLACE PROCEDURE index_table_proc AS
  3. DECLARE
  4.        TYPE TABLE_INTEGER IS TABLE OF INTEGER INDEX BY INTEGER; --定义集合类型
  5.        TYPE TABLE_VARCHAR IS TABLE OF INTEGER INDEX BY VARCHAR; --定义集合类型
  6.        TABLEINT_01 TABLE_INTEGER;                               --声明集合类型变量,未初始化
  7.        TABLEINT_02 TABLE_INTEGER := TABLE_INTEGER();            --声明集合类型变量,初始化为空 
  8.        TABLEINT_03 TABLE_INTEGER := TABLE_INTEGER(2=>3,3=>4);   --声明集合类型变量,初始化指定值 
  9.        RES INTEGER;
  10. BEGIN     
  11.        FOR I IN 1..10 LOOP  
  12.            TABLEINT_01(I) := I;     --成员赋值
  13.            TABLEINT_02(I) := I + 1; --成员赋值
  14.        END LOOP; 
  15.        TABLEINT_01 := TABLEINT_02;  --整体赋值
  16.        RES := TABLEINT_03(2);       --取值 
  17.        DBE_OUTPUT.PRINT_LINE(RES);  
  18.        DBE_OUTPUT.PRINT_LINE(TABLEINT_01(1));  
  19.        DBE_OUTPUT.PRINT_LINE(TABLEINT_01(10)); 
  20. END;  
  21. /
  23. --调用该存储过程。
  24. openGauss=# CALL index_table_proc();
  26. --删除存储过程。
  27. openGauss=# DROP PROCEDURE index_table_proc;
  29. --演示在存储过程中对嵌套集合进行操作。
  30. openGauss=# CREATE OR REPLACE PROCEDURE nest_table_proc AS
  31. DECLARE
  32.        TYPE TABLE_INTEGER IS TABLE OF INTEGER INDEX BY INTEGER;           --定义集合类型
  33.        TYPE NEST_TABLE_INTEGER IS TABLE OF TABLE_INTEGER INDEX BY INTEGER;--定义集合类型
  34.        NEST_TABLE_VAR NEST_TABLE_INTEGER;                                 --声明嵌套集合类型的变量
  35. BEGIN 
  36.        FOR I IN 1..10 LOOP  
  37.                NEST_TABLE_VAR(I)(I) := I; 
  38.        END LOOP; 
  39.        DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR.COUNT);  
  40.        DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR(1)(1));  
  41.        DBE_OUTPUT.PRINT_LINE(NEST_TABLE_VAR(10)(10)); 
  42. END;  
  43. /
  45. --调用该存储过程。
  46. openGauss=# CALL nest_table_proc();
  48. --删除存储过程。
  49. openGauss=# DROP PROCEDURE nest_table_proc;