Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1369853
  • 博文数量: 172
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3831
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc

文章分类

全部博文(172)

文章存档

2024年(27)

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

分类: Oracle

2020-07-07 16:08:34

集合类型

1-1 使用集合的优点

集合类型包括index by table,nested table,varray三种,集合是存放同类元素的一个容器,使用集合有许多优点。

1.保持列表的维护轨迹。也可以使用临时表或分割字符来获得列表轨迹等,但是使用集合是高效代码干净的。

2.双向关联和自动访问的游标。在plsql中游标只允许向前fetch,若将游标的信息存到集合中,则可以向前后操作。

3.提高查询的性能。

4.缓存数据库信息。

 

1-2 内容简介

1.集合类型概览,对此三种类型做一般性介绍。

2.集合的方法。

3.使用集合。初始化,不同方式使用以及结合sql的使用。

4.nested tablemultiset操作。在oracle10g中增加了multiset操作符,允许对nested table进行集合操作(union,intersect,minus)

5.操作schema级别的集合。使用oracle 提供的数据字典视图来操作集合类型。

 

1-3 集合类型概览

集合类型定义好之后,可以在真实的表中使用,也就是自定义类型的表。用create or replace type

1.集合类型

Associative arrays 一维,没有大小空间限制的,元素之间是[d1] 存储的,在oracle9i之前是index by表,9i正式改名为associative arrayIndex by语句可以使用”associate”或者index by varchar2(length)pls_integer来代替。Index by是存放元素的索引。初始化只要给一个元素赋值就行,和其他两种需要构造器初始化不一样定义schema级别的类型不能是index by表类型,其他两者都可以。shema级别的定义好处是外部可以使用。接受索引-231 + 1 and 231 – 1

key ----value,稀疏存储。

2.nested table index by表一样,没有index by关键字,初始化必须用构造器,默认下标是数字,大小可以自动增长。元素无顺序排列。

虽然可以通过key查找,但是不保证顺序。

3.varray 一维,但是元素之间是紧密排列的,下标必须是数字,通过构造器初始化。和nested table不一样,元素是有序排列的。

Oracle目前不支持多维集合,要使用多维集合,只能申明一个集合的类型是一个集合。如my_collection(12,22)一维,my_collection(12)(24)多维,但是oracle不直接支持,可以申明集合的集合来实现。集合中存放同种元素,使用集合变量的类型申明为预先定义的集合类型,然后初始化使用。


集合例子:

1.index by表集合

/**

author:dingjun

date:2008-4-13

一个使用index by表的例子

**/

declare

/*

  申明一个index by表集合类型,在oracle10g之后,index by表之后可以是varchar2,也可以是pls_integer,含义是一个记录的下标

*/

  type list_of_names is table of student.name%type index by binary_integer;

  --定义一个list_of_names集合变量

  s_name list_of_names;

  --记录下标

  l_row binary_integer;

  begin

  /*

    初始化,index by表不需要用构造器初始化,而且是稀疏的,只要赋值就可以使用

  */

        s_name(-10):='dj1';

        s_name(1):='dj2';

        s_name(2):='dj3';

        s_name(4):='dj4';

       

        l_row:=s_name.first;--first无参,可以不需要括号

        --循环迭代数据

        while (l_row is not null) loop

              dbms_output.put_line('the name is '||s_name(l_row));

              l_row:=s_name.next(l_row);--next(x),返回下一个下标

        end loop;

end;

 

--注意,10g允许index by表的下标是varchar2,那么最好不要用for i in low .. high了,因为它要求i是数值类型,如果是字符串,会自动转换类型,不成功则报错,用while循环,见下面例子

--下标为字符串的index by表迭代方式常规用法

declare

 type idx_table is table of varchar2(10) index by varchar2(100);

 var idx_table;

 idx varchar2(10);

 begin

 var('a'):='a';

 var('b'):='b';

 idx := var.first;

 while idx is not null

 loop

  dbms_output.put_line(var(idx));

  idx := var.next(idx);

 end loop;

 end;

 /

 

 --for i in low .. high要求i是数值类型,类型转换不成功

 declare

 type idx_table is table of varchar2(10) index by  varchar2(100);

 var idx_table;

 begin

 var('a'):='a';

 var('b'):='b';

