參考資料: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 ...
參考資料: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, START
WITH
and CONNECT
BY
clauses, and HAVING
clauses.
單行函數返回查詢表或視圖的每一行的單一結果行。單行函數可以出現在SELECT列中,WHERE子句,START WITH 和CONNECT BY子句以及HAVING子句中。
NULL-Related Functions:
The NULL-related functions facilitate null handling.
The NULL
-related functions are:
NVL:
語法: NVL(expr1,expr2)
功能:
NVL
lets you replace null (returned as a blank) with a string in the results of a query. If expr1
is null, then NVL
returns expr2
. If expr1
is not null, then NVL
returns expr1
.
說明:
1.The arguments expr1
and expr2
can have any data type.
參數expr1和expr2可以是任何數據類型。
2. If their data types are different, then Oracle Database implicitly converts one to the other.
如果倆參數數據類型不同,oracle資料庫將進行隱式轉換。
3.If they cannot be converted implicitly, then the database returns an error.
如果倆參數不能進行隱式轉換,資料庫將返回一個error。
4.The implicit conversion is implemented as follows:(隱式轉換如下)
1>.If expr1
is character data, then Oracle Database converts expr2
to the data type of expr1
before comparing them and returns VARCHAR2
in the character set of expr1
.
如果expr1是字元數據,則資料庫會將expr2轉換為expr1的數據類型,並返回expr1的字元集中的VARCHAR2。
2>.If expr1
is numeric, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.
如果expr1是數字,則Oracle資料庫確定哪個參數具有最高的數字優先順序,將另一個參數隱式轉換為該數據類型,並返回該數據類型。
用途:
最主要的是格式化數據,比如涉及到數字的,不想出現空數據時,可用nvl(num,0)來得到0。由於 null+[或-,*,/]數字 等於null,所以在表達式中對可能為空的值要使用nvl。
NVL2:
語法: NVL2(expr1,expr2,expr3)
功能:
NVL2
lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1
is not null, then NVL2
returns expr2
. If expr1
is null, then NVL2
returns expr3
.
說明:
1.The argument expr1
can have any data type. The arguments expr2
and expr3
can have any data types except LONG
.
參數expr1可以是任何數據類型,參數expr2和expr3可以是除了LONG類型外的任何數據類型。
2.If the data types of expr2
and expr3
are different, then Oracle Database implicitly converts one to the other.
如果參數expr2和expr3是不同的數據類型,資料庫將會隱式的將其中一個轉換為另一個。
3.If they cannot be converted implicitly, then the database returns an error.
如果倆參數不能進行隱式轉換,資料庫將返回一個error。
4.If expr2
is character or numeric data, then the implicit conversion is implemented as follows:
(如果expr2是字元或是數字數據,則隱式轉換如下實現)
1>.If expr2
is character data, then Oracle Database converts expr3
to the data type of expr2
before returning a value unless expr3
is a null constant. In that case, a data type conversion is not necessary, and the database returns VARCHAR2
in the character set of expr2
.
如果expr2是字元數據,資料庫會將expr3(null除外)轉換為expr2的數據類型。如果expr3為空常量,則不需要進行數據類型轉換,資料庫會返回expr2的字元集中的VARCHAR2。
2>.If expr2
is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.
如果expr2是數字數據,那麼Oracle資料庫將確定哪個參數具有最高的數字優先順序,將另一個參數隱式轉換為該數據類型,並返回該數據類型。
NULLIF:
語法: NULLIF(expr1,expr2)
功能: NULLIF
compares expr1
and expr2
. If they are equal, then the function returns null. If they are not equal, then the function returns expr1
. You cannot specify the literal NULL
for expr1
.
說明:
If both arguments are 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 are not numeric, then they must be of the same data type, or Oracle returns an error.
如果倆參數是數字數據類型,則Oracle資料庫將確定具有較高數字優先順序的參數,將另一個參數隱式轉換為該數據類型,並返回該數據類型。 如果參數不是數字,則它們必須是相同的數據類型,否則Oracle返回一個error。
The NULLIF
function is logically equivalent to the following CASE
expression:
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
LNNVL:
語法: LNNVL(condition)
功能: LNNVL
provides a concise way to evaluate a condition when one or both operands of the condition may be null.
The function can be used in the WHERE
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 is FALSE
or UNKNOWN
and FALSE
if the condition is TRUE
.
LNNVL
can be used anywhere a scalar expression can appear, even in contexts where the IS
[NOT
] NULL
, AND
, or OR
conditions are not valid but would otherwise be required to account for potential nulls.
Oracle Database sometimes uses the LNNVL
function internally in this way to rewrite NOT
IN
conditions as NOT
EXISTS
conditions.
In such cases, output from EXPLAIN
PLAN
shows this operation in the plan table output.
The condition
can evaluate any scalar values but cannot be a compound condition containing AND
, OR
, or BETWEEN
.
NANVL:
語法: NANVL(n2,n1)
功能:
The NANVL
function is useful only for floating-point numbers of type BINARY_FLOAT
or BINARY_DOUBLE
.
It instructs Oracle Database to return an alternative value n1
if the input value n2
is NaN
(not a number). If n2
is not NaN
, then Oracle returns n2
.
This function takes as arguments any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.
Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.