如果在定义VARRAY的时候带上NOT NULL限制,那么这个VARRAY的元素就不能为NULL.如下定义:CREATE OR REPLACE TYPE integer_varray
AS VARRAY(5) OF INTEGER NOT NULL;
/ 然后有一个PLSQL块如下:DECLARE -- Declare and initialize a null set of rows.
varray_integer INTEGER_VARRAY := integer_varray();BEGIN -- Loop through all records to print the varray contents.
FOR i IN 1..varray_integer.LIMIT LOOP -- Initialize row.
varray_integer.EXTEND; /*没有赋值,如果不赋值是NULL的话,应该编译错误啊,结果是顺利通过编译*/ END LOOP; -- Print to console how many rows are initialized.
dbms_output.put ('Integer Varray Initialized ');
dbms_output.put_line('['||varray_integer.COUNT||']');
--varray_integer(1):=null;
FOR i IN 1..varray_integer.COUNT LOOP -- Print the contents.
dbms_output.put ('Integer Varray ['||i||'] ');
dbms_output.put_line('['||varray_integer(i)||']');
if(varray_integer(i) is null) then /*本来是认为这里应该不执行,结果会打印出来*/ dbms_output.put_line('Integer Varray ['||i||'] '|| 'is null');
end if; END LOOP;END;
/ 运行结果如下:/*运行也正常,显示元素为NULL,于定义矛盾*/Integer Varray Initialized [5]
Integer Varray [1] []
Integer Varray [1] is null
Integer Varray [2] []
Integer Varray [2] is null
Integer Varray [3] []
Integer Varray [3] is null
Integer Varray [4] []
Integer Varray [4] is null
Integer Varray [5] []
Integer Varray [5] is null 测试环境为:BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
AS VARRAY(5) OF INTEGER NOT NULL;
/ 然后有一个PLSQL块如下:DECLARE -- Declare and initialize a null set of rows.
varray_integer INTEGER_VARRAY := integer_varray();BEGIN -- Loop through all records to print the varray contents.
FOR i IN 1..varray_integer.LIMIT LOOP -- Initialize row.
varray_integer.EXTEND; /*没有赋值,如果不赋值是NULL的话,应该编译错误啊,结果是顺利通过编译*/ END LOOP; -- Print to console how many rows are initialized.
dbms_output.put ('Integer Varray Initialized ');
dbms_output.put_line('['||varray_integer.COUNT||']');
--varray_integer(1):=null;
FOR i IN 1..varray_integer.COUNT LOOP -- Print the contents.
dbms_output.put ('Integer Varray ['||i||'] ');
dbms_output.put_line('['||varray_integer(i)||']');
if(varray_integer(i) is null) then /*本来是认为这里应该不执行,结果会打印出来*/ dbms_output.put_line('Integer Varray ['||i||'] '|| 'is null');
end if; END LOOP;END;
/ 运行结果如下:/*运行也正常,显示元素为NULL,于定义矛盾*/Integer Varray Initialized [5]
Integer Varray [1] []
Integer Varray [1] is null
Integer Varray [2] []
Integer Varray [2] is null
Integer Varray [3] []
Integer Varray [3] is null
Integer Varray [4] []
Integer Varray [4] is null
Integer Varray [5] []
Integer Varray [5] is null 测试环境为:BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
解决方案 »
- aix 上创建oracle实例,配置em失败
- 请问一个oracle 分组查询的问题???
- Oracle中把一个Owner下面的所有表导入到别外一个Owner中请问怎么实现啊 谢谢
- 我服务里为何没有 Management Server
- 全表扫描次数太多,如何分析出是哪条语句导致的?
- 如何导出oracle数据库表的结构
- 麻烦看看这句话怎么错了啊?
- 字段number(38)取出值的接收(proc)?
- 怎么回事,请高手看看!
- 求大神指点,oracle 触发器 员工签到时判断签到是否成功并更新到签到表中怎么写?
- SQL 中如何查询一个字符串在指定离散值列表中或一个‘-’分开的范围值之内
- 单行子查询返回多于一个行的问题
TYPE card_table IS TABLE OF VARCHAR2(5 CHAR) NOT NULL; -- Declare and initialize a nested table with three rows.
cards CARD_TABLE := card_table();BEGIN -- Print title.
dbms_output.put_line(
'Nested table initialized as nulls.');
dbms_output.put_line(
'----------------------------------'); -- Loop through the three records.
FOR i IN 1..3 LOOP
cards.extend;
END LOOP; -- Loop through the three records to print the varray contents.
FOR i IN 1..3 LOOP dbms_output.put_line('Cards ['||i||'] '
|| '['||cards(i)||']');
if(cards(i) is null) then
/*本来是认为这里应该不执行,结果会打印出来*/
dbms_output.put_line('Cards ['||i||'] is null' );
end if; END LOOP;END;
/
这里需要区别的是值为null和空指针。对于这里not null指的是不能设置null值,而你语句里extend但是没有赋值,是空的一个指针,所以是和值为null有区别的,这是我的理解当定义为not null的时候,应该是varray_integer(1):=null 或者 varray_integer(1):=''这样会出错的但是你extend以后,数组的元素是没有赋值的,所以不会出错;
而且if(varray_integer(i) is null) then 的条件是成立的.感觉是当定义为not null的时候,直接给varray_integer(1):=null 肯定会出错.