为什么此检查PL/SQL中的空关联数组失败? [英] Why is this check for null associative array in PL/SQL failing?

问题描述

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

I have an associative array created by a type of rowtype of a table column.

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

To give an example, this is how it is(the table names are different, but the structure is the same):

这是表的DDL

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

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

Here's what my procedure is doing:

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部分.

I assume this should result in "Null associative array" being printed. However, the if condition fails and the execution jumps to the else part.

现在，如果我放入for循环以打印收集值

Now if I put in a for loop to print the collection values

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循环行

then the program unit raises an exception, referencing the for loop line

ORA-06502:PL/SQL:数值或值错误

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

我认为

是因为空关联数组.是否由于空关联数组而引发错误?

which I presume is because of the null associative array. Is the error being raised because of null associative array?

那为什么第一次检查失败了?我在做什么错了?

So why is the first check failing then? What am I doing wrong?

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

The database server is Oracle 11g EE (version 11.2.0.3.0 64 bit)

推荐答案

我认为这将导致打印空关联数组".该假设对于关联数组是错误的.它们在声明时存在，但为空.对于其他类型的PL/SQL集合，这将是正确的:

I assume this should result in "Null associative array" being printed. That assumption is wrong for associative arrays. They exist when declared, but are empty. It would be correct for other types of PL/SQL collections:

在初始化之前，嵌套表或 varray 本质上是空的； 集合本身为null，而不是其元素.初始化一个 嵌套表或varray，请使用系统定义的构造函数 与集合类型同名的函数.该功能 从传递给它的元素构造集合.

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.

您必须为每个varray显式调用一个构造函数并嵌套 表变量. 关联数组(第三种集合) 不使用构造函数.无论函数在哪里，都允许构造函数调用 允许通话. 初始化和引用集合

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的原因:

Because the associative array is empty first and last are null, which is why your second example results in 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可以循环另一个方向.)

EDIT Also note that associative arrays can be sparse. Looping over the numbers between first and last will raise an exception for any collection that is sparse. Instead use first and next like so: (Last and prev to loop the other direction.)

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.

这篇关于为什么此检查PL/SQL中的空关联数组失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！