Sql与oracle中null值浅析

最新推荐文章于 2021-04-14 17:00:55 发布

luoshuifeiluan

最新推荐文章于 2021-04-14 17:00:55 发布

阅读量840

点赞数

分类专栏： oracle 11g数据库

本文链接：https://blog.csdn.net/luoshuifeiluan/article/details/8968862

版权

oracle 11g数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Sql与oracle中null值有哪些不同以及oracle中的优势

1 null值的介绍

NULL 是数据库中特有的数据类型，当一条记录的某个列为NULL ，则表示这个列的值是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此， NULL 并不是一个确定的值。

这是 NULL 的由来、也是 NULL 的基础，所有和 NULL 相关的操作的结果都可以从 NULL 的概念推导出来。

2 oracle中的null值介绍

在不知道具体有什么数据的时候，即未知，可以用NULL，我们称它为空，ORACLE中，含有空值的表列长度为零。

允许任何一种数据类型的字段为空，除了以下两种情况：

2.1主键字段（primary key）；

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

3 Oracle中null值说明：

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

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

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

3.4NULL的处理使用NVL函数。

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

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

3.7排序时比其他数据都大（索引默认是降序排列，小→大），所以NULL值总是排在最后。

使用方法：

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;

---------

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

---------

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

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

判断和比较规则总结表

A值	条件	结果
10	A IS NULL	FALSE
10	A IS NOT NULL	TRUE
NULL	A IS NULL	TRUE
NULL	A IS NOT NULL	FALSE
10	A=NULL	UNKNOWN
10	A!=NULL	UNKNOWN
NULL	A=NULL	UNKNOWN
NULL	A!=NULL	UNKNOWN
NULL	A=10	UNKNOWN
NULL	A!=10	UNKNOWN

SQL> select null || 'abc' from dual;4、null做一些算术运算，比如+，-，*，/等，结果还是null，但是对于连接操作符||，null忽略concat函数也忽略null

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、 decode等函数专门处理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)

-----------

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

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

5.2 nvl2函数

语法：nvl2（expr1,expr2,expr3)

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

Expr2和expr3类型不同，expr3类型转换为expr2类型

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

Expr2为null，返回值类型和expr3一致

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

NVL2(NULL,NULL,2)

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

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

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

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)

-----------

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: expectedCHAR got NUMBER

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

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') fromdual;

5.4 coalesce函数

语法：coalesce（expr1,expr2,…,exprn)

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

SQL> select coalesce(null,null,null) fromdual;

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

COALESCE(NULL,1,2)

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

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

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

ERROR at line 1:

ORA-00932: inconsistent datatypes: expectedNUMBER 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,namevarchar2(10),birth date);

Table created.

SQL> insert into testvalues(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 whereid 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 whereid is null';

5 execute immediate v_sql using '';

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed.

10 sqlserver 中null值介绍

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

当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHEREcolumn_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 的行。

2.改变 null 值的连接行为

SQL Server 提供 SET CONCAT_NULL_YIELDS_NULL

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

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

当 SETCONCAT_NULL_YIELDS_NULL 为 OFF 时，串联空值与字符串将产生字符串本身（空值作为空字符串处理）。例如， SELECT 'abc' + NULL 将生成 abc 。

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

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

3. 变量的默认值与 null 值

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

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

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

(1)select case column2 when null then 'a' else 'b' endas column3 from cassaba_null

(2)select case isnull ( column2, 'a' ) when'a' then 'a' else 'b' end as column3 from cassaba_null

(3)select case when column2 is null then 'a' else'b' end as column3 from cassaba_null

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

11 Sql与oracle中null值的不同

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

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

INSERT INTO Table (TestCol) VALUES(‘’)

其中的TestCol字段，在设计的时候，已经被设计为NOTNULL

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

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

所以相对来说oracle要比sqlserver限制要多，也就相对严谨

luoshuifeiluan

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Sql与oracle中null值浅析

Sql与oracle中null值有哪些不同以及oracle中的优势1 null值的介绍 NULL 是数据库中特有的数据类型，当一条记录的某个列为NULL ，则表示这个列的值是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此， NULL 并不是一个确定的值。这是 NULL 的由来、也是 NULL 的基础，所有和 NULL 相关的操作的结果都可以从 NULL 的概念
复制链接

扫一扫