oracle 10g 学习之单行函数（5）
目标
通过本章学习，您将可以:
l SQL中不同类型的函数。
l 在 SELECT 语句中使用字符，数字和日期函数。
l 描述转换型函数的用途。
字符函数
字符函数分为大小写控制函数和字符控制函数
（1） 大小写控制函数
LOWER将字符串转换为小写
UPPER 将字符串转换为大写
INITCAP 将字符串第一个首字母大写
select LOWER('ABC'),UPPER('abc'),INITCAP('abc') from dual;
DUAL 是一个‘伪表’，可以用来测试函数和表达式，结果为：
LOWER('ABC') UPPER('ABC') INITCAP('ABC')
------------ ------------ --------------
abc ABC Abc
比如我们查询员工表可以这样使用：
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';
（2） 字符控制函数
CONCAT将两个字符串连接
select CONCAT('Hello', 'World') FROM DUAL;
CONCAT('HELLO','WORLD')
-----------------------
HelloWorld
SUBSTR截取字符串，从1开始
select SUBSTR('HelloWorld',1,5) FROM DUAL;
SUBSTR('HELLOWORLD',1,5)
------------------------
Hello
LENGTH字符串的长度
select LENGTH('HelloWorld') FROM DUAL;
LENGTH('HELLOWORLD')
--------------------
10
INSTR找到某个字符在字符串中第一次出现的位置
select INSTR('HelloWorld', 'W') FROM DUAL;
INSTR('HELLOWORLD','W')
-----------------------
6
LPAD判断字符串是否有指定的字符串的长度，没有则从左边开始以指定的字符填充
select LPAD('abc',10,'*') FROM DUAL;
LPAD('ABC',10,'*')
------------------
*******abc
RPAD和LPAD基本一样，只不过是从右边填充
TRIM去除指定字符串的前后的指定字符，字符串区分大小写
select TRIM('a' FROM 'abcda') FROM DUAL；
TRIM('A'FROM'ABCDA')
--------------------
bcd
REPLACE替换字符串
select replace('abcd', 'b', 'm') FROM DUAL；
REPLACE('ABCD','B','M')
-----------------------
amcd
数字函数
l ROUND: 四舍五入
ROUND(45.926, 2) 45.93 保留两位小数
l TRUNC: 截断
TRUNC(45.926, 2) 45.92 保留两位小数
l MOD: 求余
MOD(1600, 300) 100
日期
l Oracle 中的日期型数据实际含有两个值: 日期和时间。
l 默认的日期格式是 DD-MON-RR.
函数SYSDATE 返回:
l 日期
l 时间
日期的数学运算
l 在日期上加上或减去一个数字结果仍为日期。
l 两个日期相减返回日期之间相差的天数。
l 可以用数字除24来向日期中加上或减去小时。
日期函数
MONTHS_BETWEEN两个日期相差的月数
select months_between(to_date('1999.11.29','yyyy.mm.dd'), to_date('1998.11.29','yyyy.mm.dd')) from dual;
MONTHS_BETWEEN(TO_DATE('1999.1
------------------------------
12
ADD_MONTHS向指定日期中加上若干月数
select add_months(to_date('1999.11.29','yyyy.mm.dd'),1) from dual
ADD_MONTHS(TO_DATE('1999.11.29
------------------------------
1999-12-29
NEXT_DAY 指定日期的下一个日期
select next_day(to_date('2013.11.30','yyyy.mm.dd'),'星期五') from dual
2013.11.30下个星期五就是NEXT_DAY(TO_DATE('2013.11.30',
------------------------------
2013-12-6
LAST_DAY 本月的最后一天
select last_day(to_date('2013.11.22','yyyy.mm.dd')) from dual
LAST_DAY(TO_DATE('2013.11.22',
------------------------------
2013-11-30
ROUND 日期四舍五入
Assume SYSDATE = '25-JUL-95':
- ROUND(SYSDATE,'MONTH') 01-AUG-95
- ROUND(SYSDATE ,'YEAR') 01-JAN-96
- TRUNC(SYSDATE ,'MONTH') 01-JUL-95
TRUNC 日期截断
- TRUNC(SYSDATE ,'YEAR') 01-JAN-95
转换函数
隐式数据类型转换
Oracle 自动完成下列转换：
VARCHAR2 or CHAR → NUMBER
VARCHAR2 or CHAR → DATE
NUMBER → VARCHAR2
DATE → VARCHAR2
TO_CHAR 函数对日期的转换
TO_CHAR(date, 'format_model')
格式:
l 必须包含在单引号中而且大小写敏感。
l 可以包含任意的有效的日期格式。
l 日期之间用逗号隔开。
日期格式的元素
时间格式
HH24:MI:SS AM 15:45:32 PM
使用双引号向日期中添加字符
DD "of" MONTH 12 of OCTOBER
TO_CHAR 函数对数字的转换
TO_CHAR(number, 'format_model')
下面是在TO_CHAR 函数中经常使用的几种格式:
SELECT TO_CHAR('33333.33', '$99,999.99') from dual;
TO_CHAR('33333.33','$99,999.99
------------------------------
$33,333.33
TO_NUMBER 和 TO_DATE 函数
l 使用 TO_NUMBER 函数将字符转换成数字:
l TO_NUMBER(char[, 'format_model'])
l 使用 TO_DATE 函数将字符转换成日期:
l TO_DATE(char[, 'format_model'])
通用函数
这些函数适用于任何数据类型，同时也适用于空值：
l NVL (expr1, expr2)
l NVL2 (expr1, expr2, expr3)
l NULLIF (expr1, expr2)
COALESCE (expr1, expr2, ..., exprn)
NVL 函数
将空值转换成一个已知的值：
l 可以使用的数据类型有日期、字符、数字。
l 函数的一般形式:
- NVL(commission_pct,0)
- NVL(hire_date,'01-JAN-97')
- NVL(job_id,'No Job Yet')
使用 NVL2 函数
NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。
exp1 != null ? exp2 : exp3
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
使用 COALESCE 函数
l COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
l 如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。
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
DECODE 函数和CASE表达式一样，语法不同
在需要使用 IF-THEN-ELSE 逻辑时:
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;
