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

末蓝、 2022-04-02 11:26 465阅读 0赞

您的位置：[首页][Link 1] → [数据库][Link 2] → [oracle][] → 正文内容 oracle 变长数组varray,嵌套表

微信[投稿][Link 3][交流社区][Link 4][在线工具][Link 5]

关注微信公众号

![扫一扫][20181226150720926]

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

更新时间：2016年02月14日 12:20:53   投稿：mdxy-dxy   [![20181226150720947][] 我要评论][20181226150720947_]

这篇文章主要介绍了oracle中变长数组varray,嵌套表,集合使用方法,需要的朋友可以参考下

[![20181226150720967][]][20181226150720967 1]

**创建变长数组类型**

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>CREATE</code> <code>TYPE varray_type </code><code>AS</code> <code>VARRAY(2) </code><code>OF</code> <code>VARCHAR2(50);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

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

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p><code>ALTER</code> <code>TYPE varray_type </code></p> <p><code>MODIFY</code> <code>ELEMENT TYPE varchar2(100) </code><code>CASCADE</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

**增加变长数组的元素数目**

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p><code>ALTER</code> <code>TYPE vrray_name</code></p> <p><code>MODIFY</code> <code>LIMIT 5 </code><code>CASCADE</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**使用变长数组**

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>CREATE</code> <code>TABLE</code> <code>table_name(</code></p> <p><code>column_name type,</code></p> <p><code>var_col_name varray_type</code></p> <p><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**获得变长数组的信息**

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> </td> 
   <td> <p><code>DESC</code><code>[RIBE] varray_type;</code></p> <p>&nbsp;</p> <p><code>SELECT</code> <code>* </code></p> <p><code>FROM</code> <code>user_varrays</code></p> <p><code>WHERE</code> <code>type_name = varray_name;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

DESC 获得的是 varray\_type AS VARRAY(2) OF VARCHAR2(50)

**填充变长数组元素**

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> <p>7</p> </td> 
   <td> <p><code>INSERT</code> <code>INTO</code> <code>table_name </code><code>VALUES</code><code>(</code></p> <p><code>&nbsp;</code><code>value,</code></p> <p><code>&nbsp;</code><code>varray_type(</code></p> <p><code>&nbsp;&nbsp;</code><code>'xxxx'</code><code>,</code></p> <p><code>&nbsp;&nbsp;</code><code>'xx'</code><code>,</code></p> <p><code>&nbsp;&nbsp;</code><code>'x'</code><code>)</code></p> <p><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

**查找变长数组元素**

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p><code>SELECT</code> <code>*</code></p> <p><code>FROM</code> <code>table_Name;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

**更改变长数组元素**

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

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>UPDATE</code> <code>table_name</code></p> <p><code>SET</code> <code>var_col_name = varray_type(</code><code>'xxx'</code><code>,</code><code>'xxxxxx'</code><code>)</code></p> <p><code>WHERE</code> <code>expr1;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

创建嵌套表类型

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>CREATE</code> <code>TYPE table_type </code><code>AS</code> <code>TABLE</code> <code>OF</code> <code>type;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

**使用嵌套表类型**

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> <p>7</p> <p>8</p> </td> 
   <td> <p><code>CREATE</code> <code>TABLE</code> <code>table_name(</code></p> <p><code>&nbsp;</code><code>column_name type,</code></p> <p><code>&nbsp;</code><code>tab_col_name table_type</code></p> <p><code>)</code></p> <p><code>NESTED </code><code>TABLE</code></p> <p><code>&nbsp;</code><code>table_col_name</code></p> <p><code>STORE </code><code>AS</code></p> <p><code>&nbsp;</code><code>next_table_name [TABLESPACE user_name];</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

**获得表信息**

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p><code>SET</code> <code>DESCRIBE DEPTH 2</code></p> <p><code>DESC</code><code>[RIBE] table_name;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>SELECT</code> <code>*</code></p> <p><code>FROM</code> <code>user_nested_tables</code></p> <p><code>WHERE</code> <code>table_name = xxxx;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**填充、查找嵌套表元素**

跟变长数组方法一样

**更改嵌套表元素**

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

**插入：**

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> </td> 
   <td> <p><code>INSERT</code> <code>INTO</code> <code>TABLE</code><code>(</code></p> <p><code>&nbsp;</code><code>SELECT</code> <code>tab_col_name </code><code>FROM</code> <code>table_name </code><code>WHERE</code> <code>expr)</code></p> <p><code>&nbsp;</code><code>VALUES</code><code>(</code></p> <p><code>&nbsp;&nbsp;</code><code>table_type(</code><code>'xxxx'</code><code>)</code></p> <p><code>&nbsp;</code><code>)</code></p> <p><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**更改：**

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> <p>7</p> <p>8</p> <p>9</p> <p>10</p> <p>11</p> </td> 
   <td> <p><code>UPDATE</code> <code>TABLE</code><code>(</code></p> <p><code>&nbsp;</code><code>SELECT</code> <code>tab_col_name </code><code>FROM</code> <code>table_name </code><code>WHERE</code> <code>expr</code></p> <p><code>) T</code></p> <p><code>SET</code></p> <p><code>&nbsp;</code><code>VALUE(T) = table_type(</code></p> <p><code>&nbsp;&nbsp;</code><code>'xxxx'</code><code>)</code></p> <p><code>)</code></p> <p><code>WHERE</code></p> <p><code>&nbsp;</code><code>VALUE(T) = table_type(</code></p> <p><code>&nbsp;&nbsp;</code><code>expr2</code></p> <p><code>&nbsp;&nbsp;</code><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

**删除:**

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> <p>7</p> </td> 
   <td> <p><code>DELETE</code> <code>FROM</code> <code>TABLE</code><code>(</code></p> <p><code>&nbsp;</code><code>SELECT</code> <code>tab_col_name </code><code>FROM</code> <code>table_name </code><code>WHERE</code> <code>expr</code></p> <p><code>)T</code></p> <p><code>WHERE</code></p> <p><code>&nbsp;</code><code>VALUE(T) = table_type(</code></p> <p><code>&nbsp;&nbsp;</code><code>expr2</code></p> <p><code>&nbsp;</code><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**集合方法**

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p>EXISTS(N)</p> </td> 
   <td style="vertical-align:top;"> <p>如果第n个元素存在，返回TRUE</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;"> <p>COUNT</p> </td> 
   <td style="vertical-align:top;"> <p>该函数集合元素的数目</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;"> <p>DELETE</p> <p>DELETE(n)</p> <p>DELETE(n,m)</p> </td> 
   <td style="vertical-align:top;"> <p>删除集合元素</p> <p>l 删除所有元素</p> <p>l 删除第n个元素</p> <p>l 删除n到m的元素</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;"> <p>FIRST</p> </td> 
   <td style="vertical-align:top;"> <p>返回集合第一个（最小的）元素索引号，如果集合为空，返回NULL</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;"> <p>LAST</p> </td> 
   <td style="vertical-align:top;"> <p>返回集合中最后一个（最大的）元素索引号，如果集合为空，返回NULL</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;"> <p>NEXT(n)</p> </td> 
   <td style="vertical-align:top;"> <p>返回集合当前元素的下n元素的索引号，如果它不存在就返回NULL</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;"> <p>PRIOR(n)</p> </td> 
   <td style="vertical-align:top;"> <p>返回集合当前元素的前n元素的索引号，如果它不存在就返回NULL</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;"> <p>LIMIT</p> </td> 
   <td style="vertical-align:top;"> <p>返回varray中创建元素的最大个数</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;"> <p>EXTEND</p> <p>EXTEND(n)</p> <p>EXTEND(n,m)</p> </td> 
   <td style="vertical-align:top;"> <p>增加集合的大小。</p> <p>l 添加一个，设为空</p> <p>l 添加n个，设为空</p> <p>l 添加n个，设为m</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;"> <p>TRIM</p> <p>TRIM(n)</p> </td> 
   <td style="vertical-align:top;"> <p>从集合末尾处删除元素</p> <p>l 删除一个</p> <p>l 删除n个</p> </td> 
  </tr> 
 </tbody> 
</table>

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

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>SELECT</code> <code>COLLECT(column_name)</code></p> <p><code>FROM</code> <code>TABLE</code></p> <p><code>WHERE</code> <code>expr;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**POWERMULTISET**

获得嵌套表的子嵌套表

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>SELECT</code> <code>*</code></p> <p><code>FROM</code> <code>TABLE</code><code>(</code></p> <p><code>&nbsp;</code><code>POWERMULTISET(table_tpye(</code><code>'1'</code><code>,</code><code>'2'</code><code>))</code></p> <p><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

获得

复制代码 代码如下:

table\_type('1')  
table\_type('2')  
table\_type('1','2')

注意：PS/SQL 不支持这个

**POWERMULTISET\_BY\_CARDINALITY**

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

[?][Link 6]

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>SELECT</code> <code>*</code></p> <p><code>FROM</code> <code>TABLE</code><code>(</code></p> <p><code>&nbsp;</code><code>POWERMULTISET_BY_CARDINALITY(table_tpye(</code><code>'1'</code><code>,</code><code>'2'</code><code>),2)</code></p> <p><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

获得

复制代码 代码如下:

table\_type('1','2')

PS/SQL 不支持这个

您可能感兴趣的文章:

*  [Oracle 中 table 函数的应用浅析][Oracle _ table]
 *  [Oracle Table Demo语句应用介绍][Oracle Table Demo]
 *  [sql – Oracle中匿名TABLE/VARRAY类型示例详解][sql _ Oracle_TABLE_VARRAY]

[https://www.jb51.net/article/79399.htm][https_www.jb51.net_article_79399.htm]

[Link 1]: https://www.jb51.net/
[Link 2]: https://www.jb51.net/list/index_104.htm
[oracle]: https://www.jb51.net/list/list_154_1.htm
[Link 3]: https://tougao.jb51.net/
[Link 4]: http://shequ.jb51.net/
[Link 5]: http://tools.jb51.net/
[20181226150720926]: /images/20220402/a48c7e6caf96468991af7f2ecd9d578a.png
[20181226150720947]: /images/20220402/92479cb3e495421baf57f601c5f857c0.png
[20181226150720947_]: https://www.jb51.net/article/79399.htm#comments
[20181226150720967]: /images/20220402/8beb21dfe07e46e68cc7d1a44a62687d.png
[20181226150720967 1]: http://www.php.cn/course.html?ad51
[Link 6]: https://www.jb51.net/article/79399.htm#
[Oracle _ table]: https://www.jb51.net/article/101587.htm
[Oracle Table Demo]: https://www.jb51.net/article/32084.htm
[sql _ Oracle_TABLE_VARRAY]: https://www.jb51.net/article/148863.htm
[https_www.jb51.net_article_79399.htm]: https://www.jb51.net/article/79399.htm