oracle 为什么在 PL/SQL 中检查空关联数组会失败？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12411991/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is this check for null associative array in PL/SQL failing?
提问by Sathyajith Bhat
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):
举个例子，是这样的（表名不同，但结构相同）：
This is the DDL of the table
这是表的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;
I assume this shouldresult in "Null associative array" being printed. However, the ifcondition fails and the execution jumps to the else part.
我认为这应该会导致打印“空关联数组”。但是，if条件失败，执行跳转到 else 部分。
Now if I put in a forloop to print the collection values
现在，如果我放入一个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;
then the program unit raises an exception, referencing the for loop line
然后程序单元引发异常，引用 for 循环行
ORA-06502: PL/SQL: Numeric or value error
ORA-06502: PL/SQL: 数字或值错误
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?
那么为什么第一次检查失败呢？我究竟做错了什么？
The database server is Oracle 11g EE (version 11.2.0.3.0 64 bit)
数据库服务器为Oracle 11g EE（版本11.2.0.3.0 64位）
采纳答案by Shannon Severance
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:
我认为这应该会导致打印“空关联数组”。这种假设对于关联数组是错误的。它们在声明时存在，但为空。它适用于其他类型的 PL/SQL 集合：
Until you initialize it, a nested tableor varrayis 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
在初始化之前，嵌套表或变量是原子性的 null；集合本身为空，而不是它的元素。要初始化嵌套表或变量数组，请使用构造函数，这是一个与集合类型同名的系统定义函数。此函数根据传递给它的元素构造集合。
您必须为每个 varray 和嵌套表变量显式调用构造函数。关联数组，第三种集合，不使用构造函数。在允许函数调用的任何地方都允许构造函数调用。初始化和引用集合
Compare:
相比：
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
Variable array done correctly:
变量数组正确完成：
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.
Because the associative array is empty firstand lastare null, which is why your second example results in ORA-06502: PL/SQL: Numeric or value error:
因为关联数组为空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.
EDITAlso note that associative arrays can be sparse. Looping over the numbers between firstand lastwill raise an exception for any collection that is sparse. Instead use firstand nextlike so: (Lastand prevto loop the other direction.)
编辑还要注意，关联数组可以是稀疏的。在循环之间的数字first，并last会引发异常的任何集合，是稀疏。而是使用firstandnext像这样：（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.
回答by Ben
I'm not going to answer why the first check is failing. I've never thought of doing anything like that and am quite surprised that it doesn't raise an error.
我不会回答为什么第一次检查失败。我从来没有想过做这样的事情，并且很惊讶它不会引发错误。
The reason why you're getting an exception raised on the loop is, as you've noted, that the index emp.firstdoes not exist.
正如您所指出的，您在循环中引发异常的原因是该索引emp.first不存在。
Rather than checking for nulls, you should really be checking for the existence of this index. Which you can do be using the .exists(i)syntax:
与其检查空值，不如检查该索引是否存在。您可以使用以下.exists(i)语法：
if not emp.exists(emp.first) then
dbms_output.put_line('Nothing in here.');
end if;
