使用关联数组

关联数组是一种集合。

另请参见：

有关集合的详细信息：

• Oracle Database Concepts

• Oracle Database PL/SQL Language Reference

关于集合

集合是一种 PL/SQL 复合变量，它以指定顺序存储相同类型的元素，类似于一维数组。集合的内部组件称为元素。每个元素都具有唯一的下标，用于标识它们在集合中的位置。

要访问集合元素，请使用下标表示法 ：collection_name(element_subscript)。

您可以将集合元素视为标量变量。也可以将整个集合作为子程序参数传递（如果发送子程序的和接收子程序都不是独立子程序）。

集合方法是一种内置的 PL/SQL 子程序，它们可以返回有关集合的信息或对集合进行操作。要调用集合方法，请使用点表示法：collection_name.method_name。例如，collection_name.COUNT 可返回集合中元素的数量。

PL/SQL 有三种类型的集合：

• 关联数组（以前称为“PL/SQL 表”或“索引表”）

• 嵌套表

• 可变数组 (varray)

本文档仅介绍关联数组。

另请参见：

• Oracle Database PL/SQL Language Reference（了解有关 PL/SQL 集合类型的详细信息

• Oracle Database PL/SQL Language Reference（了解有关集合方法的详细信息）

关于关联数组

关联数组是一种不限制大小的关键字/值对集合。每个关键字都是唯一的，并作为保存对应值的元素下标。因此，您可以访问元素而不必知道它们在数组中的位置，也不需要遍历数组。

键的数据类型可以是 PLS_INTEGER 或 VARCHAR2（长度）。

如果键的数据类型是 PLS_INTEGER，则该关联数组就是按整数索引，并且它是密集型（也就是说，元素之间不存在间隔），在第一个元素和最后一个元素之间每个元素都可以被定义，并且它们都具有一个值（可以是 NULL）。

如果关键字的类型是 VARCHAR2（长度），则该关联数组就是按字符串（长度为字符串）索引的，并且它是稀疏型数组；也就是说，元素之间存在间隔。

遍历密集型关联数组时，您不需要注意元素间的间隔；但在遍历稀疏型关联数组时需要。

要为关联数组的元素赋值为，可以使用赋值的运算符：

array_name(key) := value

如果 key 不在数组中，该赋值的语句会将 key-value 对添加到数组中。否则，该语句会将 array_name(key) 的值更改为 value。

关联数组非常适合用户临时存储数据。它们不需要使用表所需要的磁盘空间或网络操作。但是，由于关联数组用于临时存储，因此无法使用 DML 语句对其进行处理。

如果您在数据包中声明关联数组，并在数据包体中为变量赋值，那么该关联数组将在整个数据库会话生命周期中存在。否则，它仅存在于您用来声明它的子程序的生命周期中。

另请参阅： Oracle Database PL/SQL Language Reference（了解有关关联数组的详细信息）

声明关联数组

要声明关联数组，请先声明一个关联数组类型，然后声明一个该类型的变量。

以下代码显示了最简单的语法：

TYPE array_type IS TABLE OF element_type INDEX BY key_type;

array_name  array_type;

一种声明关联数组的有效方式是使用游标，如以下过程所示。该过程以最简单的形式使用每个必要的语句，但提供了有关其完整语法的参考。

要使用游标声明关联数组，请执行以下操作：

1. 在声明部分中：

   1. 声明游标：

       CURSOR cursor_name IS query;
      

      有关已申报的完整游标声明语法，请参阅 Oracle Database PL/SQL Language Reference。

   2. 声明关联数组类型：

       TYPE array_type IS TABLE OF cursor_name%ROWTYPE
         INDEX BY { PLS_INTEGER | VARCHAR2 length }
      

      有关声明关联数组类型的完整语法，请参阅 Oracle Database PL/SQL Language Reference。

   3. 声明一个该类型的关联数组变量：

       array_name  array_type;
      

      有关声明变量的完整语法，请参阅 Oracle Database PL/SQL Language Reference。

