/------------------------------------------------------------------------------------------------------------------------------------/
/--------------------------------------------------------     pl/sql集合     --------------------------------------------------------/
/------------------------------------------------------------------------------------------------------------------------------------/
                      
                        
                        
pl/sql集合
  它是为了处理多行单列数据
 
  pl/sql集合类型包括：
                       索引表 pl/sql表
                       嵌套表nested table
                       变长数组varray
                      
                      
                       索引表
                            也叫pl/sql表，它是oracle是期版本处理PL/SQL数组的数据类型；它的元素个数无限制，并且下标可以为负值
                           
                            定义PL/SQL索引表的语法：
                                                        type type_name is table of element_type [not null] index by key_type;
                                                        identifier type_name;
                                                       
                                                        补充：
                                                             not null表示不允许引用NULL无素，
                                                             key_type用于指定索引表元素下标的数据类型（binary_integer,pls_integer或varchar2)
                                                            
                                                        在oracle9i前，索引表下标只充许使用数据类型binary_integer和pls_integer;
                                                         自ORACLE9I开始，索引表下标也允许为VARCHAR2
                                                        
                                                        
                                                        
                          示例一：在索引表中使用BINARY_INTEGER和PLS_INTEGER
                                               　　　　SET SERVEROUTPUT ON
                                                       DELCARE
                                                         TYPE ENAME_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER;
                                                         ENAME_TABLE ENAME_TABLE_TYPE;
                                                       BEGIN
                                                         SELECT ENAME INTO ENAME_TABLE(_1) FROM EMP WHERE EMPNO=&NO;
                                                         DBMS_OUTPUT.PUT_LINE('雇员名：'||ENAME_TABLE(-1));--表示下标为－１的元素
                                                       END;
                                                       / 
                                                       END;    
                          
                          示例二：在索引表中使用VARCHAR2下标数据类型
                                                       set serveroutput on
                                                       declare
                                                          type area_table_type is table of number index by varchar2(10);
                                                          area_table area_table_type;
                                                       begin
                                                         area_table('beijing'):=1;
                                                         area_table('shanghai'):=2;
                                                         dbms_output.put_line('first element:'||area_table.first);--显示第一个元素，注意FIRST
                                                         DBMS_OUTPUT.PUT_LINE('LAST ELEMENT'||AREA_TABLE.LAST);
                                                        
                                                        
                                                        
                                                        
                                                        
                                                        
　　　　　　　　　　　　嵌套表
                                      是一种用于处理PL/SQL数组的数据类型；它的元素下标从１开始，并且元素个数没有限制；
                                      　它的元素的元素值可以是稀疏,即元素之间可有空位出现
                                     
                                      索引表类型不能作用表列的数据类型使用，但嵌套表类型可以作为表列的数据类型使用：
                                     
                                      定义嵌套表的语法如下：
                                         type type_name is table of element_type;
                                         identifier type_name;
                                        
                                      注意：在使用嵌套表元素时，必须首先使用其构造方法初始化嵌套表;示例如下；
                                         declare
                                           type ename_table_type is table of emp.ename%type;
                                           ename_table ename_table_type:=ename_table_type('a','b');
                                          
                                          
                                          
                                      下面说明使用嵌套表的方法：
                                         1,在PL/SQL块中使用嵌套表
                                         　　　declare
                                                   type ename_table_type is table of emp.ename%type;
                                                   ename_table ename_table_type;
                                               begin
                                                    --使用前先初始化
                                                   ename_table:=ename_table_type('a','b','c');--NESTED TABLE初始化已有数据                                                        
                                                   select ename into ename_table(2) from emp where empno=&no;--利用SELECT INTO为NESTED TABLE元素供值，作用与UPDATE相同
                                                  
                                                   dbms_output.put_line('employee name:'||ename_table(2));
                                               end;
                                               /
                                                 
                                                 
                                                 
                                              SQL>
                                              --下面为直接初始使用，未使用SELECT INTO
                                        SQL> declare
                                          2     type ename_table_type is table of emp.ename%type;
                                          3     ename_table ename_table_type;
                                          4  begin
                                          5      --使用前先初始化
                                          6     ename_table:=ename_table_type('a','b','c');
                                          7     --select ename into ename_table(2) from emp where empno=&no;
                                          8     dbms_output.put_line('employee name:'||ename_table(2));
                                          9  end;
                                         10  /
                                       
                                        employee name:b
                                       
                                       
                                       
                                        2,在表列中使用NESTED TABLE
                                          
                                           　　在表列中使用NESTED TABLE,必须首先使用CREATE TYPE建立NESTED TABLE类型
                                           　　　
                                           　　在表列中使用NESTED　TABLE时，必须要为对应的表列指定专门的存储表
                                          
                                               在表列中使用NESTED TABLE类型的示例如下:
                                                  create type phone_type is table of varchar2(20);
                                                  /
                                                  create table employee(
                                                   id number(4),
                                                   name varchar2(10,
                                                   sal number(6,2),
                                                   phone phone_type --表列PHONE使用类型PHONE_TYPE
                                                   ) NESTED TABLE PHONE STORE AS PHONE_TABLE;--要为NESTED TABLE表列指定专门的存储表
                                                  
                                                  
                                        下列说明在PL/SQL块中操作NESTED TABLE表列的方法：
                                        　　
                                        　　　示例一：在PL/SQL块中为NESTED TABLE表列插入数据
                                                   begin
                                                      insert into employee values(1,'scott',800,phone_type('0472','12255444'));
                                                   end;
                                                    /
                                                   
                                              示例二：在PL/SQL块中检索NESTED TABLE 表列的数据
                                                 set serveroutput on
                                                 declare
                                                   phone_table phone_type;--声明接受表列为NESTED TABLE列的表列
                                                 begin
                                                    select phone into phone_table  把表EMPLOYEE的NESTED TABLE表列　PHONE存储到变量PHONE_TABLE
                                                    from employee where id=1;
                                                   
                                                     --用FOR  LOOP显示NESTED TABLE类型的变量PHONE_TABLE中的值
                                                    FOR I IN 1..PHONE_TABLE.COUNT LOOP --count表示NESTED TABLE变量总共的元素个数
                                                      DBMS_OUTPUT.PUT_LINE('PHONE NUMBER:'||PHONE_TABLE(I));--phone_table(i) 表示NESTED TABLE具体的元素
                                                    END LOOP;
                                                   
                                                 end;
                                                     
                                                     
                                              示例三：在PL/SQL块中更新NESTED TABLE    表列的数据
                                                  declare
                                                    phone_table phone_type:=phone_type('0412','abc','toxls','lvs');   
                                                  begin
                                                    update employee
                                                    set phone=phone_table where id=1;
                                                  end;