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

-- #### 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

-- ####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
---

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) 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); -- 值限制