浅谈Oracle关联数组

顾名思义，关联数组(以前被称为PL/SQL表或索引表)是一个键值对的集合，每个键是一个唯一性索引,用于定位与之关联的值。在Oracle数据库中，关联数组同样扮演着举足轻重的角色。Oracle关联数组的语法格式是:

变量名(索引)

索引的数据类型可以是字符类型(VARCHAR2, VARCHAR, STRING或LONG),也可以是PLS_INTEGER。索引以排序的状态被存储,而不是以被创建时的顺序,对于字符类型的索引,排序顺序取决于初始化参数NLS_SORT和NLS_COMP，像数据库表一样,关联数组可以:

是空的(但不是NULL)直到你填充它；

可以存放不指定数量的元素,你可以不需要直到它们的位置而访问到它们；

不像数据库表的方面:

不需要磁盘空间或网络操作；

不能被DML语句操作；

样例程序 - 使用字符串作为索引的关联数组

DECLARE

    TYPE population IS TABLE OF NUMBER   -- 关联数组的类型

        INDEX BY VARCHAR2(64);           -- 索引的类型是字符串

    city_population population;          -- 关联数组变量

    i  VARCHAR2(64);                     -- 标量变量

BEGIN

    -- 向关联数组中添加键值对

    city_population('Smallville')  := 2000;

    city_population('Midland')     := 750000;

    city_population('Megalopolis') := 1000000;

    -- 改变与键'Smallville'关联的值

    city_population('Smallville') := 2001;

    -- 打印关联数组

    i := city_population.FIRST;    -- 获得关联数组的第一个元素

    WHILE i IS NOT NULL

    LOOP

        DBMS_OUTPUT.PUT_LINE(

            'Population of ' || i || ' is ' || city_population(i)

        );

        i := city_population.Next(i);    -- 获得关联数组的下一个元素

    END LOOP;

END;

/

样例程序 - 返回使用PLS_INTEGER作为索引的关联数组

DECLARE

    TYPE sum_mutiples IS TABLE OF PL_INTEGER

        INDDEX BY PLS_INTEGER;

    n  PLS_INTEGER := 5;

    sn PLS_INTEGER := 10;

    m  PLS_INTEGER := 3;

    FUNCTION get_sum_multiples (

        multiple IN PLS_INTEGER,

        num      IN PLS_INTEGER

    ) RETURN sum_mutiples

    IS

        s sum_mutiples;

    BEGIN

        FOR i IN 1..num

        LOOP

            s(i) := multiple * ((i* (i + 1)) / 2)

        END LOOP;

        RETURN s;

    END get_sum_multiples;

BEGIN

    DBMS_OUTPUT.PUT_LINE(

        'Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||

        TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples(m, sn)(n))

    );

END;

/

1、声明关联数组常量

要想声明一个关联数组常量,你必须创建一个函数,用来使用初始值填充这个关联数组,并在常量声明的时候调用这个函数。

样例程序如下:

CREATE OR REPLACE PACKAGE My_Types AUTHID CURRENT_USER

IS

    TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;

    FUNCTION Init_My_AA RETURN My_AA;

END My_Types;

/

CREATE OR REPLACE PACKAGE BODY My_Types

IS

    FUNCTION Init_My_AA RETURN My_AA

    IS

        Ret My_AA;

    BEGIN

        Ret(-10) := '-ten';

        Ret(0) := 'zero';

        Ret(1) := 'one';

        Ret(2) := 'two';

        Ret(3) := 'three';

        Ret(4) := 'four';

        Ret(9) := 'nine';

        RETURN Ret;

    END Init_My_AA;

END My_Types;

/

DECLARE

    v CONSTANT My_Types.My_AA := My_Types.Init_My_AA();

BEGIN

    DELARE

        Idx PLS_INTEGER := v.FIRST();

    BEGIN

        WHILE Idx IS NOT NULL

        LOOP

            DBMS_OUTPUT.PUT_LINE(

                TO_CHAR(Idx, '999') || LPAD(v(Idx), 7)

            );

            Idx := v.NEXT(Idx);

        END LOOP;

    END;

END;

/

2、NLS参数值对以字符串作为索引的关联数组的影响

NLS参数(例如NLS_SORT, NLS_COMP和NLS_DATE_FORMAT)会对以字符串作为索引的关联数组产生影响，在填充关联数组后改变NLS参数值，初始化参数NLS_SORT和NLS_COMP会决定关联数组的字符串索引的存储顺序。

如果你在填充关联数组后改动了这些参数值,那么在你调用集合方法FIRST, LAST, NEXT和PRIOR时可能会得到非预期值或抛出异常,如果要避免这种情况,可以在你操作关联数组的会话里改回这些参数的原来值。不是VARCHAR2的索引数据类型

以字符串作为索引的关联数组,在声明时字符串类型必须是VARCHAR2或其子类型。然后,你在填充关联数组时,可以使用其他数据类型作为索引,只要能使用TO_CHAR函数，将其转换为VARCHAR2类型。如果你声明的关联数组的索引数据类型不是VARCHAR2及其子类型,那么一定要确保这些索引在初始化参数改变时能保持一致并唯一,例如:

不要使用TO_CHAR(SYSDATE)作为索引,因为如果NLS_DATE_FORMAT参数值改变,那么TO_CHAR(SYSDATE)的值可能会改变；

不要使用NVARCHAR2类型,因为有些不同的NVARCHAR2索引可能会转换成同一个VARCHAR2值；

不要使用只在大小写或重音位置上不同的CHAR或VARCHAR2类型索引,因为当NLS_SORT或 _CI 或 _AI结尾的参数值改变,这些索引可能会转换成同一个值传递关联数组至远程数据库；

如果你将关联数组作为参数传递给远程数据库,那么本地数据库和远程数据库在NLS_SORT或NLS_COMP参数上有不同的设置值,就会:集合方法FIRST, LAST, NEXT和PRIOR可能会得到非预期值或抛出异常。在本地数据库保持唯一的索引,可能在远程数据库中不再保持唯一性,导致抛出VALUE_ERROR异常

3、关联数组的适当使用

关联数组适用于:

用作小型的关联的lookup表,当你调用子程序或初始化包时在内存中构建它在本地和远程数据库服务器间传递关联数组通常用于保存临时数据,为了使它与数据库会话有相同的生命周期,可以在包头中声明它,在包体中填充它。

Oracle关联数组的应用远不止于此，本文我们只是了解了一些最基本的使用方法。实际上，Oracle数据库中关联数组的使用往往是错综复杂的，需要结合索引，具体情况下具体分析。在本站的Oracle教程中，对一些常用的情况给出了分析，想了解的小伙伴可以自己前去学习。