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:
NVL
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
. 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:
NVL2
Lets 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 .
LNNVL
Can 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