oracle:变长数组varray,嵌套表,集合

CREATE TYPE varray_type AS VARRAY( 2 ) OF VARCHAR2 ( 50 ); 这个变长数组最多可以容纳两个数据，数据的类型为 varchar2(50) 更改元素类型的大小或精度 可以更改 变长数组类型和嵌套表类型 元素的大

<span>CREATE</span> TYPE varray_type <span>AS</span> VARRAY(<span>2</span>) <span>OF</span> <span>VARCHAR2</span>(<span>50</span>); 

 

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

 

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

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

<span>ALTER</span><span> TYPE varray_type 
MODIFY ELEMENT TYPE </span><span>varchar2</span>(<span>100</span>) <span>CASCADE</span>;

 

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

增加变长数组的元素数目

<span>ALTER</span><span> TYPE vrray_name
MODIFY LIMIT </span><span>5</span> <span>CASCADE</span>;

使用变长数组

<span>CREATE</span> <span>TABLE</span><span> table_name(
column_name type,
var_col_name varray_type
);</span>

 

 获得变长数组的信息

<span>DESC</span><span>[</span><span>RIBE</span><span>]</span><span> varray_type;

</span><span>SELECT</span> <span>*</span> 
<span>FROM</span><span> user_varrays
</span><span>WHERE</span> type_name <span>=</span> varray_name;

 DESC 获得的是 varray_type AS VARRAY(2) OF VARCHAR2(50)

填充变长数组元素

<span>INSERT</span> <span>INTO</span> table_name <span>VALUES</span><span>(
  value,
  varray_type(
    </span><span>'</span><span>xxxx</span><span>'</span><span>,
    </span><span>'</span><span>xx</span><span>'</span><span>,
    </span><span>'</span><span>x</span><span>'</span><span>)
);</span>

 

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

查找变长数组元素

<span>SELECT</span> <span>*</span>
<span>FROM</span> table_Name;

 

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

 更改变长数组元素

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

<span>UPDATE</span><span> table_name
</span><span>SET</span> var_col_name <span>=</span> varray_type(<span>'</span><span>xxx</span><span>'</span>,<span>'</span><span>xxxxxx</span><span>'</span>)
WHERE expr1;

 

 

创建嵌套表类型

<span>CREATE</span> TYPE table_type <span>AS</span> <span>TABLE</span> <span>OF</span> type;

 

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

 使用嵌套表类型

<span>CREATE</span> <span>TABLE</span><span> table_name(
  column_name type,
  tab_col_name table_type
)
NESTED </span><span>TABLE</span><span>
  table_col_name
STORE </span><span>AS</span><span>
  next_table_name [TABLESPACE user_name];</span>

 

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

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

 获得表信息

<span>SET</span> DESCRIBE DEPTH <span>2</span>
<span>DESC</span><span>[</span><span>RIBE</span><span>]</span> table_name;

 

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

<span>SELECT</span> <span>*</span>
<span>FROM</span><span> user_nested_tables
</span><span>WHERE</span> table_name <span>=</span> xxxx;

 

填充、查找嵌套表元素

跟变长数组方法一样

更改嵌套表元素

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

插入：

<span>INSERT</span> <span>INTO</span> <span>TABLE</span><span>(
  </span><span>SELECT</span> tab_col_name <span>FROM</span> table_name <span>WHERE</span><span> expr)
  </span><span>VALUES</span><span>(
    table_type(</span><span>'xx</span><span>xx</span><span>'</span><span>)
  )
);
</span>

 

 更改：

<span>UPDATE</span> <span>TABLE</span><span>(
  </span><span>SELECT</span> tab_col_name <span>FROM</span> table_name <span>WHERE</span><span> expr
) T
</span><span>SET</span><span>
  VALUE(T)  </span><span>=</span><span> table_type(
    </span><span>'</span><span>xxxx</span><span>'</span><span>)
)
</span><span>WHERE</span><span> 
  VALUE(T) </span><span>=</span><span> table_type(
    expr2
   );</span>

 

 T 为获得需要修改的那行数据对应的 嵌套表位置，WHERE 为判断语句，如果table_type 为object类型，expr2 这样写： 'x','xx','xxx'

删除:

<span>DELETE</span> <span>FROM</span> <span>TABLE</span><span>(
  </span><span>SELECT</span> tab_col_name <span>FROM</span> table_name <span>WHERE</span><span> expr
)T
</span><span>WHERE</span><span>
  VALUE(T) </span><span>=</span><span> table_type(
    expr2
  );</span>

 

 

集合方法

 

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 运算符将返回的嵌套表强制转换为一种嵌套表类型。

<span>SELECT</span><span> COLLECT(column_name)
</span><span>FROM</span> <span>TABLE</span>
<span>WHERE</span> expr;

 

 

POWERMULTISET 

获得嵌套表的子嵌套表

<span>SELECT</span> <span>*</span>
<span>FROM</span> <span>TABLE</span><span>(
  POWERMULTISET(table_tpye(</span><span>'</span><span>1</span><span>'</span>,<span>'</span><span>2</span><span>'</span><span>))
);</span>

 

获得

table_type(<span>'</span><span>1</span><span>'</span><span>)
table_type(</span><span>'</span><span>2</span><span>'</span><span>)
table_type(</span><span>'</span><span>1</span><span>'</span>,<span>'</span><span>2</span><span>'</span>)

 

注意：PS/SQL 不支持这个

 

POWERMULTISET_BY_CARDINALITY 

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

<span>SELECT</span> <span>*</span>
<span>FROM</span> <span>TABLE</span><span>(
  POWERMULTISET_BY_CARDINALITY(table_tpye(</span><span>'</span><span>1</span><span>'</span>,<span>'</span><span>2</span><span>'</span>),<span>2</span><span>)
);</span>

 

获得

table_type(<span>'</span><span>1</span><span>'</span>,<span>'</span><span>2</span><span>'</span>)

 

 

PS/SQL 不支持这个