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

 

 

•  

• 【C#从入门到遛弯】第九章 · 面向对象思想

  10-22 1426

• ES6（二）：Promise

   11-21 2831

• 可视化FreeBasic集成开发环境

   02-25 412038

• Android内存泄露与内存溢出

   12-26 7661

• Xmind 8 pro 软件破解版

   07-09 107827

• JAVA的新特性

   11-15 10892

• 教你怎么玩电报（Telegram），telegram入门教程

   02-26 228004

• webgl第33课-透视投影矩阵之不同位置的彩色三角形

   08-18 1798

转

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

2013年08月29日 16:00:24 白天的猫头鹰 阅读数：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)

填充变长数组元素

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

PL/SQL变长数组

1.1万

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

https://blog.csdn.net/zouqingfang/article/details/10526757

 

 

 

PL/SQL变长数组

2012年03月23日 15:47:29 Leshami 阅读数：10676 标签： inserttableinteger扩展nulloracle 更多

个人分类： -----SQL/PLSQL基础

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

版权声明：本文为博主原创文章，欢迎扩散，扩散请务必注明出处。 https://blog.csdn.net/robinson_0612/article/details/7387647

  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

四、更多参考

启用用户进程跟踪

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

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

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

PL/SQL 联合数组与嵌套表

https://blog.csdn.net/leshami/article/details/7387647