Oracle PLSQL的集合类型

晨曦之光 发布于 2012/04/25 16:18
阅读 1K+
收藏 1

转载:http://www.cnblogs.com/gkl0818/archive/2009/02/25/1397769.html

                               Oracle collections
一、Types of Collections

    1、Associative arrays 数组
      它是同种类型的一维、无边界的稀疏集合,只能用于 PL/SQL。
      DECLARE TYPE t_name IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER; --创建 Collection
              i_name t_name;      --创建 instance
              l_row PLS_INTEGER;
      BEGIN
              i_name(202020):='aaa';
              i_name(-125):='bbb';
              i_name(88):='ccc';              --赋值,row number 可以为任何整数,且可以跳跃(稀疏的),也不用按照顺序赋值,其内部最终按照 row number 排序
              l_row := i_name.FIRST;          --返回第一个 row number
              l_row := i_name.NEXT (l_row);   --返回 l_row 之后的一个 row number,会自动跳过为空的行
              DBMS_OUTPUT.put_line (i_name (l_row)); --返回指定行的值
      END;
  
    2、Nested tables 嵌套表
      也是同种类型的一维无边界集合。起初它是密集的,经过删除操作会变成稀疏。它可以在 PL/SQL 和 Database(某一列为一个嵌套表)中被定义。它是 multisets 的,也就是说 nested table 中的元素没有内在的顺序
      DECLARE TYPE t_name IS TABLE OF varchar2(10); --创建 Collection,和 Associative arrays 相差一个 INDEX BY
              i_n1 t_name := t_name(); --创建 instance,需要使用构造函数
              i_n2 t_name := t_name();
              i_n3 t_name := t_name();
      BEGIN
              i_n1.EXTEND(2); --赋值前要分配空间
              i_n1(1):='aaa'; --赋值,row number 最小为 1,最大为 EXTEND 分配的空间数,可以跳过某个行不分配则为 null
              i_n1(2):='bbb';
              i_n2.EXTEND;     --分配一个空间
              i_n2(1):='bbb';
              i_n3 := i_n1 MULTISET EXCEPT i_n2; --10g 开始提供的功能,将 i_n1 中除去 i_n2 的值,赋值给 i_n3
              FOR l_row IN i_n1.FIRST .. i_n1.LAST --从行首到行尾,依次输出
              LOOP
                 DBMS_OUTPUT.put_line (i_n1(l_row));
              END LOOP;
      END;

    3、VARRAYs 变量数组
      VARRAYs (variable-sized arrays),也是同种类型的一维集合。但它是有界且不稀疏的。在定义 VARRAYs 时要指定它的最大范围。VARRAYs 也可以在 PL/SQL 和 Database 中被定义,但它其中的元素是有顺序的。
      DECLARE TYPE t_name IS VARRAY (2) OF VARCHAR2 (10); --创建 Collection,需要指定最大范围
              i_n1 t_name := t_name(); --创建 instance,需要使用构造函数
      BEGIN
              i_n1.EXTEND(2); --赋值前要分配空间,不能超过最大范围
              i_n1(1):='aaa'; --赋值,row number 最小为 1,最大为 EXTEND 分配的空间数
              i_n1(2):='bbb';
      END;
   
二、Where You Can Use Collections

    1、作为 record 的成分
    2、作为程序的参数
      由于 Oracle 没有预定的集合类型,当作为参数使用前,需要定义集合类型:
        1.用 CREATE TYPE 定义 schema-level 类型
        2.在 package specification 中声明
        3.在外层作用域定义
    3、作为函数的返回值
      1.返回集合直接赋值给 collection variable,这时这个 collection variable 不需要初始化
      2.将返回集合中的一个元素赋值给一个类型兼容的变量
          variable_of_element_type := function() (subscript);
          如果函数返回空值,在赋值时会产生 COLLECTION_IS_NULL 异常,应该捕获并适当处理该异常
    4、作为数据库表的列
      当使用 nested table datatype 作为列时,必须指定 store table 的名字:
        CREATE TABLE personality_inventory (
           person_id NUMBER,
           favorite_colors Color_tab_t,
           date_tested DATE,
           test_results BLOB)
        NESTED TABLE favorite_colors STORE AS favorite_colors_st;
      不能对 store table 进行维护或者试图直接查询、存储数据,只能通过 outer table 来获取它的属性。也不能指定它的 storage parameters,它继承与 outermost table。
      nested tables 和 VARRAYs 的区别在于,VARRAY 和其它数据一起存于表内,而 nested table 存于表外,VARRAY 适合 "small" arrays,nested table 适合 "large" arrays。
    5、作为 object type 的属性
 
