关于oracle中的通用函数的点滴

这篇文章主要记录的是oracle中的通用函数

在oracle中通用函数有4个：

NVL (expr1, expr2)

NVL2 (expr1, expr2, expr3)

NULLIF (expr1, expr2)

COALESCE (expr1, expr2, ..., exprn)

 

 

lNVL (expr1, expr2)

将空值转换成一个已知的值：

l可以使用的数据类型有日期、字符、数字。

l函数的一般形式:

•NVL(commission_pct,0)

•NVL(hire_date,'01-JAN-97')

•NVL(job_id,'No Job Yet')

 

示例：

SELECT last_name, salary, NVL(commission_pct, 0),

   (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL

FROM employees;

 

英文注释

（The NVL Function

To convert a null value to an actual value, use the NVL function.

Syntax

NVL (expr1, expr2)

In the syntax:

expr1 is the source value or expression that may contain a null

expr2 is the target value for converting the null

You can use the NVL function to convert any data type, but the return value is always the same as the data type of expr1.

NVL Conversions for Various Data Types ）

the return value is always the same as the data type of expr1. 在注释中这句话最为关键。它说明NVL函数的括号内的erpr2必须和expr1的数据类型一致！！！要么都是Number,要么都是varchar等

 

lNVL2 (expr1, expr2, expr3)

NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。

 

示例：

SELECT last_name,  salary, commission_pct,

       NVL2(commission_pct,

            'SAL+COMM', 'SAL') income

FROM   employees WHERE department_id IN (50, 80);

 

英文注释

(The NVL2 Function

The NVL2 function examines the first expression. If the first expression is not null, then the NVL2 function returns the second expression. If the first expression is null, then the third expression is returned.

Syntax

NVL(expr1, expr2, expr3)

In the syntax:

expr1 is the source value or expression that may contain null

expr2 is the value returned if expr1 is not null

expr3 is the value returned if expr2 is null

In the example shown, the COMMISSION_PCT column is examined. If a value is detected, the second expression of  SAL+COMM is returned. If the COMMISSION_PCT column holds a null values, the third expression of SAL is returned.

The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG. If the data types of expr2 and expr3 are different, The Oracle server converts expr3 to the data type of expr2 before comparing them unless expr3 is a null constant. In that case, a data type conversion is not necessary.

The data type of the return value is always the same as the data type of expr2, unless expr2 is character data, in which case the return value’s data type is VARCHAR2. )

 

lNULLIF (expr1, expr2)

lNULLIF (expr1, expr2) :  相等返回NULL，不等返回expr1

 

示例：

SELECT first_name, LENGTH(first_name) "expr1",

       last_name,  LENGTH(last_name)  "expr2",

       NULLIF(LENGTH(first_name), LENGTH(last_name)) result

FROM   employees;

 

英文注释：

（The NULLIF Function

The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the function returns the first expression. You cannot specify the literal NULL for first expression.

Syntax

NULLIF (expr1, expr2)

In the syntax:

expr1 is the source value compared to expr2

expr2 is the source value compared with expr1. (If it is not equal to expr1, expr1 is returned.)

In the example shown, the job ID in the EMPLOYEES table is compared to the job ID in the JOB_HISTORY table for any employee who is in both tables. The output shows the employee’s current job.  If the employee is listed more than once, that means the employee has held at least two jobs previously.

Note: The NULLIF function is logically equivalent to the following CASE expression. The CASE expression is discussed in a subsequent page:

    CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END ）

 

COALESCE (expr1, expr2, ..., exprn)

COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。

如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。

即：找第一个不为空的值。

 

示例：

SELECT   last_name,

         COALESCE(commission_pct, salary, 10) comm

FROM     employees

ORDER BY commission_pct;

 

英文注释：

（The COALESCE Function

The COALESCE function returns the first non-null expression in the list.

Syntax

COALESCE (expr1, expr2, ... exprn)

In the syntax:

expr1 returns this expression if it is not null

expr2 returns this expression if the first expression is null and this expression is not null

exprn returns this expression if the preceding expressions are null ）