Home >Database >Mysql Tutorial >Oracle 中VARRAY的 NOT NULL之惑

Oracle 中VARRAY的 NOT NULL之惑

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal
2016-06-07 17:10:08998browse

如果在定义VARRAY的时候带上NOT NULL限制,那么这个VARRAY的元素就不能为NULL. 如下定义: CREATE OR REPLACE TYPE integer_varr

如果在定义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

linux

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn