VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

嵌套表

功能描述

在数据库中嵌套表是一种列类型,用于存储未指定数量的行。未初始化的嵌套表变量是一个空集合,使用前必须对其进行初始化,方法是将其设置为空或分配一个非NULL的值。

PL/pgSQL内部使用嵌套表首先需要进行声明,定义具体的嵌套表类型的变量,然后进行初始化、必要时申请存储空间进行使用。

嵌套表的使用中支持如下函数和过程:

方法 类型 描述
first 函数(function) 返回嵌套表中的第一个索引值,嵌套表为空时,first 返回null 值。
last 函数(function) 返回嵌套表中的最后一个索引值,嵌套表为空时,last 返回null 值;如果嵌套表只有一个元素,返回值同 first 的返回值。
count 函数(function) 返回嵌套表中元素的数量。
delete 过程(procedure) 从嵌套表中删除元素,有三种形式。
  • delete:从任何类型的嵌套表中删除所有元素。
  • delete(n):删除索引为 n 的元素,如果该元素存在;否则,它什么都不做。
  • delete(m,n):如果m和n都存在且m<=n,则删除所在范围m..n内的所有元素,否则,它什么都不做。
  • trim 过程(procedure) 该过程从嵌套表的末尾删除元素。有两种使用形式。
  • trim: 如果集合至少有一个元素,则从集合的末尾删除一个元素。
  • trim(n):如果末尾至少有n 个元素,则从集合的末尾删除 n个元素。
  • 以上两种使用方式,删除元素个数多于嵌套表元素个数将引发异常。
    extend 过程(procedure) 在嵌套表末尾增加元素,有以下三种使用方式。
  • extend:将一个空元素追加到嵌套表中。
  • extend(n):将n个空元素追加到嵌套表中。
  • extend(n,i):将第个i个元素的n个副本追加到嵌套表中。
  • exists 函数(function) 判断指定元素是否存在,存在返回为 true,否则返回false。
    prior 函数(function) 返回指定索引之前的索引。对于任何嵌套表c,c.prior(c.first)返回null。
    next 函数(function) 返回指定索引之后的索引。对于任何嵌套表c,c.next(c.last)返回null。

    函数和过程的使用方法为nested_table_name.method,其中nested_table_name为嵌套表。

    注意事项

    • 暂不支持嵌套表作为独立的类型,其作为PL/pgSQL内部类型,权限依赖PL/pgSQL自定义函数、存储过程、包和包体的访问权限。
    • 当前版本暂不支持not null选项。

    语法格式

    嵌套表声明语法:

    TYPE type_name is table of datatype [not null]
    

    嵌套表在使用之前必须经过初始化,语法如下:

    • 使用构造函数进行初始化,构造函数与嵌套表类型名字相同:

      v_nested_table type_nested_type:=type_nested_type();
      
    • 使用嵌套表中的一个或多个元素进行初始化:

      collection_type([value[],value]...)
      

    参数说明

    • type_name

      嵌套表名称。

    • datatype

      数据类型,可以是常用数据类型varchar、varchar2、number、interger、pls_integer、binary_integer以及自定义数据类型,不支持REF CURSOR和NCLOB类型。

    • not null

      可选项,对嵌套表的每个元素加非空约束。当前版本暂不支持该选项。

    示例

    示例1: 定义一个嵌套表。

    CREATE OR REPLACE FUNCTION Fun() RETURNS int AS $$
    DECLARE
    TYPE ARRAY_INTEGER IS TABLE OF int;   --定义嵌套表类型
    ARRINT ARRAY_INTEGER;   --定义变量
    BEGIN
    ARRINT[1] := 1;   --通过常量下标给数组赋值进行初始化
    FOR I IN 2..16 LOOP
        select I into ARRINT[I] from dual;  --通过变量下标给数组赋值
    END LOOP;
    RETURN ARRINT[2];
    END;
    $$
    LANGUAGE plpgsql;
    

    返回结果如下,表示支持定义嵌套表:

    CREATE FUNCTION
    

    示例2: 在嵌套表中使用函数。

    CREATE OR REPLACE FUNCTION fun2() RETURNS VOID AS $$
    DECLARE
    Type aa_int_type IS TABLE OF int;
    aa_int aa_int_type;
    BEGIN
    aa_int[1] := 3;
    aa_int[2] := 6;
    aa_int[6] := 12;
    raise info'%',aa_int.count;
    END;
    $$
    LANGUAGE plpgsql;
    select fun2();
    

    返回结果如下,获取到嵌套表中的元素个数为3:

    INFO:  3
    CONTEXT:  referenced column: fun2
     fun2
    ------
    
    (1 row)