南大通用GBase 8a对null相关函数nvl，ifnull,nullif等

GBase 8a内置几个函数实现对null值的处理，主要是为了避免null值参与运算导致结果和预期不符。

ifnull(expr1,expr2)

如果 expr1 为null, 则返回expr2, 否则返回 expr1自己。

返回值类型，根据expr1和expr2或者上下文决定。等价于 IF(expre1,expre1,expre2)。

数字

gbase> select ifnull(null,0) from dual;
+----------------+
| ifnull(null,0) |
+----------------+
|              0 |
+----------------+
1 row in set (Elapsed: 00:00:00.00)

字符

gbase> select ifnull(null,'abcd') from dual;
+---------------------+
| ifnull(null,'abcd') |
+---------------------+
| abcd                |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

日期

gbase> select ifnull(null,now()) from dual;
+---------------------+
| ifnull(null,now())  |
+---------------------+
| 2020-12-22 09:22:09 |
+---------------------+
1 row in set (Elapsed: 00:00:00.01)

nvl(expr1,expr2)

与ifnull完全相同。如果expr1为null,则返回expr2,否则返回expr1自己。

gbase> select nvl(null,1) from dual;
+-------------+
| nvl(null,1) |
+-------------+
|           1 |
+-------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select nvl(null,'abcd') from dual;
+------------------+
| nvl(null,'abcd') |
+------------------+
| abcd             |
+------------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select nvl(null,now()) from dual;
+---------------------+
| nvl(null,now())     |
+---------------------+
| 2020-12-22 09:26:08 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

nullif(expr1,expr2)

如果 expr1 = expr2 成立，返回值为 NULL，否则返回值为 expr1。
等价于 CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END。

注意内部的数据类型如果不同，会自动转换后再比较。所以数字1和字符串1也是相等的。

gbase> select nullif(1,1) from dual;
+-------------+
| nullif(1,1) |
+-------------+
|        NULL |
+-------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select nullif(1,'1') from dual;
+---------------+
| nullif(1,'1') |
+---------------+
|          NULL |
+---------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select nullif('1',1) from dual;
+---------------+
| nullif('1',1) |
+---------------+
| NULL          |
+---------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select nullif('1',2) from dual;
+---------------+
| nullif('1',2) |
+---------------+
| 1             |
+---------------+
1 row in set (Elapsed: 00:00:00.01)

gbase>