Re: about nvl2 function
Date: Wed, 9 Jun 2010 06:45:28 -0700 (PDT)
Message-ID: <40527049-b505-43a2-9210-edc1cc5e93f4_at_i28g2000yqa.googlegroups.com>
Another reason to use coalessce is that it will short circuit the parameters while nvl and nvl2 will not. This might seem trivial and is if your arguments are literals or columns. But if they are functions, sequences or some computed value it can make a difference in performance or correct data.
sql->create or replace package atst
2 as
3
4 function getNextVal return number;
5 end;
6 /
Package created.
Elapsed: 00:00:00.02
sql->show err
No errors.
sql-> sql->-------------------------------------- sql->create or replace package body atst2 as
3
4 x number;
5
6 function getNextVal return number
7 is
8 begin
9 x := x + 1; 10 return x;
11 end;
12
13
14 begin
15 x := 0;
16 end;
17 /
Package body created.
Elapsed: 00:00:00.02
sql->show err
No errors.
sql-> sql->col nxtval format 99999 sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual;
NXTVAL
1
Elapsed: 00:00:00.00
sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual;
NXTVAL
3
Elapsed: 00:00:00.00
sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual;
NXTVAL
5
Elapsed: 00:00:00.01
sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual;
NXTVAL
7
Elapsed: 00:00:00.00
sql-> sql->--============================================================ sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval fromdual;
NXTVAL
9
Elapsed: 00:00:00.00
sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval from
dual;
NXTVAL
10
Elapsed: 00:00:00.00
sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval from
dual;
NXTVAL
11
Elapsed: 00:00:00.01
sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval from
dual;
NXTVAL
12 Received on Wed Jun 09 2010 - 08:45:28 CDT