Oracle NVL, Nvl2, Nullif, decode, case functions

1. Nvl function

NVL (EXPR1,EXPR2), returns EXPR2 if Expr1 is empty;

2. NVL2 function

NVL2 (EXPR1,EXPR2,EXPR3), returns EXPR3 if Expr1 is empty, otherwise returns EXPR2;

3. Nullif function

Nullif (EXPR1,EXPR2), if EXPR1=EXPR2, returns NULL, otherwise returns EXPR1, requiring two expression data types to be consistent;

sql> INSERT INTO T1 values (9);

Note that the 1:NVL and NVL2 functions perform the expression in the function once, when the null value is judged.

4. Decode function:

is the exclusive function function of the Oracle database, not the SQL standard,

Equivalent to the If 1=1 then 1 else 1 in the program language! =1 effect of the implementation;

DECODE (value, IF1, Then1, If2,then2, If3,then3, ... else)

5. Case function

Case expr

When Comparison_expr1 then RETURN_EXPR1

when COMPARISON_EXPR2 then return_expr2

             when COMPARISON_EXPR3 then RETURN_EXPR3

                         

                         when comparison_exprn then return_exprn

             end

Performance comparison of NVL, NVL2, decode function execution

sql> CREATE TABLE t1 (i int);--Creating a T1 temporary table

sql> INSERT INTO T1 values (9);

sql> INSERT INTO T1 values (9);--Inserts 3 rows of data, data values are 9

sql> Create or Replace function Sleep_now return number is

2 I number;

3 begin

4 I: = 0;

5 While8

6 i<=1000000

7 loop

8 I: =i+1;

9 End Loop;

Ten return i;

one end;

12/--Create a sleep_now function to increase the time of SQL execution

Sql> set timing on; --Set sqlplus command execution time

Sql> Select NVL (I,sleep_now ()) from T1;

NVL (I,sleep_now ())

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

9

9

9

Executed in 0.343 seconds--Determines whether the I field in the T1 table is empty, and the Sleep_now () function is executed as NULL;

SQL execution time is 0.343 seconds;

Sql> Select Nvl2 (I,sleep_now (), 1) from T1;

NVL2 (I,sleep_now (), 1)

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

1000001

1000001

1000001

Executed in 0.343 seconds-also tested with NVL2 function

--using decode for the same test, execution time is 0.063 seconds

Sql> Select Decode (I,null,sleep_now (), 1111) from T1;

DECODE (I,null,sleep_now (), 1111

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

1111

1111

1111

Executed in 0.063 seconds

Summary: Wrong, inappropriate use of the NVL function, endless!

Oracle NVL, Nvl2, Nullif, decode, case functions