Oracle:变长数组varray 嵌套表集合

深碍√TFBOYSˉ_ 2022-04-03 06:36 255阅读 0赞

*   
 *  [【C\#从入门到遛弯】第九章 · 面向对象思想][C_ _]
    
    [10-22 1426][C_ _]
 *  [ES6（二）：Promise][ES6_Promise]
    
    [ 11-21 2831][ES6_Promise]
 *  [可视化FreeBasic集成开发环境][FreeBasic]
    
    [ 02-25 412038][FreeBasic]
 *  [Android内存泄露与内存溢出][Android]
    
    [ 12-26 7661][Android]
 *  [Xmind 8 pro 软件破解版][Xmind 8 pro]
    
    [ 07-09 107827][Xmind 8 pro]
 *  [JAVA的新特性][JAVA]
    
    [ 11-15 10892][JAVA]
 *  [教你怎么玩电报（Telegram），telegram入门教程][Telegram_telegram]
    
    [ 02-26 228004][Telegram_telegram]
 *  [webgl第33课-透视投影矩阵之不同位置的彩色三角形][webgl_33_-]
    
    [ 08-18 1798][webgl_33_-]

转

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

2013年08月29日 16:00:24 [白天的猫头鹰][Link 1] 阅读数：1045

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

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)

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

![复制代码][20181220202541235]

INSERT INTO table_name VALUES(
      value,
      varray_type(
        'xxxx',
        'xx',
        'x')
    );

![复制代码][20181220202541235]

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

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

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 居多。

** 使用嵌套表类型**

![复制代码][20181220202541235]

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];

![复制代码][20181220202541235]

创建嵌套表的时候要为嵌套表类型另外创建一个表来保存数据， 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')
      )
    );

**更改**：

![复制代码][20181220202541235]

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

![复制代码][20181220202541235]

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

**删除**:

![复制代码][20181220202541235]

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

![复制代码][20181220202541235]

**集合方法**

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

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 不支持这个**

[PL/SQL*变长**数组*][PL_SQL]

[1.1万][PL_SQL]

[PL/SQL变长数组时PL/SQL集合数据类型中的一种，其使用方法与PL/SQL嵌套表大同小异，唯一的区别则是变长数组的元素的最大个数是有限制的。也即是说变长数组的下标固定下限等于1，上限可以扩展。下...][PL_SQL][来自： robinson\_0612][robinson_0612]

