Oracle's functions for handling null values

Reprinted: 2015-01-17 15:32:33

Http://www.cnblogs.com/FeiyueHang/archive/2011/03/08/1977729.html

1.NVL (EXPR1,EXPR2)

If EXPR1 is null, the function displays the value of EXPR2;

2.NVL2 (EXPR1,EXPR2,EXPR3)

If the value of EXPR1 is null, the function displays the value of EXPR3, NOT NULL, showing the value of EXPR2;

3.NULLIF (EXPR1,EXPR2)

Returns null if EXPR1=EXPR2, or returns the value of the first expression if unequal;

4.COALSECE (EXPR1,EXPR2,EXPR3)

If all is null, the function value is null, and if one is not NULL, the EXPRN is displayed; If none of the three items is empty, the first one is displayed;

5.

1  Case2 SELECTCust_last_name,3     CaseCredit_limit when  -  Then ' Low'4     when  the  Then ' High'5    ELSE 'Medium' END6     fromCustomers

Case similar to C language

6.DECODE

SELECTproduct_id, DECODE (warehouse_id,1,              'Southlake',              2,              'San Francisco',              3,              'New Jersey',              4,              'Seattle',              'non-domestic') "Location ofInventory " fromInventoriesWHEREproduct_id< 1775;

Oracle's functions for handling null values