Detailed explanation of Oracle nvl, nvl2, nullif, decode and case functions

1. NVL function

Nvl (expr1,expr2), if expr1 is empty, expr2 is returned

2. NVL2 function

Nvl2 (expr1,expr2,expr3) returns expr3 if expr1 is empty, expr2 otherwise

3. NULLIF function

Nullif (expr1,expr2), if expr1=expr2, returns empty, otherwise returns expr1, which requires that the data types of the two expressions are the same

SQL > insert into T1 values (9)

It shows that both the 1:NVL and NVL2 functions execute the expression in the function once when they judge the null value.

4. DECODE function:

It is an exclusive function provided by oracle database, not the sql standard.

Equivalent to if 1 then 1 else 1 in a programming language! = 1 execution effect

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

Comparison of execution performance of nvl, nvl2 and decode functions

SQL > create table T1 (I int);-- create T1 temporary table

SQL > insert into T1 values (9)

SQL > insert into T1 values (9);-- insert 3 rows of data with data values of 9

SQL > create or replace function sleep_now return number is

2 i number

3 begin

4 I: = 0

5 while8

6 i set timing on;-sets the execution time of the sqlplus command

SQL > select nvl (I < title > now ()) from T1

NVL (Imaine Sleeper now ())

-

nine

nine

nine

Executed in 0.343 seconds-determines whether the I field in the T1 table is empty or executes the sleep_now () function if it is empty

The sql execution time is 0.343 seconds.

SQL > select nvl2 (1) from now (1)

NVL2 (Imaine Sleeper now (), 1)

-

1000001

1000001

1000001

Executed in 0.343 seconds-also using the nvl2 function for testing

-- use decode for the same test with an execution time of 0.063 seconds

SQL > select decode (iGramage nullmage writing now (), 1111) from T1

DECODE (I # null SLEEPIMOW (), 1111

-

1111

1111

1111

Executed in 0.063 seconds

Conclusion: wrong and inappropriate use of nvl function will lead to endless trouble!