oracle 集合

1. 记录(Record)： 由单行多列的标量类型构成的临时记录对象类型

   declare
     type location_record_type is record(
          street_address   varchar2(40),
          postal_code      varchar2(6),
          city             varchar2(10),
          country          char(3) not null :='CHN'
     );
     l_my_loc location_record_type;
   begin
     l_my_loc.street_address := '上海市徐汇区';
     l_my_loc.postal_code := '100003';
     l_my_loc.city := '上海市';
     dbms_output.put_line('我的住址是：'||l_my_loc.street_address||'--'||l_my_loc.city);
   end;

2. PL/SQL表(table): 同种类型的一维、无边界的稀疏集合（稀疏数组就是数组中大部分的内容值都未被使用（或都为零）），只能用于 PL/SQL

   /*
    * PL/SQL表，在PL/SQL中使用可以模仿数组的非永久表
    */
   declare
     type my_text_table_type is table of varchar2(20) index by binary_integer;
     l_text_type my_text_table_type;
   begin
     l_text_type(1) := 'kevin1';
     l_text_type(2) := 'kevin2';
     dbms_output.put_line('数量'||l_text_type.count||'---'||l_text_type(2));
     /*
      * 删除数据
      */
     l_text_type.delete(1);
     dbms_output.put_line(l_text_type(2));
     /*
      * 删除表
     */
     l_text_type.delete;
   end;
   
   /*
    * 遍历表
    */
   declare
     type my_text_table_type is table of varchar2(200) index by binary_integer;
     l_text_table my_text_table_type;
     l_index number;
   begin
    for emp_rec in (select * from emp) loop
      l_text_table(emp_rec.empno) := emp_rec.ename;
    end loop;
    --取得第一个索引
    l_index := l_text_table.first;
    loop
      exit when l_index is null;
      dbms_output.put_line(l_index||':'||l_text_table(l_index));
      --取得下一个索引
      l_index := l_text_table.next(l_index);
    end loop;
   end;
     

3. 变量数组(varray): 同种类型的一维对象集合，它是有界且不稀疏的。在定义 VARRAYs 时要指定它的最大范围。可以在 PL/SQL 和 Database 中被定义，但它其中的元素是有顺序的

   declare
     type my_text_varray_type is varray(2) of varchar2(10);--创建Collection,需要指定最大的范围
     l_text_varray my_text_varray_type:=my_text_varray_type();--创建instance，需要使用够着函数
     l_index number;
   begin
     l_text_varray.extend(2);--赋值前需要分配空间，不能超过最大范围
     l_text_varray(1):='aaa';
     l_text_varray(2):='bbb';
     l_index := l_text_varray.first;
     for var_index in l_index..l_text_varray.count loop
       dbms_output.put_line(l_text_varray(var_index));
       l_index := l_text_varray.next(l_index);
     end loop;
   end;

    
4. 嵌套表（Nested tables）： 同种类型的一维无边界对象集合，可以在 PL/SQL 和 Database（某一列为一个嵌套表）中被定义

   declare
     type my_nestedTable_type is table of varchar2(10);--创建集合，和PL/SQL表相差一个index by 
     l_nestedTable1 my_nestedTable_type:=my_nestedTable_type(); --创建instance，需要使用构造函数
     l_nestedTable2 my_nestedTable_type:=my_nestedTable_type();
     l_nestedTable3 my_nestedTable_type:=my_nestedTable_type();
     begin
       l_nestedTable1.extend(2); --赋值前需要分配空间
       l_nestedTable1(1):='aaa';
       l_nestedTable1(2):='bbb';
       l_nestedTable2.extend;--分配一个空间
       l_nestedTable2(1):='bbb';
       l_nestedTable3:=l_nestedTable1 multiset except l_nestedTable2;
       for l_row in l_nestedTable1.first..l_nestedTable1.last loop
           dbms_output.put_line(l_nestedTable1(l_row));
       end loop;
     end;

    
5. example:

   /**
    * PL/SQL table,nested table,varray in record
    */
   declare
     type my_table is table of varchar2(10) index by binary_integer;
     type my_nestedTable is table of varchar2(20);
     type my_varray is varray(2) of varchar2(10);
     type my_table_in_record is record(
          l_table            my_table,
          l_nestedTable     my_nestedTable:=my_nestedTable(),
          l_varray          my_varray:=my_varray()
     );
     l_record my_table_in_record;
     begin
       --初始化PL/SQL表
       l_record.l_table(1):='table1';
       l_record.l_table(2):='table2';
       --初始化嵌套表
       l_record.l_nestedTable.extend(2);
       l_record.l_nestedTable(1):='nestedTable1';
       l_record.l_nestedTable(2):='nestedTable2';
       --初始化变量数组
       l_record.l_varray.extend(2);
       l_record.l_varray(1):='varray1';
       l_record.l_varray(2):='varray2';
       
       --打印
       for index_var in l_record.l_varray.first..l_record.l_varray.last loop
           dbms_output.put_line(l_record.l_varray(index_var));
       end loop;
     end;
   
   /*
    * record in table
    */
   declare
     type my_record is record(
          name      varchar2(20),
          id        positive,
          gender    integer(1)
     );
     type my_table is table of my_record index by binary_integer;
     l_table my_table;
   begin
     l_table(1).name:='bbb';
     l_table(1).id:=1;
     l_table(1).gender:=1;
     l_table(2).name:='aaa';
     l_table(2).id:=2;
     l_table(2).gender:=0;
     for index_var in l_table.first..l_table.last loop
         dbms_output.put_line(l_table(index_var).name||'--'||l_table(index_var).id||'--'||l_table(index_var).gender);
     end loop;
   end;

    
   
6. Choosing a Collection Type
       1.如果要用稀疏 array，那么只能使用 associative array，虽然可以先分配 nested table 在删除其中的项目，但效率很低
       2.如果在 PL/SQL 中要用负数的下标，只能用 associative array
       3.如果使用 10g，希望使用 set 层面的操作，那么选择 nested tables + MULTISET EXCEPT 的方法
       4.如果要限制存储的行数，使用 VARRAYs
       5.如果要在 column 中存储大数据量集合，那么使用 nested table，Oracle可以使用单独的表来存储它
       6.如果你想将存在 collection column 中的数据保持原有顺序，并且数据量很小，可以使用 VARRAY，小的概念可以按照 BLOCK 的大小来判断，如果数据量超过一个 BLOCK，将会产生行连接