oracle - 为什么在 PL/SQL 中检查空关联数组会失败？

Question

我有一个由表列的行类型类型创建的关联数组。

举个例子，是这样的(表名不同，但结构相同):

这是表的DDL

CREATE TABLE employees
  (
     id     NUMBER,
     name   VARCHAR2(240),
     salary NUMBER
  ); 

这是我的程序正在做的事情:

DECLARE
    TYPE table_of_emp
      IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
    emp TABLE_OF_EMP;
BEGIN
    IF emp IS NULL THEN
      dbms_output.Put_line('Null associative array');
    ELSE
      dbms_output.Put_line('Not null');
    END IF;
END; 

我认为这应该会导致打印“空关联数组”。然而， if条件失败，执行跳转到 else 部分。

现在，如果我输入 for循环打印集合值

DECLARE
    TYPE table_of_emp
      IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
    emp TABLE_OF_EMP;
BEGIN
    IF emp IS NULL THEN
      dbms_output.Put_line('Null associative array');
    ELSE
      dbms_output.Put_line('Not null');

      FOR i IN emp.first..emp.last LOOP
          dbms_output.Put_line('Emp name: '
                               || Emp(i).name);
      END LOOP;
    END IF;
END; 

然后程序单元引发异常，引用 for 循环行

ORA-06502: PL/SQL: Numeric or value error

我认为这是因为空关联数组。是否由于空关联数组而引发错误？

那么为什么第一次检查失败呢？我究竟做错了什么？

数据库服务器为Oracle 11g EE(版本11.2.0.3.0 64位)

Answer 1

我认为这应该会导致打印“空关联数组”。这种假设对于关联数组是错误的。它们在声明时存在，但为空。它适用于其他类型的 PL/SQL 集合:

Until you initialize it, a nested table or varray is atomically null; the collection itself is null, not its elements. To initialize a nested table or varray, you use a constructor, a system-defined function with the same name as the collection type. This function constructs collections from the elements passed to it.

You must explicitly call a constructor for each varray and nested table variable. Associative arrays, the third kind of collection, do not use constructors. Constructor calls are allowed wherever function calls are allowed. Initializing and Referencing Collections

相比:

SQL> declare
  2      type varchar2_100_aa is table of varchar2(100) index by binary_integer;
  3      test varchar2_100_aa;
  4  begin
  5      test(1) := 'Hello';
  6      dbms_output.put_line(test(1));
  7  end;
  8  /
Hello

PL/SQL procedure successfully completed.

SQL> declare
  2      type varchar2_100_va is varray(100) of varchar2(100);
  3      test varchar2_100_va;
  4  begin
  5      test(1) := 'Hello';
  6      dbms_output.put_line(test(1));
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5

变量数组正确完成:

SQL> declare
  2      type varchar2_100_va is varray(10) of varchar2(100);
  3      test varchar2_100_va;
  4  begin
  5      test := varchar2_100_va(); -- not needed on associative array
  6      test.extend; -- not needed on associative array
  7      test(1) := 'Hello';
  8      dbms_output.put_line(test(1));
  9  end;
 10  /
Hello

PL/SQL procedure successfully completed.

因为关联数组为空 first和 last为空，这就是为什么你的第二个例子导致 ORA-06502: PL/SQL: Numeric or value error :

SQL> declare
  2      type varchar2_100_aa is table of varchar2(100) index by binary_integer;
  3      test varchar2_100_aa;
  4  begin
  5      dbms_output.put_line(test.count);
  6      dbms_output.put_line(coalesce(to_char(test.first), 'NULL'));
  7      dbms_output.put_line(coalesce(to_char(test.last), 'NULL'));
  8      test(1) := 'Hello';
  9      dbms_output.new_line;
 10      dbms_output.put_line(test.count);
 11      dbms_output.put_line(coalesce(to_char(test.first), 'NULL'));
 12      dbms_output.put_line(coalesce(to_char(test.last), 'NULL'));
 13  end;
 14  /
0
NULL
NULL

1
1
1

PL/SQL procedure successfully completed.

编辑 另请注意，关联数组可以是稀疏的。循环遍历 first 之间的数字和 last将为任何稀疏的集合引发异常。而是使用 first和 next 像这样:( Last 和 prev 循环另一个方向。)

SQL> declare
  2      type varchar2_100_aa is table of varchar2(100) index by binary_integer;
  3      test varchar2_100_aa;
  4      i binary_integer;
  5  begin
  6      test(1) := 'Hello';
  7      test(100) := 'Good bye';
  8      dbms_output.put_line(test.count);
  9      dbms_output.put_line(coalesce(to_char(test.first), 'NULL'));
 10      dbms_output.put_line(coalesce(to_char(test.last), 'NULL'));
 11      dbms_output.new_line;
 12  --
 13      i := test.first;
 14      while (i is not null) loop
 15          dbms_output.put_line(to_char(i, '999')  || ' - ' || test(i));
 16          i := test.next(i);
 17      end loop;
 18  end;
 19  /
2
1
100

   1 - Hello
 100 - Good bye

PL/SQL procedure successfully completed.