for idx in var.first .. var.last loop

   dbms_output.put_line(var(idx));

end loop;

 end;

 /

 

 --转换成功

  declare

 type idx_table is table of varchar2(10) index by varchar2(100);

 var idx_table;

 begin

 var('1'):='a';

 var('2'):='b';

for idx in var.first .. var.last loop

   dbms_output.put_line(var(idx));

end loop;

 end;

 /

 

2.nested table集合

--create type建立一个nested table类型

create or replace type list_of_name is table of varchar2(1000);

--schema级别的集合类型(nested tablevarray)不能机遇表或视图。当然object类型是可以的,可以定义一个object类型

SQL> create or replace type list_of_name is table of emp.ename%type;

  2  /

 

Warning: Type created with compilation errors

 

SQL> show err

Errors for TYPE DINGJUN123.LIST_OF_NAME:

 

LINE/COL ERROR

-------- --------------------------------------------

1/31     PLS-00201: 必须声明标识符 'EMP.ENAME'

0/0      PL/SQL: Compilation unit analysis terminated

/**

   author:dingjun

   date:2008-4-13

   声明三个集合变量,然后在happyfamily变量中增加4个元素,在children中增加两个元素,

   oracle 10g支持对nested table使用multiset操作符,进行集合操作,然后通过差集求出

   prents元素的值,打印结果

 

**

直接赋值也可以.extend,然后happyfamily(happyfamily.count):=.

/

declare

 

       happyfamily list_of_name:=list_of_name();--必须在使用的时候初始化,此处初始化为一个空元素,此处若不初始化,必须在体部初始化

       children list_of_name:=list_of_name();

       parents list_of_name:=list_of_name();

       begin

               happyfamily.extend(4);--extend(4)是在最后一个元素后面增加4个元素

               happyfamily(1):='dingjun';

               happyfamily(2):='jianjun';

               happyfamily(3):='renhua';

               happyfamily(4):='xiaozhao';

              

               children.extend;--默认在末尾增加一个元素

               children(1):='dingjun';

               children.extend;

               children(2):='jianjun';

   

               parents:=happyfamily multiset except children;--multiset except求差集

              

               for l_row in parents.first .. parents.last loop--循环得到结果,通过下标访问

                   dbms_output.put_line('parents name is '||parents(l_row));

               end loop;          

       end;


 
3.varray集合

/**

author:dingjun

date:2008-4-13

申明两个varray类型,然后用于真实的表,用匿名过程操作插入内容到表中

**/

create or replace type first_name_t is varray(2) of varchar2(100);

 

create or replace type child_name_t is varray(1) of varchar2(100);

 

create table family(

       surname varchar2(100),

       parent_names first_name_t,

       child_names child_name_t

);

 

 

 

declare

       parents first_name_t:=first_name_t();--varraynested表一样,必须用构造器初始化

       children child_name_t:=child_name_t();

       begin

                parents.extend(2);

                parents(1):='jj';

                parents(2):='dd';

               

                children.extend;

                children(1):='son';

               

                insert into family values('aa',parents,children);

                commit;

       end;

       /

    declare

       parents first_name_t:=first_name_t('a','b'); --初始化两个元素,索引自动为整数

       begin

     for i in parents.first .. parents.last loop

     dbms_output.put_line(parents(i));

     end loop;

       end;

select *  from family;

 

  1-4 集合的使用场合

数据库自身可以通过create type定义两种类型的集合(nested table and varray)index by 表不可以单独定义。

1.集合可以作为record域的类型。

2.集合可以作为过程或函数等的参数传递。如下面声明一个schema级别的集合,然后作为参数传递

CREATE TYPE yes_no_t IS TABLE OF CHAR(1);
    /
    CREATE OR REPLACE PROCEDURE act_on_flags (flags_in IN yes_no_t)
    IS
    BEGIN
        ...
    END act_on_flags;
    /

 

3.集合作为函数的返回值类型。

4.集合作为一个object的属性,如

 

CREATE TYPE Auto_spec_t AS OBJECT (
       make VARCHAR2(30),
       model VARCHAR2(30),
       available_colors Color_tab_t—一个集合
    );

 

 

1-5 集合的内置方法

  如果不需要参数,可以省略括号。其中x,y…为下标,下标列出此方法对哪个集合实用以及相应的操作用法。

