LightDB支持嵌套表(nested table)

追魂曲

已于 2023-08-09 20:49:32 修改

阅读量158

点赞数

分类专栏： LightDB postgresql 文章标签： postgresql

于 2023-08-09 20:45:24 首次发布

本文链接：https://blog.csdn.net/qq_22066003/article/details/132193017

版权

LightDB 同时被 2 个专栏收录

34 篇文章 1 订阅

订阅专栏

postgresql

32 篇文章 0 订阅

订阅专栏

1. 基本概念

从概念上讲，嵌套表就像一个具有任意数量元素的一维数组。但是，嵌套表与数组在以下几个重要方面有所不同：

数组需要声明元素个数，但嵌套表不需要。嵌套表的大小可以动态增加。
数组总是稠密的。嵌套数组最初是密集的，但可能变得稀疏，因为可以从中删除元素。

嵌套表与联合数组类似（联合数组详细信息看之前的文章），不同之处在于：

嵌套表的索引值类型必须为数字类型，索引值为从1 开始的有序关键字，没有固定上限。
嵌套表除了可以在PL/SQL 匿名块、包规范中定义外，还可以作为独立的数据库对象存储在数据库服务器中，是全局数据类型。
嵌套表必须使用构造器进行初始化后才可以使用。没有初始化的嵌套表被自动赋值为NULL 。
初始化时嵌套表中元素是连续存储的，元素索引值是连续的（密集集合）。在后续操作过程中，随着部分元素被删除，元素可以不连续存储，元素之间可以存在间隙（稀疏集合）。

以下是联合数组和嵌套表的区别总结：

Collection Type	Number of Elements	Index Type	Dense or Sparse	Uninitialized Status	Where Defined	Can Be Attribute Data Type
Associative array (or index-by table)	Unspecified	String or PLS_INTEGER	Either	Empty	In PL/SQL block or package	No
Nested table	Unspecified	Integer	Starts dense, can become sparse	Null	In PL/SQL block or package or at schema level	Only if defined at schema level

2. 全局使用

语法：

create type 自定义嵌套表名称 is table of 类型名称

创建全局的嵌套表类型：

create type nt_int is table of int;
create type nt_varchar is table of varchar(64);

我们可以直接利用该类型：

lightdb@postgres=# select nt_int(1, 2, 1000);
   nt_int   
------------
 {1,2,1000}
(1 row)
lightdb@postgres=# select nt_varchar('apple', 'lightdb', 'success');
       nt_varchar        
-------------------------
 {apple,lightdb,success}
(1 row)

也可以创建带嵌套表类型的表：

lightdb@postgres=# create table nt_table1(a int, b nt_int, c nt_varchar) NESTED TABLE b STORE AS col1_tab_b, NESTED TABLE c STORE AS col1_tab_c;
CREATE TABLE
lightdb@postgres=# \d nt_table1
               Table "public.nt_table1"
 Column |    Type    | Collation | Nullable | Default 
--------+------------+-----------+----------+---------
 a      | integer    |           |          | 
 b      | nt_int     |           |          | 
 c      | nt_varchar |           |          | 
lightdb@postgres=# insert into nt_table1 values(1, nt_int(1,2,3), nt_varchar('abc', 'test'));
INSERT 0 1
lightdb@postgres=# insert into nt_table1 values(2, nt_int(), nt_varchar());
INSERT 0 1
lightdb@postgres=# select * from nt_table1;
 a |    b    |     c      
---+---------+------------
 1 | {1,2,3} | {abc,test}
 2 | {}      | {}
(2 rows)

我们可以看到表nt_table1中的b和c字段对应的类型均为嵌套表类型。

可以结合table函数对表进行查询：

lightdb@postgres=# select * from nt_table1, table(b);
 a |    b    |     c      | COLUMN_VALUE 
---+---------+------------+--------------
 1 | {1,2,3} | {abc,test} |            1
 1 | {1,2,3} | {abc,test} |            2
 1 | {1,2,3} | {abc,test} |            3