三、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,将会产生行连接
    7.还有些情况适合使用 VARRAY:you don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.


Collection Methods (Built-Ins)

Collection methods 只能用于 PL/SQL,不能在 SQL 中使用

1、The COUNT Method
    用于计算 associative array, nested table, or VARRAY 中元素的个数,使用 DELETEd or TRIMmed 将减少 COUNT
    定义:
      FUNCTION COUNT RETURN PLS_INTEGER;
    对于初始化了,但不包含元素的 collection 返回 0,对于空的 associative array 也返回 0
    如果对未初始化的 nested table or a VARRAY 使用 COUNT 将返回 COLLECTION_IS_NULL 异常,associative arrays 无需初始化,故不会返回异常
 
2、The DELETE Method
    DELETE 用于删除 collection 中的一个、多个或者全部元素:
      1.不加参数,删除所有元素,对于 VARRAYs 只能使用这种方法,因为 VARRAYs 不能使稀疏的,要删除一行,只能用 TRIM 删除最后一行
      2.DELETE(i) 删除第 i 行
      3.DELETE(i,j) 删除第 i~j 行
    对于使用参数的 DELETE,其实使用一个占位符来代替删除的元素,以后还可以为它们再赋值,赋值之后在 COUNT 中又开始计数。从物理角度来考虑,PL/SQL 只有在删除足够多数量的元素,以至于可以释放 entire page of memory 时才释放内存,但不加参数的 DELETE 是立即释放所有内存的
    定义:
      PROCEDURE DELETE;
      PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)]);
      PROCEDURE DELETE (i [BINARY_INTEGER | VARCHAR2(size_limit)],
                        j [BINARY_INTEGER | VARCHAR2(size_limit)]);
    如果 i,j 超出范围,不会出现异常,只是删除在范围内的,超出部分忽略
    如果对未初始化的 nested table or a VARRAY 使用 DELETE 将返回 COLLECTION_IS_NULL 异常
 
3、The EXISTS Method
    判断 collection 中的指定行是否存在,如果以前存在,后来被 DELETE 了,那也返回 false
    定义:
      FUNCTION EXISTS (i IN [BINARY_INTEGER | VARCHAR2(size_limit)]) RETURN BOOLEAN;
   
4、The EXTEND Method
    用于为 nested table or VARRAY 分配空间
      1.不加参数,分配一个 null element
      2.EXTEND(n),分配 n 个 null element
      3.EXTEND(n,i),分配 n 个和第 i 值相同的 element,这对于有 NOT NULL 限制的 element 是必要的
    定义:
      PROCEDURE EXTEND (n PLS_INTEGER:=1);
      PROCEDURE EXTEND (n PLS_INTEGER, i PLS_INTEGER);
    如果用 DELETEd or TRIMmed 删除了 collection 的最后一个元素,再 EXTEND 时,会跳过这个,在后面分配空间
    如果对未初始化的 nested table or a VARRAY 使用 DELETE 将返回 COLLECTION_IS_NULL 异常,如果 EXTEND 超过 VARRAY 的最大限制,将返回 SUBSCRIPT_BEYOND_LIMIT 异常
 
5、The FIRST and LAST Methods
    分别返回 COLLECTION 中可用的最低和最高下标,被 DELETE 的不会显示
    定义:
      FUNCTION FIRST RETURN PLS_INTEGER;
      FUNCTION LAST RETURN PLS_INTEGER;
    对于初始化了,但没有元素的 COLLECTION,都返回 null;对于至少有一个 element 的 VARRAYs,FIRST 总为 1,LAST 总等于 COUNT
    如果对未初始化的 nested table or a VARRAY 使用 FIRST and LAST 将返回 COLLECTION_IS_NULL 异常
 
