今天碰到一个同事问的NVL2函数使用的问题,简单记录一下(中间那会感觉不会这样,郁闷了好一会儿,有同样情况的同学要注意了),现象如下:
SQL> Create Or Replace Function fn_tnvl2(fv_info Varchar2) Return Varchar2 Is
2 lv_msginfo Varchar2(2000);
3 Begin
4 lv_msginfo := Nvl2(fv_info, fv_info||':', '')||To_Char(Sysdate, 'yyyy-mm-dd hh24:mi:ss');
5 Return lv_msginfo;
6 End;
7 /
Warning: Function created with compilation errors
SQL> show err;
Errors for FUNCTION OSS03.FN_TNVL2:
LINE/COL ERROR
-------- ---------------------------------------------
4/17 PLS-00201: identifier 'NVL2' must be declared
4/3 PL/SQL: Statement ignored
SQL>
SQL> Create Or Replace procedure p_tnvl2 Is
2 Begin
3 dbms_output.put_line(Nvl2(Null, 2, 1));
4 End;
5 /
Warning: Procedure created with compilation errors
SQL> show err;
Errors for PROCEDURE OSS03.P_TNVL2:
LINE/COL ERROR
-------- ---------------------------------------------
3/24 PLS-00201: identifier 'NVL2' must be declared
3/3 PL/SQL: Statement ignored
SQL>
SQL> Create Or Replace procedure p_tnvl2_v2 Is
2 v_info Varchar2(20);
3 Begin
4 Select Nvl2(Null, 'Not NULL', 'NULL') Into v_info From dual; --正常
5 dbms_output.put_line(v_info);
6 End;
7 /
Procedure created
可以发现一个规律,就是NVL2函数在表达式上使用的时候,会提示NVL2函数没有被定义,但是NVL没有这个问题,NVL2只能在SQL语句上面是没有问题的,但是官方文档上也没有明确说明,有点坑啊。
Description of the illustration nvl2.gif
NVL2
lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1
is not null, then NVL2
returns expr2
. If expr1
is null, then NVL2
returns expr3
.
The argument expr1
can have any data type. The arguments expr2
and expr3
can have any data types except LONG
.
If the data types of expr2
and expr3
are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. Ifexpr2
is character or numeric data, then the implicit conversion is implemented as follows:
-
If
expr2
is character data, then Oracle Database convertsexpr3
to the data type ofexpr2
before returning a value unlessexpr3
is a null constant. In that case, a data type conversion is not necessary, and the database returnsVARCHAR2
in the character set ofexpr2
. -
If
expr2
is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.