Oracle-5 单行函数的运用

---

字符函数

转换大小写：lower() upper() initcap()

lower() 将所有字母转小写
select ename,lower(ename) 转小写后名称,job,lower(job) 转小写后的职位 from emp where lower(ename) like ‘%s%’;

select lower(‘SQL Course’) from dual;–sql course dual伪表，仅存在一行一列

upper() 将所有字母转大写
select upper(‘SQL Course’) from dual;

initcap() 首字母转大写
select initcap(‘sql course’) from dual;

字符处理函数：concat() substr() length() instr() lpad() rpad() trim() replace()
concat(参数一，参数二) 拼接字符串
select ename || ‘的职位是’ || job from emp; –SMITH的职位是CLERK

select concat(concat(ename,’的职位是’),job) from emp;
concat函数嵌套使用concat函数

substr(参数一，参数二，[参数三]) 截取字符串
参数一: 待截取的字符对象
参数二：截取的开始位置
参数三：截取的长度
select substr(‘SQL Course’,5) from dual;–Course 包括开始位置，从开始位置截取
select substr(‘SQL Course’,5,4) from dual; –Cour
select substr(‘SQL Course’,-2) from dual; –se

length(参数一) ：获取字符串的长度
select ‘SQL Course’,length(‘SQL Course’) from dual;–10
select ename,length(ename) from emp;

instr(参数一，参数二，[参数三]，[参数四]) 获取指定字符的索引
参数一：待获取索引位置的字符
参数二：需获取索引位置的字符
参数三：开始查找的位置
参数四：指定字符第N次出现的位置
select instr(‘sql course’,’q’) from dual; –2

select instr(‘sql course’,’s’,3) from dual;
–9 从索引位置为3的地方开始搜索字符’s’

select instr(‘sql courses’,’s’,3,2) from dual;
–11 从索引位置为3的地方开始搜索字符’s’,查找第2次出现的位置

lpad(参数一，参数二，参数三) 左补全
select ‘V’ || lpad(empno,10,0) from emp;– V0000007369
select concat(‘V’,lpad(empno,10,0)) from emp; –V0000007369

rpad()(参数一，参数二，参数三) 右补全
select job,length(job),rpad(job,10,’*’) from emp;

trim() 去首尾字符
select trim(’ sql course ‘) from dual;–默认去前后空格
select trim(leading ‘I’ from ‘Isql courseI’) from dual; –sql courseI 去除头部的‘I’字符
select trim(trailing ‘I’ from ‘Isql courseI’) from dual; –Isql course 去除尾部的‘I’字符
select trim(both ‘I’ from ‘Isql courseI’) from dual; –sql course 去除首尾部的‘I’字符

replace() 替换字符串 将参数二的字符替换为参数三的新字符
select replace(’ Sql Cource ‘,’ ‘,”) from dual; –SqlCource
select replace(‘sql Cource’,’s’,’S’) from dual; –Sql Cource

---

数值函数

round() 四舍五入
select round(45.926),round(45.926,2) from dual; –46 45.93
select round(45.926,-1),round(455.926,-2) from dual; –50 500
select round(44.926,-1),round(445.926,-2) from dual; –40 400

trunc() 截断
select trunc(45.926),trunc(45.926,2) from dual; –45 45.92
select trunc(45.926,-1),trunc(455.926,-2) from dual; –40 400
select trunc(44.926,-1),trunc(445.926,-2) from dual; –40 400

mod() 取余、取模
select mod(10,3) from dual; – 1

---

日期函数

sysdate 获取当前系统日期
select sysdate from dual;

查询所有雇员入职的日期信息
select ename,hiredate,sysdate,trunc(sysdate - hiredate) 入职天数,trunc((sysdate - hiredate)/7) 入职星期数 from emp;

months_between 返回两个年份之间的自然月数
select months_between(sysdate,hiredate) from emp;
select months_between(‘31-12月-85’,’31-10月-85’) from dual;–2

add_months 添加月份
查询员工的入职日期，以及转正日期（暂定试用期6个月）
select ename,hiredate,add_months(hiredate,6) 转正日期 from emp;

next_day 返回指定日期的下一个日期
查询下一个星期三的日期
select next_day(sysdate,’星期三’) from dual;–2018/8/1 9:41:29

