Oracle12C--函数（十三）

知识点的梳理：

• 在Oracle 中所有的查询都必须符合标准的sql 语句，在from 子句之后必须有一张表的名称。
  • 在验证函数功能时，可以使用Oracle提供的"dual"虚拟表来实现查看结果；
• 在进行数学计算中，如果存在了Null,则结果就为null，此时可以使用nvl()或nvl2()函数将null转为指定的内容；
• 图解函数结构

  

• 单行函数
  • 完成特定功能的函数，就是单行函数；
  • 定义语法：单行函数可以接收一个数据表中的操作列，也可以接收具体的计算结果

funcation_name(列 | 表达式[,参数1,参数2])

• 字符函数
  • 接收数据返回具体的字符信息；

函数名称	功能	示例
upper('字符串')	将全部字符串变为大写	select upper('hello') from dual; --输出'HELLO', select * from emp where ename = upper('hello'); --查询emp表中，列名为ename，值为HELLO的数据
lower('字符串')	将字符串变为小写	select lower('HELLO') from dual;
initcap('字符串')	首字母大写	select initcap('hello') from dual; --输出'Hello' select ename 原始姓名，initcap(ename) 姓名开头首字母大写 from emp;
concat(str1 , str2 )	字符串拼接
substr(str , 开始索引 , 子串长度) substr(str,截取开始点)	截取部分字符串， 从指定位置截取到结尾 注意： 该函数的索引，非常灵活； 支持0和1，它们是一样的	select * from emp where sustr(ename,0,3) = 'JAM'; --截取姓名前三个字母是'JAM'的雇员信息 select ename 原姓名,substr(ename,3) 截取之后的姓名 from emp where deptno=10; --查询所有10部门雇员的姓名，但是不显示每个雇员姓名的前3个字母 select ename,substr(ename,-3) from emp; --显示每个雇员姓名及其姓名的后3个字母 写法2：select ename,substr(ename,length(ename)-2) from emp; --效果同上
length('字符串')	返回参数长度	select length('hello') from dual; --输出5 select * from emp where length(ename) = 5; --查询出姓名长度是5的所有雇员信息
lengthb('字符串')	将字符串以字节的长度输出	select lengthb('hello') from dual; --一个英文字母的byte长度为1，汉字的byte长度为3
instr(str1 , str2)	通过此函数进行子字符串查找的时候，返回的就是子字符串的起点位置，而且区分大小写； 查找不到返回0	select instr('MLDN Java','MLDN') 查找得到, instr('MLDN Java','Java') 查找得到, instr('MLDN Java','JAVA') 查找不到 from dual;
lpad(str1,length,str2)	参数str1的长度应为length，如果不足，就用str2在左边补足length	select lpad('MLDN',10,'*') LPAD函数使用，RPAD('MLDN',10,'*') RPAD函数使用,LPAD(RPAD('MLDN',10,'*'),16,'*') 组合使用
rpad(str1,length,str2 )	同上，不过是在右边
trim(str)	去掉str两端的空白
ltrim(字符串)，rtrim(字符串)	去掉左或者右空格	select ' MLDN LiXingHua ' 原始字符串，LTRIM(' MLDN LiXingHua ') 去掉左空格 from dual; --右空格同理
replace(str1,str2,str3 )	把str1中的str2全部替换为str3	select ename,replace(ename,'A','_') from emp; --将雇员姓名中所有的字母'A'，替换为'_'
ASCII(字符)	返回与指定字符对应的十进制数字	select ascii('L') from dual;
CHR(数字)	给出一个整数，并返回与之对应的字符	select chr(100) from dual;

• 数值函数
  • 对数字进行处理，例如四舍五入；

函数名称	功能	示例
round(num , x)	四舍五入,保留num小数点后x位； 如果不指定，则表示将小数点之后的数字进行四舍五入	select round(789.652) 不保留小数 ,round(789.652,2)保留两位小数 ,round(789.652,-1) 处理整数进位 from dual;    select empno,ename,job,hiredate,sql,round(sal/30,2) 日薪金 from emp;
trunc(num , x)	截断 num小数点后x位； 不指定x，不保留小数	select trunc(789.652) 截取小数,trunc(789.652,2) 截取两位小数,trunc(789.652,-2) 取整 from dual;
mod(num , x)	求余/取模num%x	select mod(10,3) from dual;

