Java乔晓松-oracle的单行函数（日期函数和数字函数）

单行函数

单行函数:

操作数据对象

接受参数返回一个结果

只对一行进行变换

每行返回一个结果

可以转换数据类型

可以嵌套

参数可以是一列或一个值

字符函数

大小写控制函数

这类函数改变字符的大小写。

显示员工 Higgins的信息:

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  last_name = 'higgins';

no rows selected

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  LOWER(last_name) = 'higgins';

字符控制函数

这类函数控制字符:

SELECT employee_id, CONCAT(first_name, last_name) NAME,

       job_id, LENGTH (last_name),

       INSTR(last_name, 'a') "Contains 'a'?"

FROM   employees

WHERE  SUBSTR(job_id, 4) = 'REP';

数字函数

ROUND: 四舍五入

ROUND(45.926, 2)                     45.93

TRUNC:  截断

TRUNC(45.926, 2)               45.92

MOD: 求余

MOD(1600, 300)                 100

ROUND 函数

SELECT ROUND(45.923,2), ROUND(45.923,0),

       ROUND(45.923,-1)

FROM   DUAL;

DUAL 是一个‘伪表’，可以用来测试函数和表达式

TRUNC 函数

SELECT  TRUNC(45.923,2), TRUNC(45.923),

        TRUNC(45.923,-2)

FROM   DUAL;

MOD 函数

SELECT last_name, salary, MOD(salary, 5000)

FROM   employees

WHERE  job_id = 'SA_REP';

日期

Oracle 中的日期型数据实际含有两个值:日期和时间。

默认的日期格式是 DD-MON-RR.

SELECT last_name, hire_date

FROM   employees

WHERE  last_name like 'G%';

函数SYSDATE返回:

日期

时间

日期的数学运算

在日期上加上或减去一个数字结果仍为日期。

两个日期相减返回日期之间相差的天数。

可以用数字除24来向日期中加上或减去小时。

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS

FROM   employees

WHERE  department_id = 90;

日期函数

TO_CHAR 函数对日期的转换

TO_CHAR(date, 'format_model')

格式:

必须包含在单引号中而且大小写敏感。

可以包含任意的有效的日期格式。

日期之间用逗号隔开。

日期格式的元素

SELECT last_name,

       TO_CHAR(hire_date, 'DD Month YYYY')

       AS HIREDATE

FROM   employees;

TO_CHAR 函数对数字的转换

TO_CHAR(number, 'format_model')

下面是在TO_CHAR函数中经常使用的几种格式:

SELECT TO_CHAR(salary, '$99,999.00') SALARY

FROM   employees

WHERE  last_name = 'Ernst';

TO_NUMBER 和 TO_DATE函数

使用 TO_NUMBER函数将字符转换成数字:

TO_NUMBER(char[, 'format_model'])

 

使用 TO_DATE函数将字符转换成日期:

TO_DATE(char[, 'format_model'])

通用函数

这些函数适用于任何数据类型，同时也适用于空值：

NVL (expr1, expr2)

NVL2 (expr1, expr2, expr3)

NULLIF (expr1, expr2)

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

NVL 函数

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

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

函数的一般形式:

NVL(commission_pct,0)

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

NVL(job_id,'No Job Yet')

使用NVL函数

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

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

FROM employees;

使用 NVL2函数

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);

 

使用 NULLIF 函数

NULLIF (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;

使用 COALESCE函数

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

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

使用 COALESCE函数

SELECT   last_name,

         COALESCE(commission_pct, salary, 10) comm

FROM     employees

ORDER BY commission_pct;

条件表达式

在 SQL 语句中使用IF-THEN-ELSE 逻辑

使用两种方法:

CASE 表达式

DECODE 函数

CASE 表达式

在需要使用 IF-THEN-ELSE逻辑时:

CASE expr WHEN comparison_expr1 THEN return_expr1

         [WHEN comparison_expr2 THEN return_expr2

          WHEN comparison_exprn THEN return_exprn

          ELSE else_expr]

END

下面是使用case表达式的一个例子：

SELECT last_name, job_id, salary,

       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary

                   WHEN 'ST_CLERK' THEN  1.15*salary

                   WHEN 'SA_REP'   THEN  1.20*salary

       ELSE      salary END     "REVISED_SALARY"

FROM   employees;

DECODE 函数

在需要使用 IF-THEN-ELSE逻辑时:

DECODE(col|expression, search1, result1

                           [, search2, result2,...,]

                           [, default])

SELECT last_name, job_id, salary,

       DECODE(job_id, 'IT_PROG',  1.10*salary,

                      'ST_CLERK', 1.15*salary,

                      'SA_REP',   1.20*salary,

              salary)

       REVISED_SALARY

FROM   employees;

嵌套函数

单行函数可以嵌套。

嵌套函数的执行顺序是由内到外。

SELECT last_name,

       NVL(TO_CHAR(manager_id), 'No Manager')

FROM   employees

WHERE  manager_id IS NULL;

总结

通过本章学习，您应该学会:

使用函数对数据进行计算

使用函数修改数据

使用函数控制一组数据的输出格式

使用函数改变日期的显示格式

使用函数改变数据类型

使用 NVL 函数

使用IF-THEN-ELSE逻辑