6、The LIMIT Method
    返回 VARRAY 的最大数目限制,如果用于 nested tables or to associative arrays 将返回 NULL
    定义:
      FUNCTION LIMIT RETURN PLS_INTEGER;
    如果对未初始化的 nested table or a VARRAY 使用 LIMIT 将返回 COLLECTION_IS_NULL 异常
 
7、The PRIOR and NEXT Methods
    用于遍历 COLLECTION 中的内容
    定义:
      FUNCTION PRIOR (i [BINARY_INTEGER | VARCHAR2(size_limit)])
          RETURN [BINARY_INTEGER | VARCHAR2(size_limit)];
      FUNCTION NEXT (i [BINARY_INTEGER | VARCHAR2(size_limit)])
          RETURN [BINARY_INTEGER | VARCHAR2(size_limit)];
    如果应用于初始化了但为空的 COLLECTION 时,返回 null;如果 i 大于等于 COUNT,则 NEXT 返回 null;如果 i 小于等于 FIRST,则 PRIOR 返回 null
    就目前来说,如果 i 大于 COUNT,则 PRIOR 返回 LIMIT;如果 i 小于 FIRST,则 NEXT 返回 LIMIT,但以后的版本不知道是不是这样了
    如果对未初始化的 nested table or a VARRAY 使用 PRIOR and NEXT 将返回 COLLECTION_IS_NULL 异常
 
8、The TRIM Method
    由于删除 nested table or VARRAY 末尾的 n 行,如果不加参数,删除最后一行,如果用于 associative array 将产生编译时错误
    DELETE and TRIM 联合使用将产生重复删除的问题,比如 DELETE 删除最后一行之后,再用 TRIM 删除最后 n 行时,其实 TRIM 重复操作了被 DELETE 删除的那行(因为删除之后变成了占位符),使得实际上少删除一行,因此 Oracle 建议两者不要混合使用
    定义:
      PROCEDURE TRIM (n PLS_INTEGER:=1);
    如果 n 为 null,TRIM 不做任何事情
    如果对未初始化的 nested table or a VARRAY 使用 TRIM 将返回 COLLECTION_IS_NULL 异常
    如果 TRIM 的 n 大于实际存在的元素个数,那么返回 SUBSCRIPT_BEYOND_COUNT 异常
 
 
Working with Collections

一、Declaring Collection Types

    两种声明方法:
      1.在 PL/SQL 中使用 TYPE 语句,如果定义在 package 中,对于使用该类型的用户要将 package 的 EXECUTE 权限授予该用户
      2.对于 nested table type or VARRAY type 在 schema-level 使用 CREATE TYPE 命令,并将该类型的 EXECUTE 权限授予需要使用的用户
   
    Declaring an associative array collection type
      TYPE table_type_name IS TABLE OF datatype [ NOT NULL ]
         INDEX BY index_type;
      datatype 包括:
        1.Scalar datatype:VARCHAR2, CLOB, POSITIVE, DATE, or BOOLEAN 等等
        2.Anchored datatype:使用 %TYPE、%ROWTYPE
        3.Complex datatype:9i R2 开始可以使用 object types and collection types
      index_type 包括:
        INDEX BY BINARY_INTEGER;(9i R2 之前只能使用该类型)
        INDEX BY PLS_INTEGER;
        INDEX BY POSITIVE;
        INDEX BY NATURAL;
        INDEX BY SIGNTYPE;
        INDEX BY VARCHAR2(32767);
        INDEX BY table.column%TYPE;
        INDEX BY cursor.column%TYPE;
        INDEX BY package.variable%TYPE;
        INDEX BY package.subtype;
     
    Declaring a nested table or VARRAY
      CREATE [ OR REPLACE ] TYPE type_name AS | IS
         TABLE OF element_datatype [ NOT NULL ];
      CREATE [ OR REPLACE ] TYPE type_name AS | IS
         VARRAY (max_elements) OF element_datatype [ NOT NULL ];
      DROP TYPE type_name [ FORCE ];
      element_datatype 包括:大部分 scalar datatypes, an object type, or a REF object type,如果是 object type,那么它的属性不能是 collection 类型;在 PL/SQL 中,如果是 RECORD,那么它只能包含 scalars or objects 类型
      FORCE:即使该 collection type 被其它类型引用,也 drop 它
   
    Changing nested table or VARRAY characteristics

      1.改变 VARRAY 元素最大个数:
          ALTER TYPE list_t MODIFY LIMIT 100 INVALIDATE | CASCADE
      2.改变元素类型为 variable character, RAW, or numeric 的类型长度或精度
          CREATE TYPE list_t AS VARRAY(10) OF VARCHAR2(80);
          ALTER TYPE list_t MODIFY ELEMENT TYPE VARCHAR2(100) INVALIDATE | CASCADE
      INVALIDATE:所有使用该类型的对象失效
      CASCADE:将改变传递给所有使用该类型的对象
   