(3 rows)

lightdb@postgres=# select * from nt_table1, table(c);
 a |    b    |     c      | COLUMN_VALUE 
---+---------+------------+--------------
 1 | {1,2,3} | {abc,test} | abc
 1 | {1,2,3} | {abc,test} | test
(2 rows)

在匿名块中也可以使用：

lightdb@postgres=# declare
lightdb@postgres$# a nt_int := nt_int(1, 2, 3);
lightdb@postgres$# b a%type := nt_int(4, 5, 6);
lightdb@postgres$# begin
lightdb@postgres$# raise notice 'a = %, b = %', a, b;
lightdb@postgres$# end;
lightdb@postgres$# /
NOTICE:  a = {1,2,3}, b = {4,5,6}
DO

可以看出匿名块中支持嵌套表的构造器赋值，也支持对嵌套表类型的%type

在包中也能定义嵌套表类型变量：

lightdb@postgres=# CREATE OR REPLACE PACKAGE pkg_test IS
lightdb@postgres$#   type1 nt_int;
lightdb@postgres$# END;
lightdb@postgres$# /
CREATE PACKAGE
lightdb@postgres=# declare
lightdb@postgres$# a pkg_test.type1%type := nt_int(1, 2, 3);
lightdb@postgres$# b a%type := nt_int(4, 5, 6);
lightdb@postgres$# begin
lightdb@postgres$# raise notice 'a = %, b = %', a, b;
lightdb@postgres$# end;
lightdb@postgres$# /
NOTICE:  a = {1,2,3}, b = {4,5,6}
DO

可以看出包中支持嵌套表的构造器赋值，也支持对嵌套表类型的%type

嵌套表类型支持作为函数返回值：

可以结合table函数使用

create table tab1(a int, b int);
insert into tab1 (values (1,1), (2,2), (3,3));
select * from tab1;

create or replace package typkg
as
	type nt is table of tab1%rowtype;
	res nt;
	function myfunc(v int) return nt;
end;
/

create or replace package body typkg
as
	function myfunc(v int) return nt
	is
	begin
		res(1) := ROW(1,1);
		res(2) := ROW(2,2);
		res(3) := ROW(3,3);
		return res;
	end;
end;
/

select * from table(typkg.myfunc(1));
select rownum,* from table(typkg.myfunc(1));
select rownum,* from table(typkg.myfunc(1)) where rownum < 3;
select * from table(typkg.myfunc(1)) limit 2;
select a from table(typkg.myfunc(1));
select a,a+b as c from table(typkg.myfunc(1));
select * from table(typkg.myfunc(1)) order by b desc;
select count(*) from table(typkg.myfunc(1));
select a,count(*) from table(typkg.myfunc(1)) group by a;
select * from table(typkg.myfunc(1)) t0;
select * from TABLE(typkg.myfunc(1)) as t0;
select * from table(typkg.myfunc(1)) t0,tab1 where t0.a = tab1.a;
select t0.a,t0.b from table(typkg.myfunc(1)) t0,tab1 where t0.a < tab1.a;
select * from table(typkg.myfunc(1)) t0 where t0.a in (select a from tab1) order by t0.a;
select * from tab1 where tab1.a < any (select t0.a from table(typkg.myfunc(1)) t0);
select * from table(typkg.myfunc(1)) t0 join tab1 on t0.a = tab1.a;
select * into tab4 from table(typkg.myfunc(1));

3. 过程语言内部使用

内部支持局部定义嵌套表方法：type 嵌套表名称 is table of 类型名称

