Oracle Single-row Functions (single-line function)--null-related Functions

Reference: http://docs.oracle.com/database/122/SQLRF/Functions.htm#SQLRF006

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, and START WITH CONNECT BY clauses, and HAVING clauses.

The one-line function returns a single result row for each row of the query table or view. A single-line function can appear in the Select column, where clause, START with and connect by clauses, and the HAVING clause.

null-related Functions:

The null-related functions facilitate NULL handling.

The NULL -related functions is:

　　NVL:

Syntax: NVL (EXPR1,EXPR2)

Function:　　

　　　　　　　　NVLLets 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 . If expr1 is isn't null, then NVL returns expr1 .

Description

1.The arguments and can has any expr1 expr2 data type.

The parameters Expr1 and EXPR2 can be any data type.

2. If their data types is different, then Oracle Database implicitly converts one to the other.

If the two parameter data types are different, the Oracle database will be implicitly converted.

3.If they cannot be converted implicitly and then the database returns an error.

If the two parameters cannot be implicitly converted, the database returns an error.

4.The implicit conversion is implemented as follows: (implicitly converted as follows)

1>. If expr1 is character data and then Oracle Database converts to the expr2 data type of expr1 before comparing the M and returns in the VARCHAR2 character set of expr1 .

If EXPR1 is character data, the database converts expr2 to the EXPR1 data type and returns VARCHAR2 in the EXPR1 character set.

2>. If expr1 is numeric, then Oracle Database determines which argument have the highest numeric precedence, implicitly con Verts the other argument to that data type, and returns that data type.

If Expr1 is a number, the Oracle database determines which parameter has the highest numeric priority, implicitly converts another parameter to that data type, and returns the data type.

　　　　Use :

The most important is formatted data, such as involving numbers, do not want to appear empty data, you can use NVL (num,0) to get 0. Because null+[or-,*,/] numbers are equal to NULL, You use NVL for values that can be nullable in an expression .

　　NVL2:

　　　　Syntax : NVL2 (EXPR1,EXPR2,EXPR3)

　　　　Function:

　　　　　　　　NVL2Lets determine the value returned by a query based on whether a specified expression was null or not NULL. If expr1 is isn't null, then NVL2 returns expr2 . If expr1 is null and then NVL2 returns expr3 .

Description

1.The argument can has any expr1 data type. The arguments and can has any expr2 expr3 data types except LONG .

The parameter expr1 can be any data type, and the parameters expr2 and EXPR3 can be any data type except the long type.

2.If the data types expr2 expr3 of and is different, then Oracle Database implicitly converts one to the othe R.

If the parameters expr2 and EXPR3 are different data types, the database will implicitly convert one of them to another.

3.If they cannot be converted implicitly and then the database returns an error.

If the two parameters cannot be implicitly converted, the database returns an error.

4.If expr2 is character or numeric data and then the implicit conversion is implemented as follows:

(If EXPR2 is a character or numeric data, an implicit conversion is implemented as follows)

1>. If expr2 is character data and then Oracle Database converts to the expr3 data type of expr2 before Returni Ng a value unless is expr3 a null constant. In this case, a data type conversion was not necessary, and the database returns in the VARCHAR2 character set expr2 of .

if EXPR2 is character data, the database converts EXPR3 (except null) to the EXPR2 data type. If EXPR3 is an empty constant, data type conversions are not required and the database is returned to VARCHAR2 in the expr2 character set.

2>. If expr2 is numeric data, then Oracle Database determines which argument have the highest numeric precedence, Implici Tly converts the other argument to that data type, and returns that data type.

If EXPR2 is a numeric data, then the Oracle database determines which parameter has the highest numeric priority, implicitly converts another parameter to that data type, and returns the data type.

Nullif:

Syntax: Nullif (EXPR1,EXPR2)

Functions: NULLIF compares and expr1 expr2 . If They is equal, then the function returns NULL. If They is not equal and then the function returns expr1 . You cannot specify the literal NULL for expr1 .

Description

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 two arguments are numeric data types, the Oracle database determines the parameters with a higher numeric precedence, implicitly converts the other parameter to the data type, and returns the data type. If the arguments are not numbers, they must be of the same data type, or Oracle returns an error.

The NULLIF function is logically equivalent to the following CASE expression:

 Case  when =  Then NULL ELSE END

LNNVL:

Syntax: LNNVL (condition)

Function: LNNVL provides a concise-evaluate a condition when one or both operands of the condition is null.

The function can be used WHERE in the clause of a query, or as the WHEN condition in a searched CASE expression.

It takes as an argument a condition and returns TRUE if the condition are FALSE or UNKNOWN and FALSE if the condition is .

　　　　　　　　　LNNVLCan is used anywhere a scalar expression can appear, even in contexts where the IS [ NOT ] NULL , AND , or OR cond Itions is not valid but would otherwise is required to account for potential nulls.

Oracle Database sometimes uses the function internally in this-to LNNVL rewrite NOT IN conditions NOT EXISTS as Conditions.

In such cases, output from shows this operation in the EXPLAIN PLAN plan table output.

condition The can evaluate any scalar values but cannot is a compound condition containing AND , OR or BETWEEN .

NANVL:

Syntax: NANVL (N2,N1)

Function:

The NANVL function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE .

It instructs Oracle Database to return a alternative value n1 if the input value was n2 (not a number NaN ) . If n2 is not NaN and then Oracle returns n2 .

This function takes as arguments any numeric data type or any nonnumeric data type so can be implicitly converted to a n Umeric data type.

Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that Da TA type, and returns that data type.

Oracle Single-row Functions (single-line function)--null-related Functions