二、Declaring and Initializing Collection Variables

    collection_name collection_type [:= collection_type (...)];
    对于 nested table or VARRAY 需要用构造函数初始化,构造函数可以可以接受用逗号分割的初始值作为参数,如果不初始化就使用会得到错误:ORA-06531: Reference to uninitialized collection
 
    1、Initializing implicitly during direct assignment

      同一 collection type (必须同一个类型,哪怕元素类型相同的不同 collection type 也不行)的不同变量,可以通过直接赋值进行隐式初始化
        DECLARE
           earth_colors Color_tab_t := Color_tab_t('BRICK', 'RUST', 'DIRT');
           wedding_colors Color_tab_t; --必须同一类型,都是 Color_tab_t,这里没有初始化
        BEGIN
           wedding_colors := earth_colors; --赋值时同时初始化
           wedding_colors(3) := 'CANVAS';
        END;
     
    2、Initializing implicitly via FETCH

      如果表中的列是 Collection type,那么可以像一般类型一样通过 select ... into、FETCH 直接将值传递给相同类型的变量,并可以自动给该变量初始化
 
三、Populating Collections with Data

    Using the assignment operator
      1.单个赋值:
          countdown_test_list (43) := 'Internal pressure';
      2.整个 record (结构要一致)赋值:
          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;
        相同的还可以使用 select ... into、cursor FOR loop、select ... BULK COLLECT into(9i R2 之后,可将整个表赋值给 Collection,不需要初始化) 进行赋值
      3.相同类型的 Collection 直接赋值(不需要初始化)
   
四、Accessing Data Inside a Collection
    需要注意几点:
      1.对于稀疏 Collection,当访问不存在的元素时会产生 NO_DATA_FOUND 异常
      2.访问超出 EXTEND 分配空间的元素时产生 ORA-06533: Subscript beyond count 异常
      3.对于 VARRAY,访问大于最大元素个数的元素时产生 ORA-06532: Subscript outside of limit 异常
   
五、Collections of Complex Datatypes
    Collections of records
      可以定义元素类型为 record 的 collection,使用 %ROWTYPE or a programmer-defined record type。适用于 PL/SQL,如果是定义在表的列上,不能使用 %ROWTYPE
   
    Multilevel collections
      9i R2 开始可以定义 collection 的元素类型也为 collection,既 collection 嵌套,被称为 multilevel collections,通过这种方法,可以构造多维 collection。
   
六、Using String-Indexed Collections
    9i R2 开始可以使用 strings 作为 index type,FIRST, LAST, PRIOR, and NEXT 方法返回的也是 strings
 
