oracle中变长数组varray,嵌套表,集合使用方法

创建变长数组类型

create type varray_type as varray(2) of varchar2(50); 

这个变长数组最多可以容纳两个数据,数据的类型为 varchar2(50)

更改元素类型的大小或精度

可以更改变长数组类型和嵌套表类型 元素的大小。

alter type varray_type 
modify element type varchar2(100) cascade;

cascade选项吧更改传播到数据库中的以来对象。也可以用 invalidate 选项使依赖对象无效

增加变长数组的元素数目

alter type vrray_name
modify limit 5 cascade;

使用变长数组

create table table_name(
column_name type,
var_col_name varray_type
);

获得变长数组的信息

desc[ribe] varray_type;

select * 
from user_varrays
where type_name = varray_name;

desc 获得的是 varray_type as varray(2) of varchar2(50)

填充变长数组元素

insert into table_name values(
 value,
 varray_type(
  'xxxx',
  'xx',
  'x')
);

可以一次向变长数组添加多个数据。

查找变长数组元素

select *
from table_name;

如果变长数组中的元素有多个,会一起输出,输出的数据列是一个长列,跟包含对象的表一样。

更改变长数组元素

要想更改变长数组的一个元素,需要把其他元素一起更改,整个变长数组作为一个整体来的。

update table_name
set var_col_name = varray_type('xxx','xxxxxx')
where expr1;

创建嵌套表类型

create type table_type as table of type;

其中type 可以为任何类型,包括varray 和 object ,通常object 居多。

使用嵌套表类型

create table table_name(
 column_name type,
 tab_col_name table_type
)
nested table
 table_col_name
store as
 next_table_name [tablespace user_name];

创建嵌套表的时候要为嵌套表类型另外创建一个表来保存数据, nested 以下的部分就是在干这事。那个表的名称为: next_table_name

tablespace 可以将另外创建表放到另外的空间。

获得表信息

set describe depth 2
desc[ribe] table_name;

也可以直接通过数据字典来获得嵌套表的信息

select *
from user_nested_tables
where table_name = xxxx;

填充、查找嵌套表元素

跟变长数组方法一样

更改嵌套表元素

跟变长数组不同,嵌套表的元素可以单独更改:可以插入、更改和删除嵌套表元素。

插入:

insert into table(
 select tab_col_name from table_name where expr)
 values(
  table_type('xxxx')
 )
);

更改:

update table(
 select tab_col_name from table_name where expr
) t
set
 value(t) = table_type(
  'xxxx')
)
where 
 value(t) = table_type(
  expr2
  );

t 为获得需要修改的那行数据对应的 嵌套表位置,where 为判断语句,如果table_type 为object类型,expr2 这样写: ‘x’,’xx’,’xxx’

删除:

delete from table(
 select tab_col_name from table_name where expr
)t
where
 value(t) = table_type(
  expr2
 );

集合方法

exists(n)

如果第n个元素存在,返回true

count

该函数集合元素的数目

delete

delete(n)

delete(n,m)

删除集合元素

l 删除所有元素

l 删除第n个元素

l 删除n到m的元素

first

返回集合第一个(最小的)元素索引号,如果集合为空,返回null

last

返回集合中最后一个(最大的)元素索引号,如果集合为空,返回null

next(n)

返回集合当前元素的下n元素的索引号,如果它不存在就返回null

prior(n)

返回集合当前元素的前n元素的索引号,如果它不存在就返回null

limit

返回varray中创建元素的最大个数

extend

extend(n)

extend(n,m)

增加集合的大小。

l 添加一个,设为空

l 添加n个,设为空

l 添加n个,设为m

trim

trim(n)

从集合末尾处删除元素

l 删除一个

l 删除n个

调用方法是: tab_col_name.count

嵌套表运算符操作

例如:

var_tab_1 table_type;
var_tab_2 table_type;
var_tab_3 table_type;
reslut boolean;

var_tab_1 :=table_type(‘1′,’2’);
var_tab_2 :=table_type(‘3′,’4’);
var_tab_3 :=table_type(‘2′,’1’);

result:= var_tab_1 =var_tab_3  result 为true;
result:= var_tab_2 <>var_tab_3  result 为true;

in 和 not in 运算符
用于检测一个嵌套表的内容是否出现在令一个嵌套表的内容中。
result:= var_tab_1 in (var_tab_3); result 为 true;
result:= var_tab_2 not in (var_tab_3); result 为 true;

submulitset 子集运算符
检查一个嵌套表的内容是否为另外一个嵌套表的子集
result:= var_tab_1 submulitset of var_tab_3 result 为true;

multiset 集合运算符
返回的是一个嵌套集
multiset union
multiset intersect
multiset except
并 交 差 ,另外还有
all 全部
distinct 去重
var_tab1 := var_tab2 mustiset union all var_tab2

cardinality 获得嵌套表中元素数目
cardinality(var_tab1)
(跟count 有什么不同。。- -!)

member of 运算符
检测嵌套表的一个元素是否存在
‘xxx’ member of var_tab1; 返回boolean

set 运算符
将传入的嵌套表去重后返回
var_tab1 := set (var_tab2);

is a set
判断时候符合每个元素都不同
result:= var_tab1 is a set;

is empty
判断嵌套表是否为空

collect 运算符
将值列表作为嵌套表返回,可以配合 cast 运算符将返回的嵌套表强制转换为一种嵌套表类型。

select collect(column_name)
from table
where expr;

powermultiset

获得嵌套表的子嵌套表

select *
from table(
 powermultiset(table_tpye('1','2'))
);

获得

复制代码 代码如下:

table_type(‘1’)

table_type(‘2’)

table_type(‘1′,’2’)

注意:ps/sql 不支持这个

powermultiset_by_cardinality

获得指定长度以下的嵌套表

select *
from table(
 powermultiset_by_cardinality(table_tpye('1','2'),2)
);

获得

复制代码 代码如下:

table_type(‘1′,’2’)

ps/sql 不支持这个

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