Oracle 单行函数

学习Oracle 单行函数：

包括字符函数，数值函数，日期函数，转换函数，通用函数。
dual是一个”伪表”，可以用来测试函数和表达式。
1， 字符函数
包括大小写控制函数，字符控制函数。
大小写控制函数：lower，upper，initcap。
字符控制函数：concat，substr，length，instr，lpad | rpad，trim，replace。
select lower( last_name || first_name) from employees;
select upper( last_name || first_name) from employees;
select initcap(job_id),job_id from employees;

select concat('Hello','World') from dual;
select substr('HelloWorld',1,5) from dual;
select length('HelloWorld') from dual;
select instr('HelloWorld','W') from dual;
select lpad('salary',10,'*') from dual;

select rpad('salary',10,'*') from dual;
select trim('H' from 'HelloWorld') from dual;
select replace('Hello','e','o') from dual;

2， 数字函数
分别有：round — 四舍五入，trunc — 截断，mod — 求余
select round(85.969,2),round(85.969,0),round(85.969,-1) from dual;
select trunc(85.969,2),trunc(85.969,0),trunc(85.969,-2) from dual;
select last_name,salary,mod(salary,5000) from employees;

3， 日期函数
函数sysdate 返回：日期，时间
日期的数学运算:

（1）在日期上加上或减去一个数字结果仍为日期
（2）两个日期相减返回日期之间相差的天数（日期不允许做加法运算，无意义）
（3）可以用数字除24来向日期中加上或减去天数。

计算员工入职日期距离系统当前时间相差多少个星期：
select last_name,(sysdate-hire_date)/7 as "周末" from employees;

日期函数：
months_between:两个日期相差的月数。
add_months：向指定日期中价上若干月数。
next_day:指定日期的下个星期*对应的日期。
last_day: 本月的最后一天。
round: 日期四舍五入。
trunc：日期截断。

select round( months_between(sysdate,'1-1月-19')) from dual;
select add_months(sysdate,3) from dual;
select next_day(sysdate,'星期五') from dual;
select last_day(sysdate) from dual;

4，转换函数
数据类型转换：隐性/显性
隐性数据类型转换：
Oracle 自动完成转换：
Varchar2 or char --> number
Varchar2 or char–> date
Number --> Varchar2
Date --> Varchar2

To_char 函数对日期的转换

（1）必须包含在单引号中而且大小写敏感。
（2）可以包含任意的有效的日期格式。
（3）日期之间用逗号隔开。

select last_name,to_char(hire_date,'dd month yyyy') from employees;

To_char 函数对字符的转换
select to_date('2019年1月29日 10:10:20','yyyy"年"mm"月"dd"日"hh:mi:ss') From dual

to_char函数对数字的转换
下面是在to_char 函数中经常使用的几种格式:

9  数字
0	零
$  美元符
L  本地货币符号
.  小数点
， 千位符

To_char函数对数字的转换

select last_name, to_char(salary,'$999,999,99') "工资" from employees;

5，通用函数
下面这些函数适用于任何数据类型，同时也适用于空值：
nvl(n1,n2)
nvl2(n1,n2,n3)
nullif(n1,n2)
coalesce(n1,n2……,nn)

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

    （1）可以使用的数据类型有日期、字符、数字。
    （2）函数的一般形式:
    –	nvl (commission_pct,0)
    –	nvl (hire_date,'01-7月-97')
    –	nvl (job_id,'No Job Yet')
    （3）nvl2 (n1, n2,n3) :n1不为NULL，返回n2；为NULL，返回n3。

select last_name,salary,nvl(commission_pct,0),(salary*12)+ (salary*12*nvl(commission_pct,0)) "工资" from employees;
select last_name, salary, commission_pct, nvl2(commission_pct, 'AA', 'YY') from employees where department_id in (50, 80);

nullif (n1,n2) : 相等返回null，不等返回n1。

select first_name, length(first_name) "n1", last_name, length(last_name) "n2", nullif(length(first_name), length(last_name)) "结果" from employees;

coalesce函数
（1）coalesce与nvl相比的优点在于 coalesce可以同时处理交替的多个值。
（2）如果第一个表达式为空,则返回下一个表达式，对其他的参数进行coalesce。
select last_name, coalesce(commission_pct, salary, 10) YY from employees order by commission_pct;

条件表达式
（1）在 SQL 语句中使用if-then-else 逻辑
（2）使用两种方法:
– case表达式
– decode 函数

select last_name, job_id, salary, case job_id when 'IT_PROG' then 1.10*salary when 'AD_VP' then 1.15*salary when 'AD_PRES' then 1.20*salary else salary end "综合" from employees;

嵌套函数
（1）单行函数可以嵌套。
（2）嵌套函数的执行顺序是由内到外。
F3(F2(F1(col,arg1),arg2),arg3)

select last_name, nvl(to_char(manager_id), 'No') from employees;

本篇的单行函数到这里结束。通过这篇我们应该对单行函数有初始的了解，深入了解还需要勤加练习。