Oracle入门第二天（下）——单行函数
一、概述
以下内容完整参阅，参考官方文档函数手册部分：https://docs.oracle.com/cd/E11882_01/nav/portal_5.htm
离线chm手册英文版：链接：https://pan.baidu.com/s/1o85jQ9G 密码：ssyj——（若无法显示正文，在文件上右键属性-解除锁定即可！）
什么是单行函数：
操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值
// 比如分组函数就是典型的多行函数
分类：
二、字符函数
大小写控制函数：
LOWER()——转化为小写
UPPER()——转化为大写
INITCAP()——首字母大写
SELECT LOWER('SQL Course'),UPPER('SQL Course'),INITCAP('SQL Course') FROM dual;
LOWER('SQLCOURSE') UPPER('SQLCOURSE') INITCAP('SQLCOURSE')
------------------ ------------------ --------------------
sql course SQL COURSE Sql Course
// dual是一张虚表，并且再次提醒SQL大小写不敏感，lower()也是允许的
字符控制函数：
CONCAT('Hello', 'World')——连接字符串
SUBSTR('HelloWorld',1,5)——取子串，从下标1开始，取5个（SQL下标从1开始而不是0）
LENGTH('HelloWorld')——得到长度
INSTR('HelloWorld', 'W')——首次出现的位置
LPAD(salary,10,'*')——左补齐
RPAD(salary, 10, '*')——右补齐
TRIM('H' FROM 'HelloWorld')——去除首尾的指定字符
REPLACE(‘abcd’,’b’,’m’)——替换所有的字符，（将b替换为m）
SELECT CONCAT('HelloWorld','jiangbei'),SUBSTR('HelloWorld',1,5),LENGTH('HelloWorld'),INSTR('HelloWorld','W') FROM dual;
CONCAT('HELLOWORLD','JIANGBEI' SUBSTR('HELLOWORLD',1,5) LENGTH('HELLOWORLD') INSTR('HELLOWORLD','W')
------------------------------ ------------------------ -------------------- -----------------------
HelloWorldjiangbei Hello 10 6
SELECT LPAD(salary,10,'*') FROM employees;
LPAD(SALARY,10,'*')
--------------------
*****24000
*****17000
*****17000
******9000
******6000
SELECT TRIM('H' FROM 'H2H2H'),REPLACE('H2H2H2','H','M') FROM dual;
TRIM('H'FROM'H2H2H') REPLACE('H2H2H2','H','M')
-------------------- -------------------------
2H2 M2M2M2
三、数字函数
ROUND(45.926,2)——四舍五入
TRUNC(45.926,2)——截断
MOD(100,3)——求余
SELECT ROUND(45.926,2),TRUNC(45.926,2),MOD(100,3) FROM dual;
ROUND(45.926,2) TRUNC(45.926,2) MOD(100,3)
--------------- --------------- ----------
45.93 45.92 1
四、日期函数
SYSDATE——返回日期与时间
select sysdate from dual;
使用trunc(sysdate) 只取日期部分
日期数字运算：
在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回日期之间相差的天数。
日期不允许做加法运算，无意义
可以用数字除24来向日期中加上或减去天数。
日期函数
ADD_MONTHS ('11-JAN-94',6)——'11-JUL-94'
五、转换函数
隐式转换
显示转换函数
TO_CHAR(date, 'format_model')——日期转化为字符型
日期格式如下;yyyy 年 mm 月 dd 日 day 星期 hh 小时 mi 分钟 ss 秒
TO_DATE('char','format_mldel')——字符转化为日期
TO_CHAR(number, 'format_model')——数字转字符
SELECT TO_CHAR(1234567.89,'999,999,999.99')
TO_NUMBER(‘￥1,234,567,890.00’,’L999,999,999,999.99’)
六、通用函数
这些函数适用于任何数据类型，同时也适用于空值：
NVL (expr1, expr2)——空值转化为指定值
NVL2 (expr1, expr2, expr3)——expr1不为NULL，返回expr2；为NULL，返回expr3。
NULLIF (expr1, expr2)——相等返回NULL，不等返回expr1
COALESCE (expr1, expr2, ..., exprn)——交替判断是否为空：若expr1为空，返回expr2；若expr2为空，返回expr3；依次类推
七、条件表达式
SQL中使用IF/ELSE可以有两种方式：
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
employee_id,last_name,department_id,CASE department_id WHEN 10 THEN salary*1.1
WHEN 20 THEN salary*1.2
ELSE salary*1.3
END AS NEW_SAL
FROM
employees
WHERE
department_id IN (10,20,30)
// 如果后面不使用别名，将会生成内置别名
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID NEW_SAL ----------- ------------------------- ------------- ---------- 114 Raphaely 30 14300 115 Khoo 30 4030 116 Baida 30 3770 117 Tobias 30 3640 118 Himuro 30 3380 119 Colmenares 30 3250 200 Whalen 10 4840 201 Hartstein 20 15600 202 Fay 20 7200 9 rows selected
更多CASE用法，参考：http://blog.itpub.net/27042095/viewspace-739106/
DECODE函数；
语法：
DECODE(col|expression, search1, result1 ,
[, search2, result2,...,]
[, default])
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
示例：
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;
PIOVT函数用于行转列：