[https://blog.csdn.net/zouqingfang/article/details/10526757][https_blog.csdn.net_zouqingfang_article_details_10526757]

# PL/SQL变长数组 #

2012年03月23日 15:47:29 [Leshami][] 阅读数：10676 标签： [insert][][table][][integer][][扩展][Link 2][null][][oracle][] 更多

个人分类： [\-----SQL/PLSQL基础][-----SQL_PLSQL]

所属专栏： [SQL PL/SQL基础系列][SQL PL_SQL]

PL/SQL变长数组时PL/SQL集合数据类型中的一种，其使用方法与PL/SQL嵌套表大同小异，唯一的区别则是变长数组的元素的最大个数是有限  
制的。也即是说变长数组的下标固定下限等于1，上限可以扩展。下面给出具体的描述及其使用方法。

一、变长数组语法  
        TYPE type\_name IS \{VARRAY | VARYING ARRAY\} (size\_limit) OF  -->type\_name 用于指定varray类型名,size\_limit 定义varray元素的最大个数  
        element\_type \[NOT NULL\];                                    -->element\_type用于指定元素的数据类型    
        varray\_name TYPE\_NAME;                                      -->varray\_name 用于定义varray变量

二、变长数组特性  
          变长数组主要的特性即是元素的最大个数是有限制  
          变长数组下标固定为1,上限可以扩展  
          与嵌套表类似,在变长数组声明时自动设置为NULL值.所谓的空值指的是集合本身是空，不是针对它所拥有的元素  
          故在元素引用前需要对其进行初始化

三、变长数组示例

1. `--1、声明变长数组，并输出其结果`
2. `scott@CNMMBO> DECLARE`
3. `2 CURSOR name_cur IS`
4. `3 SELECT dname`
5. `4 FROM dept`
6. `5 WHERE deptno < 40;`
7. `6`
8. `7 TYPE name_type IS VARRAY( 10 ) OF dept.dname%TYPE; -->声明一个包含10个元素的变长数组，且且数据类型为dept.dname类型`
9. `8`
10. `9 varray_dname_tab name_type := name_type( ); -->初始化变长数组`
11. `10 v_counter INTEGER := 0;`
12. `11 BEGIN`
13. `12 FOR name_rec IN name_cur`
14. `13 LOOP`
15. `14 v_counter :=`
16. `15 v_counter`
17. `16 + 1;`
18. `17 varray_dname_tab.EXTEND; -->使用extend进行扩展`
19. `18 varray_dname_tab( v_counter ) := name_rec.dname; -->按下标输出变长数组的所有元素`
20. `19 DBMS_OUTPUT.put_line( 'Dname ('`
21. `20 || v_counter`
22. `21 || ') is :'`
23. `22 || varray_dname_tab( v_counter ) );`
24. `23 END LOOP;`
25. `24 END;`
26. `25 /`
27. `Dname (1) is :ACCOUNTING`
28. `Dname (2) is :RESEARCH`
29. `Dname (3) is :SALES`
30.  
31. `--2、对变长数组直接赋初值，且使用count遍历并输出所有元素`
32. `scott@CNMMBO> DECLARE`
33. `2 TYPE name_type IS VARRAY( 2 ) OF VARCHAR2( 10 );`
34. `3`
35. `4 varray_name_tab name_type := name_type( 'Robinson', 'Jackson' ); -->此处对varray_name_tab初始化并赋初值`
36. `5 BEGIN`
37. `6 FOR i IN 1 .. varray_name_tab.COUNT -->使用count来遍历并输出变长数组的所有元素`
38. `7 LOOP`
39. `8 DBMS_OUTPUT.put_line( 'Name varray_name_tab( '`
40. `9 || i`
41. `10 || ' ) is : '`
42. `11 || varray_name_tab( i ) );`
43. `12 END LOOP;`
44. `13 END;`
45. `14 /`
46. `Name varray_name_tab( 1 ) is : Robinson`
47. `Name varray_name_tab( 2 ) is : Jackson`
48.  
49. `PL/SQL procedure successfully completed.`
50.  
51. `--3、超出变长数组大小的情形`
52. `scott@CNMMBO> DECLARE`
53. `2 TYPE name_type IS VARRAY( 2 ) OF VARCHAR2( 10 );`
54. `3`
55. `4 varray_name_tab name_type := name_type( 'Robinson', 'Jackson' );`
56. `5 BEGIN`
57. `6 FOR i IN 1 .. varray_name_tab.COUNT`
58. `7 LOOP`
59. `8 DBMS_OUTPUT.put_line( 'Name varray_name_tab( '`
60. `9 || i`
61. `10 || ' ) is : '`
62. `11 || varray_name_tab( i ) );`
63. `12 END LOOP;`
64. `13`
65. `14 varray_name_tab.EXTEND;`
66. `15 varray_name_tab( 3 ) := 'Johnson';`
67. `16 DBMS_OUTPUT.put_line( 'Name varray_name_tab (3) is '`
68. `17 || varray_name_tab( 3 ) );`
69. `18 END;`
70. `19 /`
71. `Name varray_name_tab( 1 ) is : Robinson`
72. `Name varray_name_tab( 2 ) is : Jackson`
73. `DECLARE`
74. `*`
75. `ERROR at line 1:`
76. `ORA-06532: Subscript outside of limit`
77. `ORA-06512: at line 14`
78.  
79. `--4、存储变长数组到数据库及修改变长数组`
80. `scott@CNMMBO> CREATE OR REPLACE TYPE varray_phone IS VARRAY( 2 ) OF VARCHAR2( 40 ); -->创建变长数组类型`
81. `2 /`
82.  
83. `Type created.`
84.  
85. `scott@CNMMBO> CREATE TABLE tb_emp -->创建表tb_emp且其中一列使用到了变长数组`
86. `2 (`
87. `3 empno NUMBER( 4 )`
88. `4 , ename VARCHAR2( 10 )`
89. `5 , phone varray_phone -->列phone使用到了变长数组`
90. `6 )`
91. `7 ;`
92.  
93. `Table created.`
94.  
95. `--插入新记录到变长数组`
96. `scott@CNMMBO> insert into tb_emp select 6666,'Robinson',varray_phone('13423456789','075520123650') from dual;`
97.  
98. `1 row created.`
99.  
100. `scott@CNMMBO> insert into tb_emp select 7777,'Jackson',varray_phone('13423456789','075520123650') from dual;`
101.  
102. `1 row created.`
103.  
104. `scott@CNMMBO> commit;`
105.  
106. `Commit complete.`
107.  
108. `scott@CNMMBO> col phone format a25`
109. `scott@CNMMBO> select * from tb_emp; -->查看插入的记录`
110.  
111. `EMPNO ENAME PHONE`
112. `---------- ---------- -------------------------`
113. `6666 Robinson VARRAY_PHONE('13423456789`
114. `', '075520123650')`
115.  
116. `7777 Jackson VARRAY_PHONE('13423456789`
117. `', '075520123650')`
118.  
119. `-->插入变长数组包含3个元素的记录，此时提示超出了变长数组范围`
120. `scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('13423456789','075520123650','010123')`
121. `2 from dual;`
122. `insert into tb_emp select 8888,'Johnson',varray_phone('13423456789','075520123650','010123')`
123. `*`
124. `ERROR at line 1:`
125. `ORA-22909: exceeded maximum VARRAY limit`
126.  
127. `-->修改变长数组长度范围限制到3，且使用cascade，即及联修改到表tb_emp`
128. `scott@CNMMBO> alter type varray_phone modify limit 3 cascade;`
129.  
130. `Type altered.`
131.  
132. `-->再次插入数据成功`
133. `scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('13423456789','075520123650','010123')`
134. `2 from dual;`
135.  
136. `1 row created.`
137.  
138. `-->插入变长数组元素超出预定义字符串长度时收到错误提示`
139. `scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('13423456789000000000000000000000000000000000000')`
140. `2 from dual;`
141. `insert into tb_emp select 8888,'Johnson',varray_phone('13423456789000000000000000000000000000000000000')`
142. `*`
143. `ERROR at line 1:`
144. `ORA-22814: attribute or element value is larger than specified in type`
145.  
146. `-->修改变长数组元素的数据长度到varchar2(60),且使用cascade，即及联修改到表tb_emp`
147. `scott@CNMMBO> alter type varray_phone modify element type varchar2(60) cascade;`
148.  
149. `Type altered.`
150.  
151. `-->再次插入数据成功`
152. `scott@CNMMBO> insert into tb_emp select 8888,'Johnson',varray_phone('13423456789000000000000000000000000000000000000')`
153. `2 from dual;`
154.  
155. `1 row created.`
156.  
157. `-->查看变长数组的定义`
158. `scott@CNMMBO> select parent_table_name,parent_table_column,type_name from user_varrays;`
159.  
160. `PARENT_TABLE_NA PARENT_TABLE_COLUMN TYPE_NAME`
161. `--------------- ------------------------------ ------------------------------`
162. `TB_EMP PHONE VARRAY_PHONE`

四、更多参考

[启用用户进程跟踪][Link 3]

[父游标、子游标及共享游标][Link 4]

[绑定变量及其优缺点][Link 5]

[dbms\_xplan之display\_cursor函数的使用][dbms_xplan_display_cursor]

[dbms\_xplan之display函数的使用][dbms_xplan_display]

[执行计划中各字段各模块描述][Link 6]

[使用 EXPLAIN PLAN 获取SQL语句执行计划][EXPLAIN PLAN _SQL]

[启用 AUTOTRACE 功能][AUTOTRACE]

[函数使得索引列失效][Link 7]

[Oracle 绑定变量窥探][Oracle]

[PL/SQL 联合数组与嵌套表][PL_SQL 1]

[https://blog.csdn.net/leshami/article/details/7387647][https_blog.csdn.net_leshami_article_details_7387647]

[C_ _]: https://blog.csdn.net/iceSony/article/details/52894495
[ES6_Promise]: https://blog.csdn.net/caomage/article/details/84319034
[FreeBasic]: https://blog.csdn.net/yfvb2010/article/details/79369125
[Android]: https://blog.csdn.net/zping0808/article/details/53889115
[Xmind 8 pro]: https://blog.csdn.net/qq_16093323/article/details/80967867
[JAVA]: https://blog.csdn.net/weixin_43694144/article/details/84099515
[Telegram_telegram]: https://blog.csdn.net/tiandiwuya/article/details/79376589
[webgl_33_-]: https://blog.csdn.net/weixin_39452320/article/details/81809245
[Link 1]: https://me.csdn.net/zouqingfang
[20181220202541235]: /images/20220403/cb637698d8264eac96b66db7aa6811c6.png
[PL_SQL]: https://blog.csdn.net/robinson_0612/article/details/7387647
[robinson_0612]: https://blog.csdn.net/robinson_0612
[https_blog.csdn.net_zouqingfang_article_details_10526757]: https://blog.csdn.net/zouqingfang/article/details/10526757
[Leshami]: https://me.csdn.net/robinson_0612
[insert]: http://so.csdn.net/so/search/s.do?q=insert&t=blog
[table]: http://so.csdn.net/so/search/s.do?q=table&t=blog
[integer]: http://so.csdn.net/so/search/s.do?q=integer&t=blog
[Link 2]: http://so.csdn.net/so/search/s.do?q=%E6%89%A9%E5%B1%95&t=blog
[null]: http://so.csdn.net/so/search/s.do?q=null&t=blog
[oracle]: http://so.csdn.net/so/search/s.do?q=oracle&t=blog
[-----SQL_PLSQL]: https://blog.csdn.net/robinson_0612/article/category/675340
[SQL PL_SQL]: https://blog.csdn.net/column/details/robinson.html
[Link 3]: http://blog.csdn.net/robinson_0612/archive/2010/10/07/5925564.aspx
[Link 4]: http://blog.csdn.net/robinson_0612/article/details/6893477
[Link 5]: http://blog.csdn.net/robinson_0612/article/details/6904229
[dbms_xplan_display_cursor]: http://blog.csdn.net/robinson_0612/article/details/6866925
[dbms_xplan_display]: http://blog.csdn.net/robinson_0612/article/details/6866870
[Link 6]: http://blog.csdn.net/robinson_0612/article/details/6860007
[EXPLAIN PLAN _SQL]: http://blog.csdn.net/robinson_0612/article/details/6837771
[AUTOTRACE]: http://blog.csdn.net/robinson_0612/article/details/6030407
[Link 7]: http://blog.csdn.net/robinson_0612/article/details/6851973
[Oracle]: http://blog.csdn.net/robinson_0612/article/details/6923627
[PL_SQL 1]: http://blog.csdn.net/robinson_0612/article/details/7372061
[https_blog.csdn.net_leshami_article_details_7387647]: https://blog.csdn.net/leshami/article/details/7387647