PL/SQL中的集合类型_PART2

接PART1：

1-6-1 集合变量的声明

  有两种方式声明集合变量，一是在应用程序级别的声明，二是在schema级别的声明。

1. 应用程序级别的声明，指三种集合类型声明在程序中，程序可以使用，如果是声明在package中，则所有的package的执行部分可以使用。在声明部分使用type语句。

2.schema级别的声明，在应用程序之外声明，也就是建立一个数据库对象，使用create type，

当然也可以使用create or replace type，删除使用drop type name [force]，这样的自定义类型，可以在数据库中表的列类型应用，以及程序中使用。

 

   声明index by 表(associative array)

    TYPEtable_type_nameIS TABLE OFdatatype[ NOT NULL ]

       INDEX BYindex_type;

Index_type是管理行的内容的索引。index_type不局限于binary_integer，下面全为合法：

 INDEX BY BINARY_INTEGER;

    INDEX BY PLS_INTEGER;

    INDEX BY POSITIVE;

    INDEX BY NATURAL;

    INDEX BY SIGNTYPE;

    INDEX BY VARCHAR2(32767);

    INDEX BYtable.column%TYPE;

    INDEX BYcursor.column%TYPE;

    INDEX BYpackage.variable%TYPE;

    INDEX BYpackage.subtype;

如在包中声明类型，然后将excute权限给public，则所有的就可以使用这个类型。

CREATE OR REPLACE PACKAGE collection_types

   IS

      -- Associative array types

      TYPE boolean_aat IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;

   

      TYPE date_aat IS TABLE OF DATE INDEX BY BINARY_INTEGER;

   

      TYPE pls_integer_aat IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;

   

      TYPE number_aat IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

    TYPE identifier_aat IS TABLE OF VARCHAR2(30)

     INDEX BY BINARY_INTEGER;

 

    TYPE vcmax_aat IS TABLE OF VARCHAR2(32767)

     INDEX BY BINARY_INTEGER;

 --index by表中存的是record，并且索引为varchar2类型，10g支持索引为varchar2,必须指明长度

 

 type a is record(

   id number,

  mm varchar2(10)

  );

type TEST_TYPE is table of a index by varchar2(10);

 

    -- Nested table types

    TYPE boolean_ntt IS TABLE OF BOOLEAN;

    TYPE date_ntt IS TABLE OF DATE;

    TYPE pls_integer_ntt IS TABLE OF PLS_INTEGER;

    TYPE number_ntt IS TABLE OF NUMBER;

    TYPE identifier_ntt IS TABLE OF VARCHAR2(30);

    TYPE vcmax_ntt IS TABLE OF VARCHAR2(32767)

 END collection_types;

     /

   

                                         

使用：

DECLARE

       family_birthdays collection_types.date_aat;

声明nested table or arrays

      Schema级别的声明：

CREATE [ OR REPLACE ] TYPEtype_nameAS | IS

       TABLE OFelement_datatype[ NOT NULL ];

CREATE [ OR REPLACE ] TYPEtype_nameAS | IS

       VARRAY (max_elements) OFelement_datatype[ NOT NULL ];

删除类型

DROP TYPEtype_name[ FORCE ];//force是不管有没有被使用都删除

应用程序级别的声明，create or replace不使用，直接用type声明。

对于element_type在schema级别的声明可以是不可以引用其他对象类型如%rowtype，在应用程序级别
的声明可以使用其他类型。

 

修改nested table or varray的声明

1. 对varray，修改存放的元素个数，如

create or replace type  ts is varray(10) of varchar2(10) not null;--声明ts为数组

--修改数组中长度信息

Alter type ts modify limit 100 invalidate;

使用alter type命令，然后modify limit 长度，其中invalidate是强迫所有的引用此类型的程序或表发生改变。

2. 修改存放的元素类型

Alter type ts modify element type varchar2(100) cascade;

使用alter type命令，然后是element type 修改后的 cascade是级联所有使用此类型的程序或表。

 

声明和初始化集合变量

 

collection_name collection_type [:=collection_type(...)];

         对index by表不需要构造器初始化，对于nested table和varray需要构造器初始化。如果在使用之前不初始化会报错：ORA-06531: Reference to uninitialized collection一般是在声明集合变量的时候给予空初始化，如果显示初始化多个值，则用逗号隔开。

            对nested table或varray的初始化，可以在声明部分，也可以在执行部分，如

 

       DECLARE

       TYPE company_aat IS TABLE OF company%ROWTYPE;

       premier_sponsor_list company_aat := company_aat(  );--声明部分的初始化

      BEGIN

       ...--

       END;

 

