您当前的位置: 首页 > 数据库教程 > Oracle教程 > Sql与oracle中null值

Sql与oracle中null值

作者:不详 来源:网络 发布时间: 2014-08-24 20:05 点击:
1 null值的介绍 NULL 是数据库中特有的数据类型,当一条记录的某个列为 NULL ,则表示这个列的值是未知的、是不确定的。既然是未知的,就有无数种的可能性。因此, NULL 并不是一个确定的值。 这是 NULL 的由来、也是 NULL 的基础,所有和 NULL 相关的操作的结果都可以

Sql与oracle中null值

  1 null值的介绍

  NULL 是数据库中特有的数据类型,当一条记录的某个列为 NULL ,则表示这个列的值是未知的、是不确定的。既然是未知的,就有无数种的可能性。因此, NULL 并不是一个确定的值。 这是 NULL 的由来、也是 NULL 的基础,所有和 NULL 相关的操作的结果都可以从 NULL 的概念推导出来。

  2 oracle中的null值介绍

  在不知道具体有什么数据的时候,即未知,可以用NULL, 称它为空,ORACLE中,含有空值的表列长度为零。允许任何一种数据类型的字段为空,除了以下两种情况:

  a、主键字段(primary key);

  b、定义时已经加了NOT NULL限制条件的字段

  3 Oracle中null值说明:

  a、等价于没有任何值、是未知数。

  b、NULL与0、空字符串、空格都不同。

  c、对空值做加、减、乘、除等运算操作,结果 仍为空。

  d、NULL的处理使用NVL函数。

  e、比较时使用关键字用“is null”和“is not null”。

  f、空值不能被索引,所以查询时有些符合条件的数据可能查不出来, count(expr)中,用nvl(列名,0)处理后再查。

  g、排序时比其他数据都大(索引默认是降序排列,小→大), 所以NULL值总是排在最后。

  IS NULL 和IS NOT NULL 是不可分割的整体,改为IS 或IS NOT都是错误的,从上面我们看到了NULL 和空字符串的区别。

  任何和NULL 的比较操作,如<>、=、<=等都返回UNKNOWN(这里的unknown就是null,它单独使用和布尔值false类似).判断和比较规则总结如下:

  判断和比较规则总结表

  


  例如:使用方法:

  SQL> select 1 from dual where null=null;

  没有查到记录

  SQL> select 1 from dual where null="';

  没有查到记录

  SQL> select 1 from dual where ''='';

  没有查到记录

  SQL> select 1 from dual where null is null;

  1

  ---------

  1

  SQL> select 1 from dual where nvl(null,0)=nvl(null,0);

  1

  ---------

  1

  4、null做一些算术运算,比如+,-,*,/等,结果 还是null,但是对于连接操作符||,null忽略,concat函数也忽略null

  SQL> select null || 'abc' from dual;

  NUL

  ---

  abc

  SQL> select concat(null,'abc') from dual;

  CON

  ---

  abc

  SQL> select null+10 from dual;

  NULL+10

  ----------

  5、null相关函数规则

  Oracle有nvl、nvl2、nullif、coalesce等函数专门处理null

  5.1 nvl(expr1,expr2)

  描述:如果expr1是null,那么用expr2作为返回值,不是null则返回expr1.expr1与expr2一般是类型相同的,如果类型不同则会采用自动转换,转换失败则报错。

  SQL*PLUS中数值类型右对齐,字符左对齐;通过第三条语句可以看出null和‘’还是有区别的。

  SQL> select nvl(null,0) from dual;

  NVL(NULL,0)

  -----------

  0

  SQL> select nvl(to_char(null),0) from dual;

  N

  -

  0

  SQL> select nvl('',0) from dual;

  N

  -

  0

  5.2 nvl2函数

  语法:nvl2(expr1,expr2,expr3)

  描述:expr1如果是null,则返回expr3,否则返回expr2

  expr2和expr3类型不同,expr3类型转换为expr2类型

  SQL> select nvl2(null,'1',2) from dual;

  N

  -

  2

  expr2为null,返回值类型和expr3一致

  SQL> select nvl2(null,null,2) from dual;

  NVL2(NULL,NULL,2)

  -----------------

  2

  SQL> select nvl2(null,null,'2') from dual;

  N

  -

  2

  不同类型之间的转换,自动转换不成功,则报错

  SQL> select nvl2(null,1,'b') from dual;

  select nvl2(null,1,'b') from dual

  *

  ERROR at line 1:

  ORA-01722: invalid number(无效数字)

  5.3 nullif函数

  语法:nullif(expr1,expr2)

  描述:判断expr1和expr2是否相等,若相等则返回null,否则返回expr1.要求expr1与expr2类型必须相同

  SQL> select nullif(1,3) from dual;

  NULLIF(1,3)

  -----------

  1

  SQL> select nullif(1,1) from dual;

  NULLIF(1,1)

  -----------

  SQL> select nullif('1',1) from dual;

  select nullif('1',1) from dual

  *

  ERROR at line 1:

  ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

  SQL> select nullif('ab','ab ') from dual;

  NU

  --

  SQL> select nullif(null,1) from dual;

  select nullif(null,1) from dual

  *

  ERROR at line 1:

  ORA-00932: inconsistent datatypes: expected - got CHAR

  SQL> select nullif(to_char(null),'1') from dual;

  N

  -

  5.4 coalesce函数

  语法:coalesce(expr1,expr2,…,exprn)

  描述:从左到右返回第一个为非null的值,若所有的列表元素都为null,则返回null。要求所有都必须为同一类型。

  SQL> select coalesce(null,null,null) from dual;

  C

  -

  SQL> select coalesce(null,1,2) from dual;

  COALESCE(NULL,1,2)

  ------------------

  1

  SQL> select coalesce(1,'1',1) from dual;

  select coalesce(1,'1',1) from dual

  *

  ERROR at line 1:

  ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

  6 null与索引

  Oracle中的B*Tree索引,并不存储全为null的列,

  虽然在表中建立了符合UNIQUE 索引,但是全为null的行还是可以插入的,而不是全为null的重复行则不可以插入。因为在UNIQUE约束中,(null,null)和(null,null)是不同的,当然在其他一些情况,比如说分组、集合操作中都认为全是null是相等的

  SQL> create table t(a number,b number);

  Table created.

  SQL> create unique index idx_t on t(a,b);

  Index created.

  SQL> insert into t values(null,null);

  1 row created.

  SQL> insert into t values(1,null);

  1 row created.

  SQL> insert into t values(null,1);

  1 row created.

  SQL> commit;

  SQL> insert into t values(1,null);

  insert into t values(1,null)

  *

  ERROR at line 1:

  ORA-00001: unique constraint (SCOTT.IDX_T) violated

  7 null的排序

  order by默认升序(asc),这时候null是排在最后的,如果指定降序那么null是排在最前面的,认为null最大。

  但是可以用nulls first和nulls last进行调整。

  SQL> select * from emp

  2 order by comm asc;

  SQL> select * from emp

  2 order by comm desc;

  SQL> select *from emp

  2 order by comm asc nulls first;

  SQL> select *from emp

  2 order by comm desc nulls last;

  8 null与性能的关系

  Not null约束,定义约束是要付出消耗性能的代价的,由下面的测试可以看出虽然约束检查的很快,但是有时候还是很消耗资源的,至少在这个例子上是这样的,不需要not null约束,除非必要,不要乱定义约束。

  SQL> set serveroutput on

  SQL> declare

  2 v_value number not null :=0;

  3 start_time number;

  4 end_time number;

  5 begin

  6 start_time :=DBMS_UTILITY.GET_TIME;

  7 FOR i in 0..100000000 LOOP

  8 v_value :=i;

  9 end LOOP;

  10 end_time :=DBMS_UTILITY.GET_TIME;

  11 DBMS_OUTPUT.PUT_LINE(end_time-start_time);

  12 END;

  13 /

  1043

  PL/SQL procedure successfully completed.

  SQL> declare

  2 v_value number;

  3 start_time number;

  4 end_time number;

  5 begin

  6 start_time :=DBMS_UTILITY.GET_TIME;

  7 FOR i IN 0..100000000 LOOP

  8 v_value :=i;

  9 end LOOP;

  10 end_time :=DBMS_UTILITY.GET_TIME;

  11 DBMS_OUTPUT.PUT_LINE(end_time-start_time);

  12 END;

  13 /

  767

  9 动态语句中的绑定变量与null

  在PL/SQL中动态SQL和动态PL/SQL经常使用绑定变量,这个绑定变量有个要求,就是不能直接传入字面量null值,因为PL/SQL中动态语句要求传入的绑定变量必须是SQL类型,而字面量null是无类型的,null字面量传入是不可以的。当然可以采用多种方法,如果一定要传入null,则可以将null改为空字符串、TO_NUMBER,TO_CHAR,TO_DATE等函数进行转换,或定义一个未初始化的变量、直接传入变量等。

  SQL> create table test(id number,name varchar2(10),birth date);

  Table created.

  SQL> insert into test values(1,'aa',SYSDATE);

  1 row created.

  SQL> insert into test values(null,'aa',SYSDATE);

  1 row created.

  SQL> COMMIT;

  Commit complete.

  SQL> declare

  2 v_sql varchar2(4000);

  3 begin

  4 v_sql :='update test set birth=:vbirth where id is null';

  5 execute immediate v_sql using null;

  6 commit;

  7 end;

  8 /

  execute immediate v_sql using null;

  *

  ERROR at line 5:

  ORA-06550: line 5, column 31:

  PLS-00457: expressions have to be of SQL types

  ORA-06550: line 5, column 1:

  PL/SQL: Statement ignored

  SQL> declare

  2 v_sql varchar2(4000);

  3 begin

  4 v_sql :='update test set birth=:vbirth where id is null';

  5 execute immediate v_sql using '';

  6 commit;

  7 end;

  8 /

  PL/SQL procedure successfully completed.

  10 sqlserver 中null值介绍

  创建示例表, Script 如下:

  create table dbo. cassaba_null

  (

  column1 nvarchar ( 50) not null,

  column2 nvarchar ( 50) null

  )

  go

  insert into dbo. cassaba_null values ( '1' , null)

  insert into dbo. cassaba_null values ( '2' , 'string' )

  insert into dbo. cassaba_null values ( '3' , '' )

  go

  a. 使用 =null / <>null 默认情况下的确不能使用 =null / <> null 来判断 null 值如此。实际上 SQL Server 可以 使用 SET ANSI_NULLS { ON | OFF } 设定来控制 =null / <>null 的行为。

  当 SET ANSI_NULLS 为 ON 时,即使 column_name 中包含空值,使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。

  即使 column_name 中包含非空值,使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行

  但是当 SET ANSI_NULLS 为 OFF 时,等于 (=) 和不等于 (<>) 比较运算符不遵守 ISO 标准。

  使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。

  使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。

  此外,使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。

  b. 改变 null 值的连接行为

  SQL Server 提供 SET CONCAT_NULL_YIELDS_NULL

  { ON | OFF } 来控制 null 与其它字符串连接的行为。

  当 SET CONCAT_NULL_YIELDS_NULL 为 ON 时,串联空值与字符串将产生 NULL 结果。例如, SELECT 'abc' + NULL 将生成 NULL 。

  当 SET CONCAT_NULL_YIELDS_NULL 为 OFF 时,串联空值与字符串将产生字符串本身(空值作为空字符串处理)。例如, SELECT 'abc' + NULL 将生成 abc 。

  如果未指定 SET CONCAT_NULL_YIELDS ,则应用 CONCAT_NULL_YIELDS_NULL 数据库选项的设置。

  注:在 SQL Server 的未来版本中, CONCAT_NULL_YIELDS_NULL 将始终为 ON ,而且将该选项显式设置为 OFF 的任何应用程序都将产生一个错误。

  c. 变量的默认值与 null 值

  命名一个变量后,如果没有给它赋初始值,它的值就是 null 。有时候需要注意初始 null 值和通过 select 语句给变量后期赋 null 的区别。因为此 ‘null’ 非彼 ‘null’ 。

  d. 子查询中的 null

  子查询中出现的 null 值经常会被我们忽视,先查看下面的例子。

  e. Case 语句中的 null

  Case 中的 when 语句注意不要写成 when null, 否则得不到想要的结果。

  下面的第 1 条 sql 错误, 2 , 3 正确。

  f. 与 null 相关的函数 ISNULL ISNULL 检测表达式是否为 NULL ,如果是的话替换 NULL 值为另外一个值 COALESCE COALESCE 函数返回指定表达式列表的第一个非 NULL 值 NULLIF 当指定的两个表达式有相同值的时候 NULLIF 返回 NULL 值,否则返回第一个表达式的值

  示例 1 : set ansi_nulls on

  declare @test1 nvarchar ( 10)

  if ( @test1 = null)

  select 1

  else

  select 2

  ---------------------------------------------------------------------

  结果返回 2

  示例 2 : set ansi_nulls off

  declare @test1 nvarchar ( 10)

  if ( @test1 = null)

  select 1

  else

  select 2

  ---------------------------------------------------------------------

  结果返回 1

  示例 3 :

  set ansi_nulls on

  select * from dbo. cassaba_null where column2 != null

  ---------------------------------------------------------------------

  无记录返回

  示例 4 :

  set ansi_nulls off

  select * from dbo. cassaba_null where column2 != null

  ---------------------------------------------------------------------

  返回第 2 , 3 条记录

  如果不希望每次都判断 null, 可以使用 isnull 函数来达到每次把 null 自动替换为空字符串进行连接的效果。

  示例 5 :

  declare @test nvarchar ( 50)

  select isnull ( @test, '' ) + 'extend'

  go

  select column1 + isnull ( column2, '' ) as column3 from dbo. cassaba_null

  go

  ---------------------------------------------------------------------

  示例 6 :

  declare @test nvarchar ( 50)

  -- 无符合条件的语句 , 保持默认值 null

  select @test= column2 from dbo. cassaba_null where column1 = '4'

  -- 有符合条件的语句,返回的栏位值为 null ,并且赋给 @test

  select @test= column2 from dbo. cassaba_null where column1 = '1'

  如果后面的代码使用 @test 的值是否为 null 来判断有没有匹配的记录,则可能发生错误。碰到这种状况,我们同样可以使用 isnull 函数来避免这个问题。

  select @test= isnull ( column2, '' ) from dbo. cassaba_null where column1 = '1'

  如上面的语句,即使有匹配的记录返回 null, 也会变成空字符串赋给 @test 了。这样就把两种情况区分开了。

  示例 7 : set ansi_nulls off

  select * from cassaba_null a where a. column2 = ( select b. column2 from dbo. cassaba_null b where b. column1 = 1)

  ---------------------------------------------------------------------

  不管上面 ansi_nulls 设置为 on 还是 off ,始终没有记录返回。我们修改一下查询语句:

  select * from cassaba_null a where a. column2 in ( select b. column2 from dbo. cassaba_null b where b. column1 = 1)

  这样,如果 ansi_nulls 设置为 on , 则没有记录返回。 如果设置为 off ,则会返回一条记录。

  对于这种状况,如果我们确定不需要返回 null 值记录,则使用下面的 sql 语句:

  select * from cassaba_null a where a. column2 in( select b. column2 from dbo. cassaba_null b where b. column1 = 1

  and b. column2 is not null)

  反之,使用下面的语句:

  select * from cassaba_null a where a. column2 in( select b. column2 from dbo. cassaba_null b where b. column1 = 1

  and b. column2 is not null) or a. column2 is null

  10 Sql与oracle中null值的不同

  a.在SQL Server中与oracle正相反,NULL值会被认为是一个无穷小的值,所以如果按照升序排列的话,则会被排在最前面

  b. SQL Server和Oracle中对插入数据值包含空的处理有所差异,在SQL Server中,我们可以把表字段设计为非空,但我们仍然可以通过下面语句执行插入操作:

  INSERT INTO Table (TestCol) VALUES(‘’)

  其中的TestCol字段,在设计的时候,已经被设计为NOT NULL在sql server中,null和空格是不同的,也就是说,上面的语句插入的是一个空,但并不是NULL,只有当我们的插入语句中没有该字段的时候,才会被认为违反非空的条件约束,如果把NULL翻译成“空”的话,可能就会很容易搞混了。此外,如果我们的字段是INT类型的话,如果我们插入空的话,会得到一个0,也就是说,Sql server会自动帮我们处理对空格的转化。

  但是在Oracle中,这个便利便不存在了,必须严格按照规则来进行插入,也就是说,我们再想视图通过插入空来满足NOT NULL的设计约束,已经不能成功了,必须插入实实在在的内容才能符合NOT NULL的约束。

  所以相对来说oracle要比sqlserver限制要多,也就相对严谨
分享到:
本文"Sql与oracle中null值"由远航站长收集整理而来,仅供大家学习与参考使用。更多网站制作教程尽在远航站长站。
顶一下
(0)
0%
踩一下
(0)
0%
[点击 次] [返回上一页] [打印]
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
关于本站 - 联系我们 - 广告合作 - 网站声明 - 友情连接- 网站地图 - 站点地图 - 返回顶部
Copyright © 2007-2013 www.yhzhan.com(远航站长). All Rights Reserved .
远航站长:为中小站长提供最佳的学习与交流平台,提供网页制作与网站编程等各类网站制作教程.
官方QQ:445490277 网站群:26680406 网站备案号:豫ICP备07500620号-4