七、Working with Collections in SQL
    Oracle8 开始提供 collection pseudo-functions,它们可以使数据表和 collection 的数据互相转换,只能用于 SQL 语句中
 
    1.The CAST pseudo-function
        在 SQL 中,用于将 built-in datatype or collection type 转换为另一种 built-in datatype or collection type
          CREATE TYPE Color_nt AS TABLE OF VARCHAR2(30);
          CREATE TYPE Color_vat AS VARRAY(16) OF VARCHAR2(30);
          CREATE TABLE color_models (
             model_type VARCHAR2(12),
             colors Color_vat);
          SELECT COLUMN_VALUE FROM
             TABLE (SELECT CAST(colors AS Color_nt)
                   FROM color_models
                   WHERE model_type = 'FGB');
                
    2.The MULTISET pseudo-function
        MULTISET 必须和 CASTs 一起使用,MULTISET 将数据集转换为 collection,SQL MULTISET function 和操作 nested tables 的 PL/SQL MULTISET 是完全不同的
          SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type)
          FROM DUAL;
        As with the CAST pseudo-function, MULTISET cannot serve as the target of an INSERT, UPDATE, or DELETE statement.
     
    3.The TABLE pseudo-function
        TABLE 将 collection 转换为可以 SELECT from 的结果集,既将 collection 中的内容像数据表一样 select 出来
        例如,表中含有 collection 字段,现在要找出 collection 中包含某个内容的所有数据:
          SELECT *
            FROM color_models c
           WHERE 'RED' IN
                (SELECT COLUMN_VALUE FROM TABLE(c.colors));
             
          MODEL_TYPE   COLORS
          ------------ ------------------------------------------------------
          RGB          COLOR_TAB_T('RED', 'GREEN', 'BLUE')
       
        COLUMN_VALUE 是 TABLE operator 创建的 system-defined 列名


Nested Table Multiset Operations

10g 之后针对 nested tables 提供了 high-level set operations,将其作为 multisets 对待

Operation             Return value    Description
=                     BOOLEAN         比较两个 nested tables,如果 named type、cardinality、elements 相等,返回 TRUE,如果 nested table 中包含 null 则必不相等。10g 之前要比较,只能通过一个个元素来比较
<> or !=              BOOLEAN         比较两个 nested tables 是否不同
[NOT] IN ( )          BOOLEAN         判断左边的 nested table 是否在右边括号中的 nested tables list 中
x MULTISET EXCEPT     NESTED TABLE    返回一个 NESTED TABLE,它的元素在 x 中,但不在 y 中,所有的 NESTED TABLE 都必须是相同类型的。DISTINCT 表示返回的 nested table 中除去重复元素
    [DISTINCT] y
x MULTISET INTERSECT NESTED TABLE    返回一个 NESTED TABLE,它的元素是 x 和 y 中元素的交集
    [DISTINCT] y
x MULTISET UNION      NESTED TABLE    返回一个 NESTED TABLE,它的元素是 x 和 y 中元素的并集
    [DISTINCT] y
SET(x)                NESTED TABLE    返回一个 NESTED TABLE,包含 x 中不重复的所有元素
x IS [NOT] A SET      BOOLEAN         判断 x 中的元素是否有重复值
x IS [NOT] EMPTY      BOOLEAN         判断 x 是否为空
e [NOT] MEMBER        BOOLEAN         判断表达式 e 是否包含在 x 的元素中
    [OF] x
y [NOT] SUBMULTISET   BOOLEAN         判断 y 的元素是否全都包含在 x 的元素中
    [OF] x
 
 
Maintaining Schema-Level Collections

一、Necessary Privileges
    如果创建 Schema-Level 的 nested tables and VARRAYS,其它 Schema 如果需要使用该类型必须有该类型的 EXECUTE 权限:
      GRANT EXECUTE on Color_tab_t TO JOE;
    对于包含 Schema-Level Collections 的 packages, procedures, functions 只要有这些过程的 EXECUTE 权限就可以使用这些类型了,但对于 PL/SQL anonymous blocks 中使用这些类型,只能直接将这些类型的 EXECUTE 权限授予执行的用户
    如果某表的一列为 Schema-Level Collection 类型,对该表要有 SELECT, INSERT, UDPATE, and DELETE 权限才能做相应的操作,其次在 INSERT or UPDATE 时还需要有该 Collection 类型的 EXECUTE 权限

二、Collections and the Data Dictionary
    1.USER_TYPES
        所有创建的类型
        SELECT type_name FROM user_types
        WHERE typecode ='COLLECTION';
    2.USER_SOURCE
        类型的定义
        SELECT text FROM user_source
        WHERE name = 'FOO_T' AND type = 'TYPE'
        ORDER BY line;
    3.USER_DEPENDENCIES
        依赖创建的类型的对象
        SELECT name, type FROM user_dependencies
        WHERE referenced_name='FOO_T';


原文链接:http://blog.csdn.net/kkdelta/article/details/4151965
加载中
返回顶部
顶部