Oracle常用的单行函数应用技巧总结

单行函数;
   字符函数,数字函数,日期函数,转换函数(核心),通用函数(核心)

一:字符函数:

   .UPPER(字符串) 将字符串转为大写
   .LOWER (字符串) 将字符串转为小写 
   .INITCAP(字符串) 将首字母大写
   .LENGTH (字符串) 字符串的长度
   .REPLACE(字符串,'A','_') 将字符串字符A转换成_
   .SUBSTR (字符串,开始截取点,结束截取点)字符串的截取

 

 

oracle验证字符串,必须输入完整的sql语句,所以在oracle数据库中为了用户的查询方便,专门提供了一个虚拟的"dual"虚拟表

 

实例:转大写的函数(UPPER)
 select UPPER('hello') from dual;

实例:将emp表的名字大写
 select UPPER(ename) from emp;

实例;将emp表姓名小写
select LOWER(ename) from emp;

实例:将姓名的首字母大写
select initcap(ename) from emp;

实例:查询字符长度为5的数据
select * from emp where length(ename)=5;

实例:将字母a用下划线替换
select replace(ename,'a','_') from emp;

 

字符串的截取操作的两种语法:
      .语法一substr(字符串,开始点)表示从开始点一直截取到结尾
 实例; 从第三个字母开始截取
 select ename ,substr(ename,3) from emp;

     .语法二substr(字符串,开始点,结束点)表示从开始点截取一直截取点结束点

实例:截取前3个
select ename ,substr(ename,0,3) from emp;
select ename ,substr(ename,1,3) from emp;

Oracle里面的0和1都是代表第一个

实例:截取姓名的后三个字母
思路一:字符的长度-2
 

select ename, substr(ename,length(ename)-2) from emp;

 

思路二:设置负数,-3 从倒数截取第三个截取

select ename ,substr(ename,-3) from emp;

substr从0或者是1开始截取正确吗?
 .substr从0和1开始截取的效果是一样的
 .substr还可以设置负数从后面开始截

 

-----------------------------------------------------------------------------------------

二: 数字函数:

   ROUND(数字):四舍五入的操作
   trunc(数字):舍弃指定的小树
   mod(数字) :取摸,取余数

实例:round函数
select round(900.23),round(-120,90),round(900.23,2),round(-999.89,-3) from dual;

实例:trunc函数
select trunc(900.23),trunc(-120,90),trunc(900.23,2),trunc(-999.89,-2) from dual;


实例:mod函数
select mod(10,3) from dual;

 

----------------------------------------------------

三: 日期函数:

获得当前日期"sysdate"
select sysdate from dual;

 .日期+数字=日期 若干天后的天数
 select sysdate+3 from dual;

.日期-数字=日期 若干天前的天数
  select sysdate from dual;

.日期-日期=天数
实例:求出每个雇员到今天的天数
select ename ,hiredate,sysdate-hiredate from emp;

日期的操作函数
.last_day(日期) 指定日期的最后一天
实例:求出本月的最后一天
select last_day(sysdate) from dual;

.next_day(日期,'星期数')求出下一个星期数的日期
select next_day(sysdate,'星期一') from dual;

.add_months(日期,数字);求出若干月后的日期
select add_months(sysdate,4) from dual;


.months_between(日期1,日期2);日期1 到日期2之间的天数
select ename,hiredate, trunc(months_between(sysdate,hiredate)) from emp;

 

------------------------------------------------------------------

四: 转换函数(核心):

 数字,字符串,日期  三种转换函数
 .to_char(字符串,字符串格式) 将日期转化为字符串
 .to_date(字符串,格式字符) 将数字转换为date数据显示
 .to_number(字符串) 将字符串转化为数字

1,to_char函数
 实例: 查询系统时间
select sysdate from dual;
使用指定的格式显示 年yyyy  月mm 日 dd 时 hh 分 mi 秒ss

 实例:将时间转化为年-月-日
select to_char(sysdate,'yyyy-mm-dd') from dual;
 数据可能会出现前导0,消除前导加一个"fm"

实例:将时间转化为年-月-日 时-分-秒
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
此时显示的是字符串而不是数字


2,to_date函数
 实例;将1890-8-18转为数字类型的显示
select to_date('1890-8-18','yyyy-mm-dd') from emp;

3,to_number函数:Oracle很少用到

 

 

---------------------------------------------------------------

五:  通用函数(核心)

.nvl函数
  将null转为指定的字符
  实例;将emp表中的comm为null的转为0
  select nvl(comm,0) from emp;

.nvl2函数nvl2(comm,0,2)如果comm为null就转化为0,否则就转化为2
 select nvl2(comm,0,2) from emp;

 decode函数

decode函数,多数值判断
 decode语法:
     decode(job,'数值','转化的名称','数值','转化的名'..);

实例;将emp表中的职位用中文显示
 select  ename,sal,job,decode(job,'CLERK','清洁工') from emp; 
结果:
ENAME            SAL JOB       DECODE(JOB,'CLERK','清洁工')
---------- --------- --------- ----------------------------
SMITH        1000.00 CLERK     清洁工
ALLEN        1600.00 SALESMAN  
WARD         1250.00 SALESMAN  
JONES        2975.00 MANAGER   
MARTIN       1250.00 SALESMAN  
BLAKE        2850.00 MANAGER   
CLARK        2450.00 MANAGER   
SCOTT        3000.00 ANALYST   
KING         5000.00 PRESIDENT 
TURNER       1500.00 SALESMAN  
ADAMS        1100.00 CLERK     清洁工
JAMES         950.00 CLERK     清洁工
FORD         3000.00 ANALYST   
MILLER       1300.00 CLERK     清洁工