DECLARE

       TYPE company_aat IS TABLE OF company%ROWTYPE;

       premier_sponsor_list company_aat;

    BEGIN

       premier_sponsor_list:= company_aat(  );--执行部分的初始化

END;

   

     CREATE OR REPLACE TYPE Color_tab_t AS TABLE OF VARCHAR2(30);

/**

集合的声明和初始化

**/

   DECLARE

       my_favorite_colors Color_tab_t := Color_tab_t(  );--初始化为空

       his_favorite_colors Color_tab_t := Color_tab_t('PURPLE');--初始化一个元素

       her_favorite_colors Color_tab_t := Color_tab_t('PURPLE', 'GREEN');--初始化两个元素

      

    begin

       dbms_output.put_line(her_favorite_colors(2));--通过下标访问

    END;

 

 

增加新元素，必须使用extend，初始化了，使用隐extend。

 

通过隐性相互赋值直接初始化

        相同类型的集合变量可以相互赋值，而不是存放的元素类型相同。

   

create or replace type st is table of varchar2(10);

 

declare

       a st:=st('ding','wang');--初始化两个值

       b st:=st();--初始化为空

       begin

         b:=a;--相同类型之间赋值，相当于拷贝

         dbms_output.put_line('直接赋值b(2)='||b(2));

         b(2):='jack';--修改b(2),a(2)不受影响

         dbms_output.put_line('修改后的b(2)='||b(2));

       end;  

通过fetch或select..into隐性初始化

如果数据库中表的列类型是集合类型，可以通过fetch或select..into到集合中实现初始化。

--建立一个schema level的集合类型
CREATE OR REPLACE TYPE Color_tab_t AS TABLE OF VARCHAR2(30);
/
--建立一个表有集合列
    CREATE TABLE color_models (
         model_type VARCHAR2(12)
       , colors Color_tab_t
       )
       NESTED TABLE colors STORE AS color_model_colors_tab;
    INSERT INTO color_models
       VALUES ('RGB', Color_tab_t('RED','GREEN','BLUE'));
  DECLARE
       l_colors Color_tab_t;
    BEGIN
       /* Retrieve all the nested values in a single fetch.
       || This is the cool part.
       */
       SELECT colors INTO l_colors FROM color_models
          WHERE model_type = 'RGB';
      dbms_output.put_line(l_colors(1));
    END;

用数据操纵集合

 

       Index by表初始化不需要构造器，只要初始化一个就可以，直接赋值，索引可正可负，范围在-2³¹ + 1 and 2³¹ – 1.

       Nested table和array初始化必须通过构造器。先初始化为空的话，要增加数据必须通过extend关键字，而且增加数据的范围由extend决定。否则会报越界错误。

       给集合变量分配数据：

 

DECLARE

       TYPE emp_copy_t IS TABLE OF emp%ROWTYPE;

       l_emps emp_copy_t := emp_copy_t(  );

       l_emprec emp%ROWTYPE;

    BEGIN

       l_emprec.ename := 'Steven';

       l_emprec.salary := 10000;

       l_emps.EXTEND

       l_emps (l_emps.LAST) := l_emprec;

    END;

 

Index by 表在10g中接受索引类型的种类

       10g中index by表又称为associate arrays，接受的索引不光是binary_integer,对于pls_integer,varchar2等也接受。见下表：

 

INDEX BY clause	Minimum value	Maximum value
INDEX BY BINARY_INTEGER	-231 + 1	231 - 1
INDEX BY PLS_INTEGER	-231 + 1	231 - 1
INDEX BY NATURAL	0	231 - 1
INDEX BY POSITIVE	1	231 - 1
INDEX BY SIGNTYPE	-1	1
INDEX BY VARCHAR(N)	Any string within specified length 任何字符都合法	Any string within specified lengt

 

 

declare

  type aa is table of varchar2(10) index by varchar2(10);--支持varchar2作为索引,任意字符

  vb aa;

  begin

       vb('ds'):='jack';

       vb('4'):='wang';

       vb('5'):='ding';

       dbms_output.put_line(vb('ds'));

  end;

未完待续，见PART3：