华为云云数据库GaussDB集合支持的函数_云淘科技
集合操作符
=
参数：nesttable类型
返回值：true or false，bool类型
功能描述：两个集合类型是否相等。
示例：
gaussdb=# declare gaussdb-# type nest is table of int; gaussdb-# a nest := nest(1,2); gaussdb-# b nest := nest(1,2); gaussdb-# flag bool; gaussdb-# begin gaussdb$# flag := a = b; gaussdb$# raise info '%', flag; gaussdb$# end; gaussdb$# / INFO: t ANONYMOUS BLOCK EXECUTE
参数：nesttable类型
返回值：true or false，bool类型
功能描述：两个集合类型是否不相等。
示例：
gaussdb=# declare gaussdb-# type nest is table of int; gaussdb-# a nest := nest(1,2); gaussdb-# b nest := nest(1,2); gaussdb-# flag bool; gaussdb-# begin gaussdb$# flag := a b; gaussdb$# raise info '%', flag; gaussdb$# end; gaussdb$# / INFO: f ANONYMOUS BLOCK EXECUTE
集合MULTISET函数
MULTISET UNION [ALL | DISTINCT]
参数：nesttable类型
返回值：nesttable类型
功能描述：两个集合变量的并集，ALL不去除重复元素，DISTINCT去除重复元素。
示例：
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1,2);
gaussdb-# b nest := nest(2,3);
gaussdb-# begin
gaussdb$# a := a MULTISET UNION ALL b;
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {1,2,2,3}
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1,2);
gaussdb-# b nest := nest(2,3);
gaussdb-# begin
gaussdb$# a := a MULTISET UNION DISTINCT b;
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {1,2,3}
ANONYMOUS BLOCK EXECUTE
MULTISET EXCEPT [ALL | DISTINCT]
参数：nesttable类型
返回值：nesttable类型
功能描述：两个集合变量的差集。如A MULTISET EXCEPT B：ALL表示去除A中与B重复的元素；DISTINCT表示先对A进行去重操作，然后去除与B中有重复的元素。
示例：
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1,2,2);
gaussdbs-# b nest := nest(2,3);
gaussdb-# begin
gaussdb$# a := a MULTISET EXCEPT ALL b;
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {1,2}
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1,2,2);
gaussdb-# b nest := nest(2,3);
gaussdb-# begin
gaussdb$# a := a MULTISET EXCEPT DISTINCT b;
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {1}
ANONYMOUS BLOCK EXECUTE
MULTISET INTERSECT [ALL | DISTINCT]
参数：nesttable类型
返回值：nesttable类型
功能描述：两个集合变量的交集。如 A MULTISET INTERSECT B：ALL表是取A与B所有重复的元素；DISTINCT表示取A与B中重复元素，且去除重复元素。
示例：
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1,2,2);
gaussdbs-# b nest := nest(2,2,3);
gaussdb-# begin
gaussdb$# a := a MULTISET INTERSECT ALL b;
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {2,2}
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1,2,2);
gaussdb-# b nest := nest(2,2,3);
gaussdb-# begin
gaussdb$# a := a MULTISET INTERSECT DISTINCT b;
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {2}
ANONYMOUS BLOCK EXECUTE
集合类型函数
exists(idx)
参数：idx为int4类型或varchar类型，
返回值：true or false，bool类型
功能描述：查找指定位置是否存在有效元素。
示例：
gaussdb=# declare
gaussdb-# type nest is table of varchar2;
gaussdb-# a nest := nest('happy','?');
gaussdb-# flag bool;
gaussdb-# begin
gaussdb$# flag := a.exists(1);
gaussdb$# raise info '%', flag;
gaussdb$# flag := a.exists(10);
gaussdb$# raise info '%', flag;
gaussdb$# end;
gaussdb$# /
INFO: t
INFO: f
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of varchar2 index by varchar2;
gaussdb-# a nest;
gaussdb-# flag bool;
gaussdb-# begin
gaussdb$# a('1') := 'Be';
gaussdb$# a('2') := 'happy';
gaussdb$# a('3') := '.';
gaussdb$# flag := a.exists('1');
gaussdb$# raise info '%', flag;
gaussdb$# flag := a.exists('ddd');
gaussdb$# raise info '%', flag;
gaussdb$# end;
gaussdb$# /
INFO: t
INFO: f
ANONYMOUS BLOCK EXECUTE
extend[(count[, idx])]
参数：idx和count为int4类型
返回值：无返回值
功能描述：仅支持nesttable类型。在nesttable变量末尾拓展1个或count个元素。存在idx下标元素时，拷贝count个idx下元素到变量末尾。
示例：
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1);
gaussdb-# begin
gaussdb$# raise info '%', a;
gaussdb$# a.extend;
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {1}
INFO: {1,NULL}
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1);
gaussdb-# begin
gaussdb$# raise info '%', a;
gaussdb$# a.extend(2);
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {1}
INFO: {1,NULL,NULL}
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1);
gaussdb-# begin
gaussdb$# raise info '%', a;
gaussdb$# a.extend(2,1);
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {1}
INFO: {1,1,1}
ANONYMOUS BLOCK EXECUTE
delete[(idx1[, idx2])]
参数：idx1和idx2为int4类型或varchar2类型
返回值：无返回值
功能描述：无参数时，（nesttable）删除集合类型的所有元素和空间，后续使用需要重新extend，（indexbytable）删除所有元素内容，一个参数删除指定位置元素（不删除空间），两个参数删除小标区间内的元素（不删除空间）。
约束：删除元素而没删除空间，会使该空间处于无效状态，可继续赋值。作为函数返回值或者自治事务入参时，会删除无效成员的存储空间，所有成员的下标会相应发生改变。
示例：
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1,2,3,4,5);
gaussdb-# begin
gaussdb$# raise info '%', a;
gaussdb$# a.delete;
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {1,2,3,4,5}
INFO: {}
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1,2,3,4,5);
gaussdb-# begin
gaussdb$# raise info '%', a;
gaussdb$# a.delete(3);
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {1,2,3,4,5}
INFO: {1,2,4,5}
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# a nest := nest(1,2,3,4,5);
gaussdb-# begin
gaussdb$# raise info '%', a;
gaussdb$# a.delete(2,4);
gaussdb$# raise info '%', a;
gaussdb$# end;
gaussdb$# /
INFO: {1,2,3,4,5}
INFO: {1,5}
ANONYMOUS BLOCK EXECUTE
trim[(n)]
参数：n为int4类型
返回值：无返回值
功能描述：仅支持nesttable类型，无参数时，删除末尾一个元素空间，输入参数合法时，删除末尾指定数量元素空间。
示例：
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# aa nest:=nest(11,22,33,44,55);
gaussdb-# begin
gaussdb$# raise info 'aa:%' ,aa;
gaussdb$# aa.trim;
gaussdb$# raise info 'aa:%' ,aa;
gaussdb$# aa.trim(2);
gaussdb$# raise info 'aa:%' ,aa;
gaussdb$# end;
gaussdb$# /
INFO: aa:{11,22,33,44,55}
INFO: aa:{11,22,33,44}
INFO: aa:{11,22}
ANONYMOUS BLOCK EXECUTE
count
参数：无
返回值：int类型
功能描述：返回集合中存在有效元素的个数。
示例：
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# aa nest:=nest(11,22,33,44,55);
gaussdb-# begin
gaussdb$# raise info 'count:%' ,aa.count;
gaussdb$# end;
gaussdb$# /
INFO: count:5
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int index by varchar;
gaussdb-# aa nest;
gaussdb-# begin
gaussdb$# aa('aaa') := 111;
gaussdb$# aa('bbb') := 222;
gaussdb$# aa('ccc') := 333;
gaussdb$# raise info 'count:%' ,aa.count;
gaussdb$# end;
gaussdb$# /
INFO: count:3
ANONYMOUS BLOCK EXECUTE
first
参数：无
返回值：int类型或varchar类型
功能描述：返回集合中第一个有效元素的下标。
示例：
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# aa nest:=nest(11,22,33,44,55);
gaussdb-# begin
gaussdb$# raise info 'first:%' ,aa.first;
gaussdb$# end;
gaussdb$# /
INFO: first:1
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int index by varchar;
gaussdb-# aa nest;
gaussdb-# begin
gaussdb$# aa('aaa') := 111;
gaussdb$# aa('bbb') := 222;
gaussdb$# aa('ccc') := 333;
gaussdb$# raise info 'first:%' ,aa.first;
gaussdb$# end;
gaussdb$# /
INFO: first:aaa
ANONYMOUS BLOCK EXECUTE
last
参数：无
返回值：int类型或varchar类型
功能描述：返回集合中最后一个有效元素的下标。
示例：
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# aa nest:=nest(11,22,33,44,55);
gaussdb-# begin
gaussdb$# raise info 'last:%' ,aa.last;
gaussdb$# end;
gaussdb$# /
INFO: last:5
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int index by varchar;
gaussdb-# aa nest;
gaussdb-# begin
gaussdb$# aa('aaa') := 111;
gaussdb$# aa('bbb') := 222;
gaussdb$# aa('ccc') := 333;
gaussdb$# raise info 'last:%' ,aa.last;
gaussdb$# end;
gaussdb$# /
INFO: last:ccc
ANONYMOUS BLOCK EXECUTE
prior(idx)
参数：idx为int类型或varchar类型
返回值：int类型或varchar类型
功能描述：返回集合中当前下标的前一个有效元素下标。
示例：
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# aa nest:=nest(11,22,33,44,55);
gaussdb-# begin
gaussdb$# raise info 'prior:%' ,aa.prior(3);
gaussdb$# end;
gaussdb$# /
INFO: prior:2
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int index by varchar;
gaussdb-# aa nest;
gaussdb-# begin
gaussdb$# aa('aaa') := 111;
gaussdb$# aa('bbb') := 222;
gaussdb$# aa('ccc') := 333;
gaussdb$# raise info 'prior:%' ,aa.prior('bbb');
gaussdb$# end;
gaussdb$# /
INFO: prior:aaa
ANONYMOUS BLOCK EXECUTE
next(idx)
参数：idx为int类型或varchar类型
返回值：int类型或varchar类型
功能描述：返回集合中当前下标的后一个有效元素下标。
示例：
gaussdb=# declare
gaussdb-# type nest is table of int;
gaussdb-# aa nest:=nest(11,22,33,44,55);
gaussdb-# begin
gaussdb$# raise info 'next:%' ,aa.next(3);
gaussdb$# end;
gaussdb$# /
INFO: next:4
ANONYMOUS BLOCK EXECUTE
gaussdb=# declare
gaussdb-# type nest is table of int index by varchar;
gaussdb-# aa nest;
gaussdb-# begin
gaussdb$# aa('aaa') := 111;
gaussdb$# aa('bbb') := 222;
gaussdb$# aa('ccc') := 333;
gaussdb$# raise info 'next:%' ,aa.next('bbb');
gaussdb$# end;
gaussdb$# /
INFO: next:ccc
ANONYMOUS BLOCK EXECUTE
limit
参数：无
返回值：null
功能描述：用于nesttable类型，返回集合中最大可以储存的元素个数，只适用于array类型，nesttable返回空。
示例：
gaussdb=# declare gaussdb-# type nest is table of int; gaussdb-# aa nest:=nest(11,22,33,44,55); gaussdb-# begin gaussdb$# raise info 'limit:%' ,aa.limit; gaussdb$# end; gaussdb$# / INFO: limit: ANONYMOUS BLOCK EXECUTE
集合相关函数
unnest_table(anynesttable)或unnest(anynesttable)
描述：返回nesttable中的元素集合。
返回类型：setof anyelement
示例：
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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
create or replace procedure f1()
as
type t1 is table of int;
v2 t1 := t1(null, 2, 3, 4, null);
tmp int;
cursor c1 is select * from unnest_table(v2);
begin
open c1;
for i in 1 .. v2.count loop
fetch c1 into tmp;
if tmp is null then
dbe_output.print_line(i || ': is null');
else
dbe_output.print_line(i || ': ' || tmp);
end if;
end loop;
close c1;
end;
/
gaussdb=# call f1();
1: is null
2: 2
3: 3
4: 4
5: is null
f1
----
(1 row)
-- nesttable嵌套record类型示例
create or replace procedure p1() is
type rec is record(c1 int, c2 int);
type t1 is table of rec;
v t1 := t1(rec(1, 1), rec(2, null), rec(null, null), null);
v2 t1 := t1();
cursor cur is select * from unnest(v);
begin
v2.extend(v.count);
open cur;
for i in 1 .. v.count loop
fetch cur into v2(i);
raise info '%', v2(i);
end loop;
close cur;
end;
/
gaussdb=# call p1();
INFO: (1,1)
INFO: (2,)
INFO: (,)
INFO: (,)
p1
----
(1 row)
|
当集合的元素类型为record类型且元素为NULL时，会返回一个所有列为NULL的record，具体可参考示例。
unnest_table(anyindexbytable)
描述：返回table of index by类型根据index排序后的元素集合。
返回类型：setof anyelement
约束：只支持index by int类型，不支持index by varchar类型。
示例：
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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
create or replace procedure f1()
as
type t1 is table of int index by int;
v2 t1 := t1(1=>1, -10=>(-10), 6=>6, 4=>null);
tmp int;
cursor c1 is select * from unnest_table(v2);
begin
open c1;
for i in 1 .. v2.count loop
fetch c1 into tmp;
if tmp is null then
dbe_output.print_line(i || ': is null');
else
dbe_output.print_line(i || ': ' || tmp);
end if;
end loop;
close c1;
end;
/
gaussdb=# call f1();
1: -10
2: 1
3: is null
4: 6
f1
----
(1 row)
-- index by table嵌套record类型示例
create or replace procedure p1() is
type rec is record(c1 int, c2 int);
type t1 is table of rec index by int;
v t1 := t1(1 => rec(1, 1), 2 => rec(2, null), 3 => rec(null, null), 4 => null);
v2 t1 := t1();
cursor cur is select * from unnest(v);
begin
open cur;
for i in 1 .. v.count loop
fetch cur into v2(i);
raise info '%', v2(i);
end loop;
close cur;
end;
/
gaussdb=# call p1();
INFO: (1,1)
INFO: (2,)
INFO: (,)
INFO: (,)
p1
----
(1 row)
|
当集合的元素类型为record类型且元素为NULL时，会返回一个所有列为NULL的record，具体可参考示例。
父主题： 集合
同意关联代理商云淘科技，购买华为云产品更优惠（QQ 78315851）
内容没看懂？ 不太想学习？想快速解决？ 有偿解决： 联系专家