-- Change value of nested table
DECLARE
    TYPE Roster IS TABLE OF VARCHAR( 15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    raise info 'Initial Values %', names2;
    names2( 3) := 'P Perez';   -- Change value of one element

    raise info 'Current Values: %', names2;

    names2 := Roster( 'A Jansen', 'B Gupta');   -- Change entire table
    raise info 'Current Values: %', names2;
END;
/
INFO:  Initial Values { "D Caruso", "J Hamil", "D Piro", "R Singh"}
INFO:  Current Values: { "D Caruso", "J Hamil", "P Perez", "R Singh"}
INFO:  Current Values: { "A Jansen", "B Gupta"}

支持first和last方法：

-- add elements and test FIRST(), LAST()

DECLARE
    TYPE Roster IS TABLE OF VARCHAR(15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    raise info 'Initial Values %', names2;
    raise info 'first Values: %', names2.first;
    raise info 'last Values: %', names2.last;

    names2(5) := 'P Perez';   -- add one element

    raise info 'Current Values: %', names2;
    raise info 'last Values: %', names2.last;

    -- add one element, the elements between the valid element and the new element is NULL
    names2(7) := 'A Jansen';

    raise info 'Current Values: %', names2;
    raise info 'last Values: %', names2.last;
END;
/
INFO:  Initial Values { "D Caruso", "J Hamil", "D Piro", "R Singh"}
INFO:   first Values: 1
INFO:   last Values: 4
INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh", "P Perez"}
INFO:   last Values: 5
INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh", "P Perez", NULL, "A Jansen"}
INFO:   last Values: 7

支持extend方法：

-- test EXTEND()
DECLARE
    TYPE Roster IS TABLE OF VARCHAR(15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    raise info 'Initial Values %', names2;

    names2.EXTEND;
    raise info 'Current Values: %', names2;

    names2.EXTEND();
    raise info 'Current Values: %', names2;

    names2.EXTEND(2);
    raise info 'Current Values: %', names2;
END;
/
INFO:  Initial Values { "D Caruso", "J Hamil", "D Piro", "R Singh"}
INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh", NULL}
INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh", NULL, NULL}
INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh", NULL, NULL, NULL, NULL}

支持delete方法：

-- test DELETE()

DECLARE
    TYPE Roster IS TABLE OF VARCHAR(15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    raise info 'Initial Values %', names2;

    names2.DELETE;

    raise info 'Current Values: %', names2;
END;
/
INFO:  Initial Values { "D Caruso", "J Hamil", "D Piro", "R Singh"}
INFO:  Current Values: {}

默认初始化方法：

-- test DEFAULT initialized
DECLARE
    TYPE Roster IS TABLE OF VARCHAR(15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster default Roster('none'); -- default

BEGIN
    raise info 'Initial Values %', names2;

    names2 := Roster();
    raise info 'Current Values: %', names2;

    names2 := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');
    raise info 'Current Values: %', names2;
END;
/
INFO:  Initial Values {none}
INFO:  Current Values: {}
INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh"}

混合用法：

DECLARE
    TYPE Roster IS TABLE OF VARCHAR(15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');
    total integer;
BEGIN
    total := names2.count;
    raise info 'Total % people', total;

    FOR i IN names2.first .. names2.last LOOP
        raise info 'ID: %, people: %', i, names2(i);
    END LOOP;
END;
/
INFO:  Total 4 people
INFO:  ID: 1, people: D Caruso
INFO:  ID: 2, people: J Hamil
INFO:  ID: 3, people: D Piro
INFO:  ID: 4, people: R Singh

DECLARE
    TYPE Roster IS TABLE OF VARCHAR( 15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');
    i integer;
BEGIN
    FOR i IN 1.. 6 LOOP
        IF names2.EXISTS(i) THEN
            raise info 'names2(%) = %', i, names2(i);
        ELSE
            raise info 'names2(%) does not exist', i;
        END IF;
    END LOOP;

END;
/
INFO:  names2(1) = D Caruso
INFO:  names2(2) = J Hamil
INFO:  names2(3) = D Piro
INFO:  names2(4) = R Singh
INFO:  names2(5) does not exist
INFO:  names2(6) does not exist

详细语法可参考LightDB官网查看：

LightDB: 更快、更稳、更懂金融的分布式关系型数据库