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值.所谓的空值指的是集合本身是空，不是针对它所拥有的元素
          故在元素引用前需要对其进行初始化

 

三、变长数组示例

[sql]  view plain copy print ?

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  

转载地址：http://blog.csdn.net/robinson_0612/article/details/7387647