• 日期函数
  • 直接对日期进行相关操作的函数，对于Oracle的日期类型，去这里详细了解：Oracle日期格式

函数名称	功能	示例
add_months(日期,数字)	向指定日期中加上若干月数； 此函数考虑到了闰年的问题，如果使用日期加减数字的方式则无法进行精确的日期操作	select add_months(sysdate,1) 一个月之后的日期, add_months(sysdate,-1) 一个月之前的日期 from dual;    select empon,ename,job,sal,hiredate,add_months(hiredate,3) from emp; --显示所有雇员在被雇佣三个月之后的日期
months_between(日期1,日期2)	两个日期相差的月数	select months_between(sysdate,'1990-05-11') from dual; months_between()的复杂示例
next_day(日期，星期数)	获取下一个星期x的具体日期	select sysdate,next_day(sysdate,'星期日') 下一个星期日, next_day(sysdate,'星期一') 下一个星期一 from dual;
last_day(date)	本月的最后一天	select sysdate,last_day(sysdate) from dual;
extract(格式 from 数据)	日期时间分割，或计算给定两个日期的间隔 extract()的复杂示例	select extract(year from date '2001-09-19') years, extract(month from date '2001-09-19') months, extract(day from date '2001-09-19') days from dual; --从日期时间中取出年月日
round(date,'格式')	日期四舍五入 可用的格式有'MONTH','YEAR'	select round(sysdate,'month') from dual; select round(sysdate,'day') from dual;
trunc()	日期截断

• months_between()的复杂示例
  • 查询出每个雇员的编号，姓名，雇佣日期，雇佣的月数以及年份
    • 分析：求出雇佣的月数，自然使用months_between()函数。而对于年份，如果要使用（天数/365）的话，就不准确了，因为要考虑闰年的问题。所以还是要使用months_between()函数，先求出到今天为的雇佣月数之后的除以12；
    • select empno 雇员编号,ename 雇员姓名,hiredate 雇佣日期,

      trunc(months_between(sysdate,hiredate)) 雇佣总月数,

      trunc(months_between(sysdate,hiredate)/12) 雇佣总年份

      from emp;

      

  • 查询出每个雇员的编号，姓名，雇佣日期，已雇佣的年数，月数，天数
    • 步骤一：求出每个雇员的雇佣年数，年份可以直接利用"雇佣的总月数/12"的形式取得，为了避免小数，使用trunc()截取掉

select empno 雇员编号,ename 雇员姓名,hiredate 雇佣日期, trunc(months_between(sysdate,hiredate)/12) 已雇佣年数 from emp;

此步骤的查询结果：

• 步骤二：求出每个雇员雇佣的月数，在计算年份时采用的公式是"months_between(sysdate,hiredate)/12",在使用months_between()函数求出全部的月数后除以12将产生余数，所以这个余数就是雇员雇佣的月数，可以使用mod()函数求出模，就为每个雇员雇佣的月数

select empon 雇员编码,ename 雇员姓名,hiredate 雇佣日期, trunc(months_between(sysdate,hiredate)/12) 已雇佣年数, trunc(mod(months_between(sysdate,hiredate),12)) 已雇佣月数 from emp;

• 步骤三：求出每个雇员雇佣的天数，计算天数最准确的方法就是（日期 - 日期），其中有一个肯定是当前日期（sysdate），但是另外一个日期就必须尽可能准确了，而且两个日期之间的天数不应该超过30天，所以最后得出计算公式"当前日期 - （雇佣日期+距离今天为止所雇佣的月）=天数"

select empno 雇员编号,ename 雇员姓名,hiredate 雇佣日期, trunc(months_between(sysdate,hiredate)/12) 已雇佣年数, trunc(mod(months_between(sysdate,hiredate),12)) 已雇佣月数, trunc(sysdate -add_months(hiredate,months_between(sysdate,hiredate))) 已雇佣天数 from emp;

• 以上三步就计算出了每个雇员已雇佣的年数，月数，天数；

• extract()的复杂示例
  • 取得时间间隔

