oracle集合数据类型的使用

oracle数据库中集合数据类型包括：关联数组Associative array（索引表 pl/sql table）、嵌套表（Nested Table）、变长数组（VARRAY）

1. 区别

Nested table与VARRY既可以被用于PL/SQL，也可以被直接用于数据库中，但是Associative array不行，也就是说，Associative array是不能通过CREATE TYPE语句进行单独创建，
只能在PL/SQL块(或Package)中进行定义并使用(即适用范围是PL/SQL Block级别)，而Nested table与VARRAY则可以使用CREATE TYPE进行创建(即适用范围是Schema级别)，
它们还可以直接作为数据库表中列的类型。

2. 集合的方法

 exists(index) 索引处的元素是否存在  
       count 当前集合中的元素总个数  
       limit 集合元素索引的最大值(索引表和嵌套表是不限个数的，所以返回null，变长数组返回定义时的最大索引)  
       first  返回集合第一个元素索引  
       last  返回集合最后一个元素索引  
       prior 当前元素的前一个  
       next 当前元素的后一个  
      
       extend 扩展集合的容量，增加元素 只是用于嵌套表和varry类型  
           x.extend 增加一个null元素  
           x.extend(n) 增加n个null元素  
           x.extend(n,i) 增加n个元素，元素值与第i个元素相同  
       trim 从集合的尾部删除元素 只用于NEST TABLE和VARRY类型  
       trim 从集合尾部删除一个元素  
       trim(n) 从集合尾部删除n个元素  
       delete 按索引删除集合元素  
           delete 删除所有  
           delete(index) 删除第index个  
           delete(a,b) 删除a--b之间的所有元素 

3. 集合类型的声明与初始化

   • 关联数组:

     1. 下标无限制，可以为负数  
     2. 元素个数无限制  
     3. 定义  
           TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type;  
           type_name：用户自定义数据类型的名字  
           element_type：索引表中元素类型  
           key_type：索引表元素下标的数据类型（BINARY_INTEGER，PLS_INTEGER,VARCHAR2）
     

     例：

       declare  
         type index_tab_type is table of varchar2(30) index by BINARY_INTEGER;  
         v_table index_tab_type;  
       begin  
         v_table(-1) :='hello';--设定下标为-1的元素的值  
         v_table(1)  :=',';  
         dbms_output.put_line(v_table(-1)||'-'||v_table(1));  
         dbms_output.put_line('元素个数：'||v_table.count);  
         v_table(5) :='world';  
         dbms_output.put_line('元素个数：'||v_table.count);  
         dbms_output.put_line('第一个元素'||v_table.first);  
         dbms_output.put_line('最后一个元素'||v_table.last);  
       end;  
       /  
     

   • 嵌套表 Nested Table

     1. 下标从1开始，元素个数没有限制(使用时必须先初始化，用extend属性可以扩展元素个数)  
     2. 可以作为表定义数据类型，但是前提是要先create 创造嵌套表类型，这就可以实现1对多了定义   TYPE type_name IS TABLE OF element_type;  
     3. 和索引表的区别也就是看看有无index by语句，嵌套表的索引固定是int型的 
     

     例：

       declare  
            type nest_table_type is table of emp.ename%type;  
            v_nest_tab nest_table_type;  
       begin  
            v_nest_tab :=nest_table_type('x');--初始化 必须！ 语句 type_name(...)  
            select ename into v_nest_tab(1) from emp where empno=7788;  
            dbms_output.put_line(v_nest_tab(1));  
       end;  
     

     例：在表列中使用嵌套表 嵌套表类型的列是单独一个表存储，先创建一个这样的类型才能使用

     create type nest_tab_type is table of varchar2(30);     
     create table test_nest_tab(    
        id int,    
        vals nest_tab_type --使用    
     ) nested table vals store as nest_tab;--vals字段用嵌套表存储，表明nest_tab    
     --上面语句执行完之后，在生成TEST_NEST_TAB的同时会生出一个关联表NEST_TAB用来存储关联表的数据    
     --插入数据    
     insert into test_nest_tab values(1,nest_tab_type('one','two','three','four'));    
     --查询数据    
     declare     
       v_id int;    
       v_tab nest_tab_type;    
     begin    
       select * into v_id,v_tab from test_nest_tab where id=1;    
       dbms_output.put_line(v_id);    
       for i in 1..v_tab.count loop    
       dbms_output.put_line(v_tab(i));    
       end loop;    
     end;    
     

   • Varry 可变数组

     1. 这个就和java中的数组差不多了，下标from 1 ，定义时先指定最大元素个数
     2. 定义 TYPE type_name IS VARRAY(size_limit) OF element_type[NOT NULL];  
     

     例：

     declare  
       type varr is VARRAY(10) of int;  
       v_varr varr :=varr();  
     begin  
     --dbms_output.put_line(varr.count);  
     for i in 1..5 loop  
       v_varr.extend;  
       v_varr(i) :=i*i;  
     end loop;  
     
     for i in 1..5 loop  
       dbms_output.put_line(v_varr(i));  
       end loop;  
     end;  
     

4. 如何选择适用的集合类型

通常来说，对集合类型的第一选择应该是Associative array，因为它不需要初始化或者EXTEND操作，并且是迄今为止最高效的集合类型。唯一不足的一点是它只能用于PL/SQL而不能直接用于数据库。

如果你需要允许使用负数索引，应该选择Associative array;

如果你需要使用10g，11g中的那些集合操作，应该选择Nested table;

如果你需要限制集合元素的个数，应该选择VARRAY。

原文地址：Oracle 集合类型

https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm oracle 11g plsql 开发官方文档
https://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm oracle 11g sql语法开发官方文档
https://docs.oracle.com/cd/E11882_01/server.112/e10897/toc.htm oracle 11g dba官方入门文档