示例 5-9 使用前面的过程声明了两个关联数组 employees_jobs 和 jobs_，然后在不使用游标的情况下声明了第三个关联数组 job_titles。前两个数组按整数索引，第三个数组按字符串索引。

注： employees_jobs_cursor 声明中的 ORDER BY 子句决定了关联数组 employee_jobs 的元素存储顺序。

示例 5-9 声明关联数组

DECLARE
  -- Declare cursor:

  CURSOR employees_jobs_cursor IS
    SELECT FIRST_NAME, LAST_NAME, JOB_ID
    FROM EMPLOYEES
    ORDER BY JOB_ID, LAST_NAME, FIRST_NAME;

  -- Declare associative array type:

  TYPE employees_jobs_type IS TABLE OF employees_jobs_cursor%ROWTYPE
    INDEX BY PLS_INTEGER;

  -- Declare associative array:

  employees_jobs  employees_jobs_type;

  -- Use same procedure to declare another associative array:

  CURSOR jobs_cursor IS
    SELECT JOB_ID, JOB_TITLE
    FROM JOBS;

  TYPE jobs_type IS TABLE OF jobs_cursor%ROWTYPE
    INDEX BY PLS_INTEGER;

  jobs_  jobs_type;

-- Declare associative array without using cursor:

  TYPE job_titles_type IS TABLE OF JOBS.JOB_TITLE%TYPE
    INDEX BY JOBS.JOB_ID%TYPE;  -- jobs.job_id%type is varchar2(10)

  job_titles  job_titles_type;

BEGIN
  NULL;
END;
/

另请参见：

• “关于游标”

• 有关关联数组声明语法的Oracle Database PL/SQL Language Reference

填充关联数组

填充密集型关联数组的最有效方法通常是使用带 BULK COLLECT INTO 子句的 SELECT 语句。

注：如果密集的关联数组太大，以至于 SELECT 语句返回的结果集太大而无法容纳在内存中，则不要使用 SELECT 语句。而应使用游标填充数组，并使用子句 BULK COLLECT INTO 和 LIMIT 填充 FETCH 语句。有关将 FETCH 语句与 BULK COLLECT INTO 子句结合使用的信息，请参阅 Oracle Database PL/SQL Language Reference。

不能使用 SELECT 语句填充稀疏关联数组（例如“声明关联数组”中的 job_titles）。而是必须在循环语句内使用赋值语句。有关循环语句的信息，请参阅“控制程序流”。示例 5-10 使用 SELECT 语句填充按整数索引的关联数组 employees_jobs 和 jobs_。然后它在一个语句中使用赋值语句填充按字符串索引的关联数组 job_titles。

示例 5-10 填充关联数组

-- Declarative part from Example 5-9 goes here.

BEGIN
  -- Populate associative arrays indexed by integer:

SELECT FIRST_NAME, LAST_NAME, JOB_ID BULK COLLECT INTO employees_jobs
  FROM EMPLOYEES ORDER BY JOB_ID, LAST_NAME, FIRST_NAME;

SELECT JOB_ID, JOB_TITLE BULK COLLECT INTO jobs_ FROM JOBS;

  -- Populate associative array indexed by string:

  FOR i IN 1..jobs_.COUNT() LOOP
    job_titles(jobs_(i).job_id) := jobs_(i).job_title;
  END LOOP;
END;
/

另请参阅：“关于游标”

遍历密集型关联数组

密集型关联数组（按整数编制索引）的元素间不存在任何间隔，在第一个元素和最后一个元素之间不存在每个元素都定义，并且都具有一个值（可以是 NULL）。

可以使用 FOR LOOP 语句遍历密集数组，如 Example 5-11 中所示。

在示例 5-10 中的可执行部分插入到用来填充 employees_jobs 数列的代码之后，示例 5-11 中的 FOR LOOP 语句将会以元素的存储顺序输出 employees_jobs 数组中的元素。这些节点的存储顺序由用于声明 employees_jobs_cursor 的 ORDER BY 子句决定（请参阅示例 5-9 ）。

