NULL相关的函数-CSDN博客

本文是对NULL值相关的函数的简单总结，包括NVL,DECODE,NVL2，COALESCE,NULLIF,LNNVL,NANVL,SYS_OP_MAP_NONNULL.

下文中的大部分例子都会用到下面的表。

DROP TABLE null_test_tab;

CREATE TABLE null_test_tab (

id NUMBER,

col1 VARCHAR2(10),

col2 VARCHAR2(10),

col3 VARCHAR2(10),

col4 VARCHAR2(10)

);

INSERT INTO null_test_tab values (1, 'ONE', 'TWO', 'THREE', 'FOUR');

INSERT INTO null_test_tab values (2, NULL, 'TWO', 'THREE', 'FOUR');

INSERT INTO null_test_tab values (3, NULL, NULL, 'THREE', 'FOUR');

INSERT INTO null_test_tab values (4, NULL, NULL, 'THREE', 'THREE');

COMMIT;

NVL

该函数有两个参数，当第一个参数为NULL时返回第二个参数，否则返回第一个参数。

测试表null_test_tab，只有第一行的第一列不为空。下面的查询使用NVL函数将NULL值替换为'ZERO'.

SQL> SELECT id, NVL(col1, 'ZERO') AS output FROM null_test_tab ORDER BY id;

ID OUTPUT

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

1 ONE

2 ZERO

3 ZERO

4 ZERO

4 rows selected.

DECODE

该函数不是专门用来处理NULL值的，但有着与NVL函数类似的功能，见下面的例子：

SQL> SELECT id, DECODE(col1, NULL, 'ZERO', col1) AS output FROM null_test_tab ORDER BY id;

ID OUTPUT

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

1 ONE

2 ZERO

3 ZERO

4 ZERO

4 rows selected.

NVL2

该函数接受三个参数。如果第一个参数不为空，则返回第二个参数；否则返回第三个参数。可参见下面的查询来理解NVL2的行为。

SQL> SELECT id, NVL2(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

ID OUTPUT

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

1 TWO

2 THREE

3 THREE

4 THREE

4 rows selected.

COALESCE

该函数是在ORACLE 9i被引入的，它接受多个参数并返回第一个非空值。如果所有参数为空，则返回空值，示例如下：

SQL> SELECT id, COALESCE(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

ID OUTPUT

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

1 ONE

2 TWO

3 THREE

4 THREE

4 rows selected.

NULLIF

该函数也是在ORACLE 9i被引入，它接受两个参数。如果这两个参数相等，则返回空；否则返回第一个参数。

在测试表中，第四行的COL3和COL4相等，所以下面的查询只有该行会返回NULL。

SQL> SELECT id, NULLIF(col3, col4) AS output FROM null_test_tab ORDER BY id;

ID OUTPUT

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

1 THREE

2 THREE

3 THREE

4 rows selected.

LNNVL

该函数早在ORACLE 9i就有了，但直至ORACLE 11g才在文档中出现。其用途是在WHERE子句中判断一个条件。如果该条件为假或未知，则返回TRUE；否则返回FALSE.

SELECT id,col1,col2, col3 FROM null_test_tab WHERE LNNVL(col1 IS NULL) ORDER BY id;

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

ID COL1 COL2 COL3

1 ONE TWO THREE

SELECT id, col1,col2, col3 FROM null_test_tab WHERE LNNVL(col2 = 'TWO') ORDER BY id;

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

ID COL1 COL2 COL3

3 THREE

4 THREE

SELECT id, col1,col2, col3 FROM null_test_tab WHERE LNNVL(col2 != 'TWO') ORDER BY id;

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

ID COL1 COL2 COL3

1 ONE TWO THREE

2 TWO THREE

3 THREE

4 THREE

NANVL

该函数是在ORACLE 10g中随着数据类型BINARY_FLOAT 和BINARY_DOUBLE 引入的，主要用来处理'Not a Number'和'NaN'，此处不多做介绍。

SYS_OP_MAP_NONNULL

用来使表达式“NULL=NULL”返回TRUE。该功能可以用NVL和DECODE实现，但是要保证这两个函数所使用的NULL的替代值在数据库中不存在。

SELECT id, 'col1=col2'

FROM null_test_tab

WHERE NVL(col1, '!null!') = NVL(col2, '!null!');

ID 'COL1=COL

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

3 col1=col2

4 col1=col2

2 rows selected.

SELECT id, 'col1=col2'

FROM null_test_tab

WHERE DECODE(col1, NULL, '!null!', col1) = DECODE(col2, NULL, '!null!', col2);

ID 'COL1=COL

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

3 col1=col2

4 col1=col2

2 rows selected.

SYS_OP_MAP_NONNULL函数也是一个可选方案。

SELECT id, 'col1=col2'

FROM null_test_tab

WHERE SYS_OP_MAP_NONNULL(col1) = SYS_OP_MAP_NONNULL(col2);

ID 'COL1=COL

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

3 col1=col2

4 col1=col2

2 rows selected.

注意：该函数没有出现在ORACLE官方文档中，严格的讲，不应该在生产系统中使用。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24867611/viewspace-739381/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24867611/viewspace-739381/