First, NVLOfficial explanation: PURPOSENVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null and then NVL returns EXPR2. IFEXPR1 is isn't null, then NVL returns EXPR1. If EXPR1 is null, then EXPR2 is returned and EXPR1 is returned if EXPR1 is not null. The arguments expr1 and EXPR2 can have any data type. If their data types is different, then Oracle Database implicitly converts one to the other. If they cannot is converted implicitly, then the database returns an error.
Expr1
and
EXPR2
can be arbitrary data types, but they must be of the same data type or implicitly converted to the same data type, or the display will be converted to the same data type. If they are not the same type, the error is. The implicit conversion is implemented as follows:
- If Expr1 is character data, then Oracle Database converts expr2 to the data type OFEXPR1 before comparing them and returns VARCHAR2 in the character set OFEXPR1.
- If Expr1 is a character type, the EXPR2 is converted to a data type of expr1 before comparison.
- If expr1 is numeric, then Oracle Database determines which argument have the highest numeric precedence, implicitly convert s the other argument to that data type, and returns that data type.
- If EXPR1 is a numeric type, determine which parameter's data type is implicitly converted to which data type.
Official reference: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions119.htm#sthref1312
Second, NVL2PurposeNVL2 lets you determine the value returned by a query based on whether a specified expression was null or not NULL. If EXPR1 is not null and then NVL2 returns EXPR2. If expr1 is null and then NVL2 returns EXPR3. If Expr1 is not empty, then EXPR2 is returned, and if EXPR1 is null, then expr3the argument EXPR1 can has any data type. The arguments expr2 and EXPR3 can have any data types except LONG.
Expr1
can be any data type,
EXPR2
and
EXPR3
can be any data type, but cannot be of type long, with data types consistent, or implicitly converted to consistent, or the display converted to consistent. If the data types of EXPR2 and EXPR3 is different, then Oracle Database implicitly converts one to the other. If the EXPR2 and EXPR3 data types are different, then implicitly to the same if they cannot is converted implicitly, then the database returns an error. If the conversion is not implicit, an error occurs. If Expr2 is character or numeric data and then the implicit conversion is implemented as follows:
- If EXPR2 is character data and then Oracle Database converts EXPR3 to the data type of EXPR2 before returning a value unless EXPR3 is a null constant.
- In this case, a data type conversion are not necessary, and the database returns VARCHAR2 in the character set of EXPR2.
- If EXPR2 is a character type, the EXPR3 is converted to the same data type as EXPR2.
- If EXPR2 is numeric data and then Oracle Database determines which argument have the highest numeric precedence, implicitly Co Nverts the other argument to that data type, and returns that data type.
Official reference: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions120.htm#sthref1315
Third, NullifPurposenullif compares Expr1 and EXPR2. If They is equal, then the function returns NULL.
compares Expr1 and EXPR2, or returns a null value if they are equal. If They is not equal and then the function returns EXPR1. You cannot specify the literal NULL for EXPR1.
if not, the EXPR1 is returned. Cannot specify EXPR1 to be empty. If Both arguments is numeric data types, then Oracle Database determines the argument with the higher numeric precedence, Implicitly converts the other argument to that data type, and returns that data type. If The arguments is not numeric, then they must is the of the same data type, or Oracle returns an error.
If the parameter type is not a numeric type, it must be the same data type or an error. The NULLIF function is logically equivalent to the following case expression:case when expr1 = Expr2 then NULL ELSE expr1 End website Reference: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions116.htm#sthref1303
Iv. COALESCEPurposecoalesce (expression_1, Expression_2, ..., expression_n)
The first non-empty expression in the list is the return value of the function, and if all of the expressions are NULL, a null value will eventually be returned. COALESCE returns the first non-null expr in the expression list. Must specify at least
two expressions. If all occurrences of expr evaluate to null, then the function returns null. oracle Database Uses shor T-circuit evaluation. The database evaluates each expr value and determines whether it is null, rather than evaluating all of the expr values before determining whether any of the them is null. if all occurrences of expr is numeric data type or any nonnumeric data type This can be implicitly converted to a numeric data type and then Oracle Dat Abase determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that dat A type, and returns that data type. This function is a generalization of the nvl function. you can also use coalesce as a variety of The case expression. For Example, coalesce (EXPR1, EXPR2) was equivalent to:case when EXPR1 was not NULL then expr1 ELSE expr2 Endsimilarly,co Alesce (Expr1, Expr2, ..., exprn) where N >= 3, is equivalent to: case if EXPR1 is isn't NULL then expr1 ELSE coalesce (expr2, ..., exprn ) End Official reference:http://docs.oracle.com/cd/e11882_01/server.112/e41084/functions030.htm#sthref995
Wu, DECODEDECODE (Expr,search1,result1[,search2,result2......,default]): Compare expr with search and return RESULT1 if equal to Search1 If equals SEARCH2 returns RESULT2, and so on, if neither equals, returns null if there is default. Oracle automatically converts expr and each search to the first search (SEARCH1) data type before comparison. Automatically converts a return value to the data type of the first result (RESULT1). If the data type of the first result is char or the value is NULL, the Oracle conversion return value is VARCHAR2. In the decode function, NULL is equal, and if expr is empty, Then Oracle returns the result for the first null search. The maximum number of expressions in a decode list is 255. Expr is the same type as SEARCH1, Searchn and searche1 type return values and RESULT1 similar types
