Sql与oracle中null值
Sql与oracle中null值
发布时间:2015-06-05 来源:查字典编辑
摘要:1null值的介绍NULL是数据库中特有的数据类型,当一条记录的某个列为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与oracle中null值1

例如:使用方法:

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限制要多,也就相对严谨

推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
相关阅读
网友关注
最新Oracle教程学习
热门Oracle教程学习
编程开发子分类