GBase 8s SET、LIST、MULTISET

GBase 8s SET、LIST、MULTISET

GBase 8s 支持的COLLECTION 数据类型包括：SET、MULTISET 和 LIST

SET 是无序元素集合，每个元素值唯一。

MULTISET 是无序元素集合，每个元素值可重复。

LIST 是有序元素集合，每个元素值可重复。

当尝试插入包含重复值的SET元素时，数据库默认只保留一个值，舍弃所有重复的元素。

COLLECTION 数据类型均不允许 Null 元素，所以在定义 SET 类型时，必须指定 NOT NULL 约束。

通过 SPL 例程，访、存到集合内的元素内容，可实现单个元素插入、更新、删除的操作。

对集合类型增删改查

数据准备。

create table tab1 (c1 int, c2 list (varchar(20) not null));
---整行插入
insert into tab1 values(1,list{'111','222','333'});

查询集合，可查询整表或集合内元素数据。

---查询整表
> select * from tab1;

c1  1
c2  LIST{'111','999','333'} 

1 row(s) retrieved.

---查询set内容
> select * from table((select c2 from tab1)) as t1(c1);

c1                   

111                 
999                 
333                 

3 row(s) retrieved.

插入新元素到集合尾部，插入 444 到LIST集合尾部。

> create procedure p1 ()
    define x list(varchar(20) not null);
    select limit 1 c2 into x from tab1 ;
    insert into table(x) values('444');
    update tab1 set c2 = x where c1 =1;
  end procedure;
> call p1();
> select * from tab1;

c1  1
c2  LIST{'111','222','333','444'} 

1 row(s) retrieved.

插入新元素到集合指定位置，插入 555 到 LIST 集合指定位置 3 。

注：插入到指定位置，仅 LIST 类型支持，因为 LIST 类型集合内有序存储。

> create procedure p1 ()
    define x list(varchar(20) not null);
    select limit 1 c2 into x from tab1 ;
    insert at 3 into table(x) values('555');
    update tab1 set c2 = x where c1 =1;
  end procedure;
> call p1();
> select * from tab1;

c1  1
c2  LIST{'111','222','555','333','444'} 

1 row(s) retrieved.

更新集合指定位置元素，更新LIST 222 元素为 999。

> create procedure p1 ()
    define x LIST(varchar(20) not null);   
    define str varchar(20);
    let str = null;
    select limit 1 c2 into x from tab1 where c1 = 1;
    foreach cursor1 for SELECT * into str FROM TABLE(x)(cc1)
        if str = '222' then
            UPDATE TABLE(x)(cc1) SET cc1 = '999' WHERE CURRENT OF cursor1; 
        else  CONTINUE FOREACH;
        end if;
    END foreach;
    update tab1 set c2 = x where c1 =1;
  end procedure;
> call p1();
> select * from tab1;

c1  1
c2  LIST{'111','999','555','333','444'} 

1 row(s) retrieved.

删除集合指定元素，删除LIST 内 333 元素。

> create procedure p1 ()
    define x LIST(varchar(20) not null);   
    define str varchar(20);
    let str = null;
    select limit 1 c2 into x from tab1 where c1 = 1;
    foreach cursor1 for SELECT * into str FROM TABLE(x)(cc1)
        if str = '333' then
           delete from TABLE(x) WHERE CURRENT OF cursor1; 
        else  CONTINUE FOREACH;
        end if;
    END foreach;
    update tab1 set c2 = x where c1 =1;
end procedure;
> call p1();
> select * from tab1;

c1  1
c2  LIST{'111','999','555','444'} 

1 row(s) retrieved.

嵌套集合

嵌套集合，指集合的元素数据类型为复杂类型（collection 或 row 类型）。

使用嵌套集合，可以实现在单行单列的数据格内存储一个二维数据结构。在一定程度上，可存储 Oracle 的 嵌套表类型。

嵌套集合增删改查

数据准备。

---创建ROWTYPE
create row type nested (n1 int,n2 varchar(20));

---创建基础表
create table tab1 (c1 int,c2 List(nested not null));

---插入数据
insert into tab1 values(1,LIST{ROW(1,'nestedstring1')::nested,ROW(2,'nestedstring2')::nested});
insert into tab1 values(2,LIST{ROW(1,'nestedstring21')::nested,ROW(2,'nestedstring22')::nested});

查询嵌套集合，可查询整表或集合内元素数据。

---查询整表
> select * from tab1;


c1  1
c2  LIST{ROW(1          ,'nestedstring1'),ROW(2          ,'nestedstring2')} 

c1  2
c2  LIST{ROW(1          ,'nestedstring21'),ROW(2          ,'nestedstring22')} 

2 row(s) retrieved.

---查询 c1=1 行的嵌套集合 
> select * from table((select C2 from tab1 where c1 =1)); 

         n1 n2                   

          1 nestedstring1       
          2 nestedstring2       

2 row(s) retrieved.

对嵌套集合增删改，示例中，对表 tab1 中 c1 =1 的数据行内的嵌套集合元素进行如下操作：

• 删除 n1 = 1 的子元素
• 更新 n1 = 2 的子元素
• 在嵌套集合第一行插入新元素
• 在嵌套集合末尾插入新元素

> create procedure p1()
    define nest LIST(nested not null);
    define t nested;
    select c2 into nest from tab1 where c1 = 1;
    
    FOREACH cursor1 FOR
    SELECT * INTO t FROM TABLE(nest)
    if t.n1 = 1 then
    ---删除指定位置
       DELETE FROM TABLE(nest) 
       WHERE CURRENT OF cursor1;
    elif t.n1 = 2 then
    ---更新指定行位置   
    UPDATE TABLE(nest)(n) 
    SET n = ROW(2,'update_string')::nested WHERE CURRENT OF cursor1;  
    else 
    CONTINUE FOREACH;
    END IF;
    END foreach;
    --插入指定位置 1
    insert at 1 into table(nest) values (ROW(9,'insert_string_at_1')::nested);
    --插入嵌套集合末尾
    insert into table(nest) values (ROW(10,'insert_string_append')::nested);
    --更新到tab1表内
    update tab1 set c2= nest where c1 =1;   
  end procedure;
> call p1();
> select * from table((select C2 from tab1 where c1 =1));


         n1 n2                   

          9 insert_string_at_1  
          2 update_string       
         10 insert_string_append

3 row(s) retrieved.