select extract(day from to timestamp('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') -to timestamp('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days, extract(hour from datetime_one-datetime_two) hours, extract(minute from datetime_one - datetime_two) minutes, extract(second from datetime_one - datetime_two) seconds from( select to_timestamp('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one, to_timestamp('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss') datetime_two from dual);

• 转换函数
  • 日期，字符，数字之间可以完成互相转换的功能
  • 显示转换函数
    • 图解

      

函数名称	功能	示例
to_char(日期|数字|列，转换格式)	1.将指定的数据按照指定的格式变为字符串型， 2.可以通过该函数，将"31-1月-12"，这样的日期格式转换为'2012-01-31'这样的字符串格式 3.对于日期格式的介绍，还要看这里：日期参数 4.此函数还可以完成数字的格式化操作：数字格式 5.另外，日期的格式化标记也是不区分大小写的； 6.to_char()属于格式化函数，在进行格式化转换的操作中使用最多，而在Java中也提供了与之对应的一个API： DateFormat(日期格式化)	select sysdate 当前系统时间，to_char(sysdate,'YYYY-MM-DD') 格式化日期, to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') 格式化日期时间, to_char(sysdate,'FMYYYY-MM-DD HH24:MI:SS') 去掉前导0的日期时间 from dual; --格式化当前的日期时间 其他方法格式化年，月，日 select sysdate 当前系统时间,to_char(sysdate,'year-month-dy') 格式化日期 from dual; 查询出所有在每年2月份雇佣的雇员信息 select * from emp where to_char(hiredate,'MM') = '02'; 由于Oracle存在着数据类型的自动转换功能，所以也可以将以上的'02'写成'2' select * from emp where to_char(hiredate,'MM') =2;
to_date(字符串|列，转换格式)	将指定字符串按照指定的格式变为date型； 此函数只在数据的更新操作上使用较多，查询显示的时候使用的并不多；	select to_date('1979-09-19','yyyy-mm-dd') from dual;
to_timestamp()	将数据变为时间戳	select to_timestamp('1981-09-27 18:07:10','yyyy-mm-dd hh24:mi:ss') datetime from dual;
to_number(字符串|列)	将指定的数据类型变为数字型	select to_number('09') + to_number('19') 加法计算, to_number('09') * to_number('19') 乘法计算 from dual; --使用to_number()进行显示转换 因为Oracle数据库中存在着数据的隐式转换，所以不使用此函数也可以达到一样的效果 select '09'+'19' 加法计算,'09'*'19' 乘法计算 from dual; 以上两句Sql的效果是一样的：

• 隐式数据类型转换
  • varchar2 <--> number

select '1'+'2' from dual; --字符串隐式转换数字，输出3 select 1 || 2 from dual; --数字隐式转换字符串，输出12 select '2015-10-01' || 2 from dual; --字符串与数字转换为字符串，输出2015-10-012

• varchar2 <--> date

update emp set hiredate='2015-01-01'; --更新emp 表的日期数据 select sysdate || 'hello' from dual; --输出2016-08-24hello

  

• 通用函数
  • Oracle自己提供的函数,这些函数适用于任何数据类型，同时也适用于空值：

函数名称	功能	示例
nvl(expr1, expr2)	如果expr1为null,则返回expr2,否则返回expr1	select empno,ename,job,hiredate,(sql+nvl(comm,0))*12 年薪 from emp;
nvl2(expr1,expr2,expr3)	如果expr1为null,返回expr3,否则返回expr2	select empno,ename,nvl2(comm,sal+comm,sal),sal,comm from rmp;
nullif (expr1, expr2)	如果expr1=expr2,返回null,否则返回expr1; 注意：该函数的第一个参数，也就是expr1不能是null，如果设置为null，会出现语法错误	select nullif(1,1),nullif(1,2) from dual;
coalesce(expr1, expr2, ...)	如果expr1的值为null,则显示expr2，依次类推，如果全是null,就返回Null	select ename,sal,comm,coalesce(comm,100,2000),coalesce(comm,null,null) from emp;
decode(expr,search1, result1 [, search2, result2,..., default])	检查expr的值 ,如果等于search1 , 则返回result1 ; 如果等于search2,则返回result2 , 如果都没找到,则返回default	select decode(2,1,'内容为一',2,'内容为2'),decode(2,1,'内容为一','没有条件满足') from dual;
case 列|数值 when 表达式1 then 显示结果1... else 表达式n ... end	用于实现多条件判断，在when之后编写条件，而在then之后编写条件满足的显示操作，如果都不满足则使用else中的表达式处理	select ename,sal, case job when 'cleak' then sal*1.1 when 'salesman' then sal*1.2 when 'manager' then sal*1.3 else sal*1.5 from emp;