PL/SQL中的集合类型_PART3

集合分配

相同的集合类型相互赋值，则原来的那个元素的所有值全被替换成新的。

 

   

declare

  type t_name is table of varchar2(10) index by pls_integer;

  old_names t_name;

  new_names t_name;

 

begin

  old_names(1) := 'wang';--初始化

--  old_names(2) := 'ding';

 

  new_names(111) := 'jack';--初始化

  new_names(222) := 'json';

 

  old_names := new_names;--将new_names赋值给old_names，则原来的值不存在了，只有111，222这个值

 

  dbms_output.put_line(old_names.last);

end;

 

 

从数据库的表中分配集合的值

1.       获得单行数据

通过select into获得单行数据进入到集合中。最好把集合的元素类型定义成表类型。

 

declare

  type student_copy is table of student%rowtype;

  cst student_copy:=student_copy();--初始化为空

  begin

      cst.extend;--必须有此关键字，不然不可以增加,报下标超出数量错误

       select *  into cst(1) from student where rownum=1;--抓取第一行到集合中

     dbms_output.put_line(cst(1).name);--相当于获得一个行，通过.column获得其属性值

  end;

 

 

2.       获得多行数据

通过for循环一个游标获得多行插入,对于nested table和varray通过extend，对于index by表通过预定义首行index。

/**

获得多行数据

**/

 

declare

  type student_copy is table of student%rowtype;

  cst student_copy:=student_copy();

  begin

      for via in (select *  from student) loop--循环加入

          cst.extend;

          cst(cst.last):=via;--cst.last返回最后一个索引

         dbms_output.put_line(cst(cst.last).subject);

      end loop;

  end;

/**

对index by表copy多行数据,不能通过extend和loop循环实现，因为extend对于index by非法,index by是稀疏性的，例子里面按顺序来

1.通过bulk collect into 2.如下

**/ 

declare

    res operate_collect.i_st;

    l_row binary_integer:=1;

    begin

        for via in (select *  from student) loop

            res(l_row):=via;

            l_row:=l_row+1;

        end loop;

        dbms_output.put_line('总共拷贝'||res.count||'行数据 ');

       for i in res.first..res.last loop

        dbms_output.put_line('name is '||res(i).name||', subject is '||res(i).subject||', grade is '||res(i).grade);

        end loop;

    end;

 

3.       批处理

Oracle 9i realse2在plsql中对集合初始化支持bulk collect into批量处理，对nested table或varray不需要用构造器初始化，当然也支持index by表的操作。如：

 

/**

通过批处理bulk collect into，不需要初始化nested table或arrays,oracle 9i realse2支持

**/

declare

          type student_copy is table of student%rowtype;

          cst student_copy;--没有用构造器初始化

          begin

              select *  bulk collect into cst from student;--批量插入

              for i in cst.first..cst.last loop

                  dbms_output.put_line(cst(i).subject);

              end loop;           

          end;

 

 

 

 

 

 

高级无序集合操作

       为什么要对集合进行无序操作？在很多应用程序中，我们常常进行一次又一次的相同操作，如果在操作期间，这些查询的数据是很少发生改变的话，我们没有必要每次都进入数据库查询，发送sql—>解析sqlà执行sqlà返回结果。就算在oracle中，相同的sql会缓存到sga中，我们也需要访问sga，然后解析sql获得查询结果。如果查询的表很小或是一个静态表，则可以把该表缓存到一个包级的集合中，用函数获得查询结果。这样就可以在pga(进程全局区)而不是sga(系统全局区)来取数据，这样效率很高。如：

/**

无顺序的集合操作，提高查询效率

**/

--建立表

create table hairstyles(

       code number(10),

       description varchar2(100)

);

 

--插入测试数据

begin

       for i in 1 ..100000 loop

       insert into hairstyles values(i,'this is hair'||i);

       end loop;

end;

commit;

 

--建立package

create or replace package justonce is

   FUNCTION description (code_in IN hairstyles.code%TYPE)--description函数返回查询结果

          RETURN hairstyles.description%TYPE;

end justonce;

 

--建立package body

    CREATE OR REPLACE PACKAGE BODY justonce

    IS

    /*

    声明一个集合类型，作为返回结果

    */

       TYPE desc_t IS TABLE OF hairstyles.description%TYPE

          INDEX BY BINARY_INTEGER;

       descriptions   desc_t;

    

    /*

    声明函数description传入code_in返回查询结果赋给声明的集合descriptions

    */

       FUNCTION description (code_in IN hairstyles.code%TYPE)

          RETURN hairstyles.description%TYPE

       IS

       --作为返回结果

          return_value   hairstyles.description%TYPE;

     --声明子函数

          FUNCTION desc_from_database RETURN hairstyles.description%TYPE

          IS

 

--cursor根据外层函数的code查询结果

            CURSOR desc_cur IS

               SELECT description FROM hairstyles WHERE code = code_in;

          --返回desc_rec，存放的是description

            desc_rec   desc_cur%ROWTYPE;

         BEGIN

            OPEN desc_cur;

            FETCH desc_cur INTO desc_rec;

            CLOSE desc_cur;

            RETURN desc_rec.description;

         END;

        

        

      BEGIN

         RETURN descriptions (code_in);

      EXCEPTION

         WHEN NO_DATA_FOUND THEN

            descriptions (code_in) := desc_from_database;

            RETURN descriptions (code_in);

      END;

   END justonce;

  

  

--测试结果

declare

   res varchar2(100);

   begin

       res:=justonce.description(9999);

       dbms_output.put_line(res);

   end;

在集合里访问数据要解决的问题

1.       如果集合还未被初始化，就查询行数据，则oracle抛出NO_DATA_FOUND异常。所以使用之前必须要填充数据到集合中。

2.       如果extend到varray中，已经超出最大范围limit，则会报越界。