Oracle兼容-函数-NVL2()函数

1. 语法

  1. NVL2(expr1, expr2, expr3)

2. 定义和用法

参数 expr1、expr2、expr3 均为表达式。

NVL2() 函数作用类似 IFNULL()，如果 expr1 不为 NULL，则返回 expr2；当 expr1 为 NULL 时，则返回 expr3。

如果expr2和expr3类型不同，则返回的数据类型会依据expr2,expr3的类型自动选择其一并做适当转换。

3. Oracle兼容说明

GreatSQL中的 NVL2() 函数使用方法和Oracle中相同，但函数返回值类型与Oracle存在不同。

在Oracle中，如果 expr2 和 expr3 类型不同，expr3 会转换为 expr2 的类型，如果不能转换，则会报错。

而在GreatSQL中，则会依据 expr2 和 expr3 的类型，判断是否可聚合成为expr2 或 expr3 的类型之一。这个处理方式与 NVL()、COALESCE()、IF()、CASE() 等函数一致。

4. 示例

  1. -- 1. 当expr2和expr3类型不同时，与oracle不同的示例
  2. -- GreatSQL返回值，整型和浮点型聚合成浮点型
  3. greatsql> SELECT NVL2(1, 2.0, 3.0) + 0, NVL2(1.111111, 3, 2.111111), NVL(3, 2.111111) FROM DUAL;
  4. +-----------------------+-----------------------------+------------------+
  5. | NVL2(1, 2.0, 3.0) + 0 | NVL2(1.111111, 3, 2.111111) | NVL(3, 2.111111) |
  6. +-----------------------+-----------------------------+------------------+
  7. |                   2.0 |                    3.000000 |         3.000000 |
  8. +-----------------------+-----------------------------+------------------+
  10. -- Oracle返回值，expr3转换成expr2的类型
  11. SQL> SELECT NVL2(1, 2.0, 3.0) + 0, NVL2(1.111111, 3, 2.111111), NVL(3,2.111111) FROM DUAL;
  13. NVL2(1,2.0,3.0)+0 NVL2(1.111111,3,2.111111) NVL(3,2.111111)
  14. ----------------- ------------------------- ---------------
  15.                 2                         3               3
  17. -- GreatSQL返回值，整型和字符串聚合成字符串
  18. greatsql> SELECT NVL2(NULL, 1, 'GreatSQL'), NVL(1, 'GreatSQL') FROM DUAL;
  19. +---------------------------+--------------------+
  20. | NVL2(NULL, 1, 'GreatSQL') | NVL(1, 'GreatSQL') |
  21. +---------------------------+--------------------+
  22. | GreatSQL                  | 1                  |
  23. +---------------------------+--------------------+
  25. -- Oracle会报错，expr3字符串无法直接转换成expr2整型
  26. SQL> SELECT NVL2(NULL, 1, 'GreatSQL'), NVL(1, 'GreatSQL') FROM DUAL;
  27. SELECT NVL2(NULL, 1, 'GreatSQL'), NVL(1, 'GreatSQL') FROM DUAL
  28.                                 *
  29. ERROR at line 1:
  30. ORA-01722: invalid number
  33. -- 2. GreatSQL中其他函数的处理方式
  34. greatsql> SELECT NVL2(NULL, 1, 'GreatSQL'), if(NULL, 1, 'GreatSQL'), COALESCE(NULL, 1, 'GreatSQL'), NVL(3306, 'GreatQL') FROM DUAL;
  35. +---------------------------+-------------------------+-------------------------------+----------------------+
  36. | NVL2(NULL, 1, 'GreatSQL') | if(NULL, 1, 'GreatSQL') | COALESCE(NULL, 1, 'GreatSQL') | NVL(3306, 'GreatQL') |
  37. +---------------------------+-------------------------+-------------------------------+----------------------+
  38. | GreatSQL                  | GreatSQL                | 1                             | 3306                 |
  39. +---------------------------+-------------------------+-------------------------------+----------------------+
  41. -- 3. 正常案例
  42. -- GreatSQL
  43. greatsql> SELECT NVL2(0, 'ERROR', 'THIS'), NVL2(1, 'IS', 'ERROR'), NVL2(NULL, 'ERROR', 'A'), NVL2(1, 2, 3) FROM DUAL;
  44. +--------------------------+------------------------+--------------------------+---------------+
  45. | NVL2(0, 'ERROR', 'THIS') | NVL2(1, 'IS', 'ERROR') | NVL2(NULL, 'ERROR', 'A') | NVL2(1, 2, 3) |
  46. +--------------------------+------------------------+--------------------------+---------------+
  47. | ERROR                    | IS                     | A                        |             2 |
  48. +--------------------------+------------------------+--------------------------+---------------+
  50. -- Oracle
  51. SQL> SELECT NVL2(0, 'ERROR', 'THIS'), NVL2(1, 'IS', 'ERROR'), NVL2(NULL, 'ERROR', 'A'), NVL2(1, 2, 3) FROM DUAL;
  53. NVL2( NV N NVL2(1,2,3)
  54. ----- -- - -----------
  55. ERROR IS A           2
  57. -- GreatSQL，全部返回NULL
  58. greatsql> SELECT NVL2('GreatSQL', NULL ,3306), NVL2(NULL, 3306 ,NULL), NVL2(NULL, 3306, '') FROM DUAL;
  59. +------------------------------+------------------------+----------------------+
  60. | NVL2('GreatSQL', NULL ,3306) | NVL2(NULL, 3306 ,NULL) | NVL2(NULL, 3306, '') |
  61. +------------------------------+------------------------+----------------------+
  62. |                         NULL |                   NULL | NULL                 |
  63. +------------------------------+------------------------+----------------------+
  65. -- Oracle，全部返回空值
  66. SQL> SELECT NVL2('GreatSQL', NULL ,3306), NVL2(NULL, 3306 ,NULL), NVL2(NULL, 3306, '') FROM DUAL;
  68. NVL2('GREATSQL',NULL,3306) NVL2(NULL,3306,NULL) NVL2(NULL,3306,'')
  69. -------------------------- -------------------- ------------------
  72. SQL>

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx