【database】oracle集合 - Associative Arrays、Varrays、Nested Tables

前言

  参考oracle官方文档：PL/SQL Language Reference 11g Release 2  -  5 PL/SQL Collections and Records

  可以去看下文档中写的 - 各个集合的适用场景：Appropriate Uses for Associative Arrays 、 Appropriate Uses for Varrays 、Appropriate Uses for Nested Tables 。

  （自己也没怎么用过，所以不太知道怎么抉择）

一、Associative Arrays

-- #### Example 5–1 Associative Array Indexed by String
DECLARE
    -- Associative array indexed by string:
    TYPE t_Map IS TABLE OF NUMBER INDEX    -- index类型：NUMBER
            BY VARCHAR2(64);     -- value类型：varchar2(64)
    map t_Map; -- 定义一个类型是t_Map的变量map
    key_ VARCHAR2(64); -- Scalar variable
BEGIN
    -- Add elements (key-value pairs) to associative array:
    map('key-2') := 2;
    map('key-1') := 1;
    map('key-3') := 3;
    -- Change value associated with key 'key-3':
    map('key-3') := 33;
    -- map('key-1') := '2001';  -- 正确
    -- map('key-1') := '2001a'; -- 错误：value类型只能是NUMBER
    -- map(1)    := 2002;         -- 正确,虽然定义的key是String。但会被oracle隐式转换
    -- Print associative array:
    key_ := map.FIRST; -- (collection methods)Returns first index in collection.
    WHILE key_ IS NOT NULL LOOP
        DBMS_Output.PUT_LINE('key: ' || key_ || ' , value：' || map(key_));
        key_ := map.NEXT(key_); -- (collection methods)Returns index that succeeds specified index.
    END LOOP;
END;

### 输出结果
key: key-1 , value：1
key: key-2 , value：2
key: key-3 , value：33

  1. 不妨可以把它理解成Map。

  2. 存储顺序与create/add的顺序无关，与index的排序有关。（Changing NLS Parameter Values After Populating Associative Arrays）

  3. key唯一不重复，重复则覆盖之前的。（与map一样）

二、Variable-Size Arrays

-- ####Example 5–4 Varray (Variable-Size Array)
DECLARE
    TYPE Foursome IS VARRAY(18) OF VARCHAR2(15); -- VARRAY type
    -- varray variable initialized with constructor:
    team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita');
    PROCEDURE print_team (heading VARCHAR2,lim NUMBER) IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE(heading||',max:'||team.LIMIT);
            FOR i IN 1..lim LOOP
                DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('---');
        END;
BEGIN
    print_team('2001 Team:',team.count);
    team(3) := 'Pierre'; -- Change values of two elements
    team(4) := 'Yvonne';
--    team.EXTEND();    -- 添加1个null元素到数组最后。
    team.EXTEND(2);    -- 添加2个null元素到数组最后。
    team.EXTEND(3,2);    -- 把index=2的元素复制3次,添加到最后

    print_team('2005 Team:',team.count);
    -- Invoke constructor to assign new values to varray variable:
    team := Foursome('Arun', 'Amitha', 'Allan', 'Mae');
    print_team('2009 Team:',team.count);
END;

#### 输出结果
2001 Team:,max:18
1.John
2.Mary
3.Alberto
4.Juanita
---
2005 Team:,max:18
1.John
2.Mary
3.Pierre
4.Yvonne
5.
6.
7.Mary
8.Mary
9.Mary
---
2009 Team:,max:18
1.Arun
2.Amitha
3.Allan
4.Mae
---

  1. 数组长度不灵活，事先定义了最大数组长度。

  2. 只找到了构造函数初始化数组，如demo。 但这有个问题，我定义的最大数组长度是18，但构造函数只定义了4个元素。

    此时，可以通过team(n)，1≤n≤4来 访问/修改 这4个元素的值。（oracle的下标从1开始，而不是0）

    但是，并不能调用类似team(5+)：[Err] ORA-06532: 下标超出限制。

    而collection methods中只找到了EXTEND(…)方法，但并不能达到team[i] = value的效果;

三、Nested Tables

DECLARE
    TYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type
    -- nested table variable initialized with constructor:
    names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
    PROCEDURE print_names (heading VARCHAR2) IS
        BEGIN
            DBMS_OUTPUT.PUT_LINE(heading);
            FOR i IN names.FIRST .. names.LAST LOOP -- For first to last element
                DBMS_OUTPUT.PUT_LINE(names(i));
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('---');
        END;
BEGIN
    print_names('Initial Values:');

    names(3) := 'P Perez'; -- Change value of one element
    print_names('Current Values:');


    names := Roster('A Jansen', 'B Gupta'); -- Change entire table
--    DBMS_OUTPUT.PUT_LINE(names(3)); -- 改变结构,访问index=3会抛出err：[Err] ORA-06533: Subscript beyond count
    print_names('Current Values:');
END;

#### 输出结果
Initial Values:
D Caruso
J Hamil
D Piro
R Singh
---
Current Values:
D Caruso
J Hamil
P Perez
R Singh
---
Current Values:
A Jansen
B Gupta
---

  1. 这是最简单的nested tables。对于nested tables有更复杂的用法。

  2. nested tables与arrays的区别：Important Differences Between Nested Tables and Arrays

 

四、三种集合的总结

  （1） 定义的差异

-- (1) Associative Arrays (索引类型限制:string、PLS_INTEGER)
TYPE t_Map IS TABLE OF NUMBER INDEX    -- index(索引)类型：NUMBER
        BY VARCHAR2(64);     -- value(值)类型 & 值长度限制：varchar2(64)

-- (2) Varrays (Variable-Size Arrays)
TYPE t_Varray IS VARRAY(18)         -- 数组最多元素;18
        OF VARCHAR2(15);     -- 数组值限制

-- (3) Nested Tables
TYPE t_Nested IS TABLE OF VARCHAR2(15); -- 值限制

  （2） 类型差异

五、扩展

  （1） Collection Methods