方法(函数或过程)

描述

Count方法

返回集合中元素的个数

Delete过程

删除一个或多个或所有元素,所有删除:delete,一个删除:delete(x),如果xnull,则集合保持不变。删除xy的元素:

delete(x,y),如果x>y则集合保持不变,对varray,只能做全部删除操作。

Exists函数

返回truefalse,指定元素是否存在。使用是:collection.exists(x),必须有且只有一个参数,下标

Extend过程

在集合元素末尾增加n个元素,对index by表非法。增加一个元素collection.extend;增加n个元素:collection.extend(n),增加指定元素的副本n次:collection.extend(n,x);增加x的副本n次在末尾。

Firstlast

返回集合的最小下标和最大下标,对varray而言,first始终是1last始终等于count

Limit

返回varray中允许的元素最大个数,nested tablenull,对index by表不起作用。

Prior,next

返回指定元素x之前的元素下标,若元素x是第一个元素,则prior返回null,若是最后一个元素,next返回null,使用:collection.next(x)

Trim过程

从集合末尾删除一个元素或多个元素。删除一个:collection.trim,删除ncollection.trim(n)

 

方法举例:

declare

       happyfamily list_of_name:=list_of_name();--必须在使用的时候初始化,此处初始化为一个空元素,此处若不初始化,必须在体部初始化

       children list_of_name:=list_of_name();

       parents list_of_name:=list_of_name();

       begin

               happyfamily.extend(4);--extend(4)是在最后一个元素后面增加4个元素

               happyfamily(1):='dingjun';

               happyfamily(2):='jianjun';

               happyfamily(3):='renhua';

               happyfamily(4):='xiaozhao';

   

               happyfamily.extend(4,3);--extend(x,y)x指增加了几个复制的值,y指赋值下标为y

的元素的值。也就是在末尾赋值元素下标为y的元素值x次。

               dbms_output.put_line('总共有元素数量为:'||happyfamily.count);--返回数量

               dbms_output.put_line(happyfamily(happyfamily.prior(2))||'返回值');--happyfamily.prior(2)返回紧跟下标为2的元素之前的一个元素的下标

               dbms_output.put_line(happyfamily.next(2)||'返回下标');

              

               --末尾删除一个元素

               happyfamily.trim;

               dbms_output.put_line('末尾删除一个元素之后还有:'||happyfamily.count);

               --再次在末尾删除两个元素

               happyfamily.trim(2);

                dbms_output.put_line('末尾删除两个元素之后还有:'||happyfamily.count);

                --一次删除1-3的所有三个元素

               happyfamily.delete(1,3);

                dbms_output.put_line('末尾删除两个元素之后还有:'||happyfamily.count);

               if happyfamily.exists(1) then

                  dbms_output.put_line(happyfamily(1)||' 存在!');

               end if;

              

               children.extend;--默认在末尾增加一个元素

               children(1):='dingjun';

               children.extend;

               children(2):='jianjun';

   

               parents:=happyfamily multiset except children;--multiset except求差集

              

               for l_row in parents.first .. parents.last loop--循环得到结果,通过下标访问

                   dbms_output.put_line('parents name is '||parents(l_row));

               end loop;        

          end;

 

结果是:

总共有元素数量为:8

dingjun返回值

3返回下标

末尾删除一个元素之后还有:7

末尾删除两个元素之后还有:5

末尾删除两个元素之后还有:2

parents name is xiaozhao

parents name is renhua

 

 

--遍历索引是字符串的,因为for i 是数字的,不能用

DECLARE

    TYPE tp_test IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);

 

    lists tp_test;

    l_row VARCHAR2(100);

BEGIN

    lists('a') := 'aa';

    lists('b') := 'bb';

    lists('c') := 'cc';

    lists('d') := 'dd';

 

   

    dbms_output.put_line(lists.FIRST);

    l_row := lists.FIRST;

    WHILE (l_row IS NOT NULL)

    LOOP

        dbms_output.put_line('the name is ' || lists(l_row));

        l_row := lists.NEXT(l_row); --next(x),返回下一个下标

    END LOOP;

END;

 


 

 未完待续,见PART2:http://blog.chinaunix.net/uid-7655508-id-5835535.html



阅读(2242) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~