Oracle学习笔记_03_单行函数
1.SQL函数：
单行函数、多行函数
单行函数:
操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值
2.单行函数分类
字符、数字、日期、转换、通用函数
3.字符函数
（1）大小写控制函数：LOWER、UPPER、INITCAP
LOWER('SQL Course') sql course
UPPER('SQL Course') ——> SQL COURSE
INITCAP('SQL Course') Sql Course
SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = 'shirayner';
（2）字符控制函数
CONCAT('Hello' ,'World') HelloWorld
SUBSTR('HelloWorld', 1 , 5 ) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld' ,'W') ——> 6
LPAD(salary , 10 , '*') *****24000 (用*左填充)
RPAD(salary , 10 , '*') 24000*****
TRIM('H' FROM 'HelloWorld') elloWorld
REPLACE(‘abcd’ , ’b’ , ’m’) amcd
4.数字函数
个位数的下标为0
ROUND: 四舍五入
ROUND(45.926) = ROUND(45.926，0) 46
ROUND(45.926, 2) 45.93
TRUNC: 截断
TRUNC(45.926) =TRUNC(45.926，0) 46
TRUNC(45.926, 2) 45.92
TRUNC(45.926, -2) 0
TRUNC(45.926, -1) 40 （保留十位，后面截去，补0）
MOD: 求余
MOD(1600, 300) 100
MOD(salary, 5000)
5.日期函数
Oracle 中的日期型数据实际含有两个值: 日期和时间。
函数SYSDATE 返回: 日期、时间
（1）日期的数学运算：
在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回日期之间相差的天数。 （日期不允许做加法运算，无意义）
可以用数字除24来向日期中加上或减去天数。
（2）日期函数
MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') ： 两个日期相差的月数 19.6774194
ADD_MONTHS ('11-JAN-94',6) ： 向指定日期中加上若干月数 '11-JUL-94'
NEXT_DAY ('01-SEP-95','FRIDAY') ： 指定日期的下一个星期 * 对应的日期 '08-SEP-95'
NEXT_DAY ('01-SEP-95',1) 指定日期的下一个星期1对应的日期
LAST_DAY ('01-FEB-95') ： 本月的最后一天 '28-FEB-95'
ROUND('25-JUL-95' ,'MONTH') ： 日期四舍五入 '01-AUG-95'
TRUNC('25-JUL-95' ,'MONTH') ： 日期截断 '01-JUL-95'
6.转换函数
数据类型转换：隐式 、显式
隐式： Oracle 自动完成下列转换
显式：
（1） TO_CHAR(date, 'format_model')
select employee_id,hire_date
from employees
where to_char(hire_date,'yyyy/mm/dd') = '1994/06/07';
--where to_date('1994/06/07','yyyy/mm/dd') = hire_date;
TO_CHAR(number, 'format_model')
select to_char(12345678.99,'L999,999,999.99') from dual;
在TO_CHAR 函数中经常使用的几种格式:
（2）TO_DATE(char[ , 'format_model' ])
select employee_id,hire_date
from employees
--where to_char(hire_date,'yyyy/mm/dd') = '1994/06/07';
where to_date('1994/06/07','yyyy/mm/dd') = hire_date;
（3）TO_NUMBER(char[ , 'format_model' ])
TO_NUMBER(‘￥1,234,567,890.00’,’L999,999,999,999.99’) from dual
7.通用函数
这些函数适用于任何数据类型，同时也适用于空值：
NVL (expr1, expr2) 若expr1不为空，则expr1=expr1 ; 为空，expr1=expr2
NVL2 (expr1, expr2, expr3) 若expr1不为空，则expr1=expr2 ; 为空，expr1=expr3
NULLIF (expr1, expr2) 相等返回NULL，不等返回expr1
COALESCE (expr1, expr2, ..., exprn) 返回表达式中第一个非空表达式，如果都为空则返回空值。
（1） NVL (expr1, expr2)
select last_name,nvl(to_char(department_id),'没有部门') from employees;
（2） NVL2 (expr1, expr2, expr3)
select employee_id, nvl2(commission_pct ,commission_pct + 0.015 , 0.01) from employees;
（3） NULLIF (expr1, expr2)
SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees;
（4） COALESCE (expr1, expr2, ..., exprn)
SELECT last_name, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct;
（5）条件表达式之 CASE表达式
语法：
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
示例：
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;
（6）条件表达式之 DECODE 函数
语法： 若expression=search1，则结果为 result1
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;