last_day 返回指定日期当月的最后一天日期
select last_day(sysdate) from dual; –2018/7/31 9:42:59

round() 日期四舍五入
查询81年入职的员工姓名，入职日期按月四舍五入的日期
select ename,
hiredate,
round(hiredate),
round(hiredate, ‘year’),
–年份按1-6月和7-12月四舍五入到最近的 几几年1月1日
round(hiredate, ‘month’),
–月份按1-15日和16-30日四舍五入到最近的 几月1日
round(hiredate, ‘day’)
–天数按周一-周三和周四到周日四舍五入到最近的 周日
from emp
where substr(hiredate, -2, 2) = ‘81’; –1980/12/17 17-12月-80

trunc() 日期截断
查询81年入职的员工姓名，入职日期按月截断的日期
select ename,
hiredate,
trunc(hiredate),
trunc(hiredate, ‘year’),
trunc(hiredate, ‘month’),
trunc(hiredate, ‘day’)
from emp
where substr(hiredate, -2, 2) = ‘81’; –1980/12/17 17-12月-80

extract() 抽取指定日期的年份、月份、日期
select extract(year from sysdate) 年,
extract(month from sysdate) 月,
extract(day from sysdate) 日
from dual;

---

转换函数： 字符串、日期、数值类型之间的转换 to_char to_date to_number

to_char 用于日期类型
select to_char(sysdate) from dual; –25-7月 -18 默认DD-MON-RR格式

select to_char(sysdate,’YYYY-MM-DD hh24:mi:ss’) from dual;
–2018-07-25 11:02:03

select to_char(sysdate,’YYYY/MM/DD hh24:mi:ss’) from dual;
–2018/07/25 11:02:03

select
to_char(sysdate,’YEAR’), –TWENTY EIGHTEEN
to_char(sysdate,’MONTH’), –7月
to_char(sysdate,’DAY’), –星期三
to_char(sysdate,’DY’), –星期三
to_char(sysdate,’AM’), –上午
to_char(sysdate,’YYYY/MM/DD hh12:mi:ss am day’)
from dual;

to_char 用于数值类型
–注意：进行数字类型到字符型转换时，格式中的宽度一定要超过实际列宽度，否则会显示为###
select to_char(123345789.123) from dual;–123345789.123

select to_char(123345789.126,’$999,999,999.99’) from dual; –$123,345,789.13

select to_char(123345789.126,’L999,999,999.99’) from dual;
–￥123,345,789.13

to_number 将字符串转换为数值
select ‘123.456’+100 from dual; –隐式转换，将字符串默认转换为数值计算
select to_number(‘123.456’)+100 from dual; –223.456 显示转换

to_date 将字符串转换为日期
select to_date(‘25-7月-18’) from dual;
–只带一个参数，格式必须为DD-MON-RR格式
select to_date(‘2018-07-25’,’YYYY-MM-DD’) from dual；
select to_date(‘2018/07/25’,’YYYY/MM/DD’) from dual;

---

–通用函数

nvl（参数一，参数二） 如果参数一为null,取参数二的值，否则，取参数一本身的值
select ename,comm,nvl(comm,0) from emp;

nvl2(参数一，参数二，参数三) 如果参数一位null,取参数三的值，否则，取参数二的值
select nvl2(null,’男’,’女’) from dual;–’女’
select nvl2(‘M’,’男’,’女’) from dual; – ‘男’

nullif(参数一，参数二) 如果参数一等于参数二，返回null,否则，返回第一个参数的值
select nullif(10,10) from dual;–null
select nullif(10,20) from dual;–10

coalesce(参数一，参数二,[参数三…..]) 返回不为null的参数值
select ename,comm,coalesce(comm,null,null,0) from emp;

case..when..then..else..end
select ename,job,sal,deptno,
case deptno
when 10 then ‘软件开发事业部’
when 20 then ‘测试部’
when 30 then ‘实施部’
else ‘产品部’
end
from emp;

decode
select ename,job,sal,deptno,decode(deptno,10,’软件开发事业部’,20,’测试部’,30,’实施部’,’产品部’) from emp;

函数嵌套(可以嵌套任意多层)
select ename, concat(ename,nvl(to_char(mgr), ‘No Manager’)) from emp where mgr is null;–KING