FOR LOOP 语句 employees_jobs.COUNT 调用了一个集合方法以返回数组中元素的数量。有关 COUNT 的详细信息，请参阅 Oracle Database PL/SQL Language Reference。

示例 5-11 遍历密集型关联数组

-- Code that populates employees_jobs must precede this code:

FOR i IN 1..employees_jobs.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE(
    RPAD(employees_jobs(i).first_name, 23) ||
    RPAD(employees_jobs(i).last_name,  28) ||     employees_jobs(i).job_id);
  END LOOP;

结果：

William                Gietz                       AC_ACCOUNT
Shelley                Higgins                     AC_MGR
Jennifer               Whalen                      AD_ASST
Steven                 King                        AD_PRES
Lex                    De Haan                     AD_VP
Neena                  Kochhar                     AD_VP
John                   Chen                        FI_ACCOUNT
...
Jose Manuel            Urman                       FI_ACCOUNT
Nancy                  Greenberg                   FI_MGR
Susan                  Mavris                      HR_REP
David                  Austin                      IT_PROG
...
Valli                  Pataballa                   IT_PROG
Michael                Hartstein                   MK_MAN
Pat                    Fay                         MK_REP
Hermann                Baer                        PR_REP
Shelli                 Baida                       PU_CLERK
...
Sigal                  Tobias                      PU_CLERK
Den                    Raphaely                    PU_MAN
Gerald                 Cambrault                   SA_MAN
...
Eleni                  Zlotkey                     SA_MAN
Ellen                  Abel                        SA_REP
...
Clara                  Vishney                     SA_REP
Sarah                  Bell                        SH_CLERK
...
Peter                  Vargas                      ST_CLERK
Adam                   Fripp                       ST_MAN
...
Matthew                Weiss                       ST_MAN

遍历稀疏型关联数组

稀疏关联数组（按字符串进行索引）的元素间可能会有间隙。

可以使用 WHILE LOOP 语句对其进行遍历，如 Example 5-12 中所示。

要运行 Example 5-12 中的代码（该代码输出 job_titles 数组的元素），请完成以下步骤：

1. 在 Example 5-9 中的声明部分末尾，插入此变量声明：

    i jobs.job_id%TYPE;
   

2. 在示例 5-10 中的可执行部分，将 job_titles 数组的代码插入到用来填充 Example 5-12 数组的代码之后。

示例 5-12 遍历稀疏关联数组

/* Declare this variable in declarative part:

   i jobs.job_id%TYPE;

   Add this code to the executable part,
   after code that populates job_titles:
*/

i := job_titles.FIRST;

WHILE i IS NOT NULL LOOP
  DBMS_OUTPUT.PUT_LINE(RPAD(i, 12) || job_titles(i));
  i := job_titles.NEXT(i);
END LOOP;

结果：

AC_ACCOUNT  Public Accountant
AC_MGR      Accounting Manager
AD_ASST     Administration Assistant
AD_PRES     President
AD_VP       Administration Vice President
FI_ACCOUNT  Accountant
FI_MGR      Finance Manager
HR_REP      Human Resources Representative
IT_PROG     Programmer
MK_MAN      Marketing Manager
MK_REP      Marketing Representative
PR_REP      Public Relations Representative
PU_CLERK    Purchasing Clerk
PU_MAN      Purchasing Manager
SA_MAN      Sales Manager
SA_REP      Sales Representative
SH_CLERK    Shipping Clerk
ST_CLERK    Stock Clerk
ST_MAN      Stock Manager

示例 5-12 包含两个集合方法调用 job_titles.FIRST 和 job_titles.NEXT(i)。其中，job_titles.FIRST 用于返回 job_titles 的第一个元素，而 job_titles.NEXT(i) 用于返回下标位于 i 之后的元素。有关 FIRST 的详细信息，请参阅 Oracle Database PL/SQL Language Reference。有关 NEXT 的详细信息，请参阅 Oracle Database PL/SQL Language Reference。