《Oracle PL/SQL开发指南》学习笔记29——源码调试——关联数组（第五部分，Associative Arrays）

 

关联数组的定义（说句实话，不好理解！平时工作中尚未用到，缺乏直观感受，更别提深入理解了！）：

Associative arrays are also single-dimensional structures of an Oracle Database 12c database, and  they can hold the same base data types as SQL collections. As discussed in the “Oracle PL/SQL  Tables” sidebar, they were previously known as PL/SQL tables. This section focuses on single dimensional  structures of the associative array.  Associative arrays are single-dimensional composite data types, and they can hold only a  scalar or composite base data type. You can’t define a multidimensional collection because  collections can’t hold multiple copies of a base type across each element (row). While collections  can’t hold other composite data types, they can hold another copy of the collection in each row.  When collections hold other collections, they’re called multilevel collections.  Associative arrays cannot be used as column data types in tables. They may be used only as  programming structures. You can only use associative arrays in a PL/SQL context, which means  you can’t pass a PL/SQL collection as a parameter from within a SQL statement or as a return  value from a function.  It is important to note some key issues presented by associative arrays. These issues drive a  slightly different approach to illustrating how you use them.

Associative arrays：

1. Do not require initialization and have no constructor syntax. They also do not need to  allocate space before assigning values.  2. Can be indexed numerically in Oracle Database versions up to and including 12c. In  Oracle Database 12c forward, they can also use unique variable-length strings. 

3. Can use any integer as the index value, which means any negative, positive, or zero  whole numbers. 

4. Are implicitly converted from equivalent %ROWTYPE, record type, and object type return  values to associative array structures. 

5. Require special treatment when using a character string as an index value in any database  using globalized settings, such as NLS_COMP or NLS_SORT initialization parameters.

1. 在PL/SQL中定义关联数组：

CREATE OR REPLACE TYPE type_name AS TABLE OF base_type [ NOT NULL ]
INDEX BY [ PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size) ];

或

CREATE OR REPLACE TYPE type_name AS TABLE OF base_type [ NOT NULL ]
INDEX BY key_type;

2. 标量类型的关联数组

1）数字索引的关联数组

SQL> ed
已写入 file afiedt.buf

  1  DECLARE
  2    /* Define an associative array of a scalar data type. */
  3    TYPE suit_table IS TABLE OF VARCHAR2(20 CHAR)
  4      INDEX BY BINARY_INTEGER;
  5    /* Declare and attempt to construct an object. */
  6    lv_suit SUIT_TABLE;
  7  BEGIN
  8    /* Assign values to an ADT. */
  9    lv_suit(1) := 'Club-梅花';
 10    lv_suit(2) := 'Heart-红桃';
 11    lv_suit(3) := 'Diamond-方块';
 12    lv_suit(4) := 'Spade-黑桃';
 13    /* Loop through a densely populated indexed collection. */
 14    FOR i IN lv_suit.FIRST..lv_suit.LAST LOOP
 15      dbms_output.put_line(lv_suit(i));
 16    END LOOP;
 17* END;
SQL> /
Club-梅花
Heart-红桃
Diamond-方块
Spade-黑桃

PL/SQL 过程已成功完成。

不可以调用关联数组的构造函数，因为它没有构造函数。

SQL> ed
已写入 file afiedt.buf

  1  DECLARE
  2    /* Define an associative array of a scalar data type. */
  3    TYPE suit_table IS TABLE OF VARCHAR2(20 CHAR)
  4      INDEX BY BINARY_INTEGER;
  5    /* Declare and attempt to construct an object. */
  6    lv_suit suit_table := SUIT_TABLE('club','heart','diamond','spade');
  7  BEGIN
  8    null;
  9* END;
SQL> /
  lv_suit suit_table := SUIT_TABLE('club','heart','diamond','spade');
                        *
第 6 行出现错误:
ORA-06550: 第 6 行, 第 25 列:
PLS-00222: 在此范围中不存在名为 'SUIT_TABLE' 的函数
ORA-06550: 第 6 行, 第 11 列:
PL/SQL: Item ignored

2）对象类型的关联数组

SQL> ED
已写入 file afiedt.buf

  1  CREATE OR REPLACE
  2     TYPE suit_object IS OBJECT
  3*    (suit VARCHAR2(7));
SQL> /

类型已创建。

SQL> ED
已写入 file afiedt.buf

  1  DECLARE
  2    /* Define an associative array of a object type. */
  3    TYPE suit_table IS TABLE OF suit_object
  4      INDEX BY BINARY_INTEGER;
  5    /* Declare and attempt to construct an object. */
  6    lv_suit SUIT_TABLE;
  7  BEGIN
  8    /* Populate elements of the associative array */
  9    lv_suit(1) := suit_object('Club');
 10    lv_suit(2) := suit_object('Heart');
 11    lv_suit(3) := suit_object('Diamond');
 12    lv_suit(4) := suit_object('Spade');
 13    /* Read the object type contents */
 14    FOR i IN lv_suit.FIRST..lv_suit.LAST LOOP
 15      dbms_output.put_line(lv_suit(i).suit);
 16    END LOOP;
 17* END;
SQL> /
Club
Heart
Diamond
Spade

PL/SQL 过程已成功完成。