Oracle个人学习常用函数汇总

Oracle函数

sysdate 日期函数

sysdate ：Oracle用来获取日期时间

select sysdate from dual;
#获取当前日期

具体常用方法：https://i.csdn.net/#/user-center/collection-list?type=1&spm=1001.2101.3001.4506

rowid和rownum

rowid： 用于定位数据表中某条数据的位置，是唯一的、也不会改变

rownum ：表示查询某条记录在整个结果集中的位置， 同一条记录查询条件不同对应的 rownum 是不同的而 rowid 是不会变的

---

rowid唯一不会改变，用于删除相同数据，具体sql:

delete from emp where rowid not in (select min(t.rowid)  from EMP t group by t.emp_no)
#分组查询所有rowid，将最小的都排除，剩下的就是重复的，即可进行删除。

rownum可以对数据条数进行取出，例如：

select * from (select rownum no,t.* from emp t order by name)   where no between 3 and 7 order by name desc
#先将所有rownum条数通过那么排序查询出来，然后取3-7区间段的数据
select rownum,id,name from student where rownum <3;
#查找两条数据

substr 字符串截取函数

substr:函数格式

格式1： substr(string string, int a, int b);

• string 需截取的字符串

• 截取的开始位置，（0,1都表示为第一个元素）

• b要截取的字符串长度

格式2：substr(string string, int a) ;

• string 需截取的字符串

• a 从第a个元素到最后一个元素

---

select ename,
substr(ename,1),   #从第一位开始截取，截取到最后；
substr(ename,-3),  #从倒数第三个开始截，截取至结尾;
substr(ename,3,3)  #从第三个开始截，截取3位;
from emp;

#常和instr配合使用 instr()函数的格式（俗称：字符查找函数）
substr(信息,instr(信息,'|',1,3)+1,instr(信息,'|',1,4)-instr(信息,'|',1,3)-1) 年龄

instr()字符查找函数

用法：

1. instr（’源字符串’ , ‘目标字符串’ ,’开始位置’,’第几次出现’）----》第n个下标，比如分割｜｜｜｜之间的内容，配合substr使用
2. instr（’源字符串’ , ‘目标字符串’）

select instr('helloworld','l',2,3) from dual;    #返回结果：9 也就是说:在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置
select instr('helloworld','lo') from dual; #返回结果：4 即：在“lo”中，“l”开始出现的位置

decode()条件函数

使用方式：

1. decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

IF 条件=值1 THEN
　　　　RETURN(返回值1)
ELSIF 条件=值2 THEN
　　　　RETURN(返回值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(返回值n)
ELSE
　　　　RETURN(缺省值)
END IF

# 具体sql
select id,name,
	decode(id,1,'第一个',2,'第二个',3,'第三个','没有') new_id 
from t_decode;

2. decode(字段或字段的运算，值1，值2，值3）

这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

常与sign函数配合使用：

decode(sign(avg(t.salary + t.bonus)-3000),1,'3000以上',-1,'3000以下',0,'持平') 平均收入水平

sign()运算函数

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

用来进行运算比较，常用于和decode联合使用。

---

SELECT sign(120 - 100) FROM DUAL;   --  1
SELECT sign(100 - 100) FROM DUAL;   --  0
SELECT sign(100 - 120) FROM DUAL;   -- -1

to_char()字符转换函数

可以进行对日期格式的转化，也可以在sql中拼接不能添加的字符，如单引号：to_char(38)

select a.name,to_char(a.join,'yyyy-mm-dd') 日期,to_char(a.join,'yyyy"年"mm"月"dd"日"') 中文显示 from emp1 a where name='李丽'

输出样式为：

select  to_char(sysdate,'dl') from dual;   #获取当前日期和星期几
select   to_char(sysdate,'yyyy')  from dual; #获取年

#字符到日期
select to_date(’2003-10-17 21:15:37’,’yyyy-mm-dd hh24:mi:ss’) from dual  
#日期到字符
select sysdate,to_char(sysdate,’yyyy-mm-dd hh:mi:ss’) from dual   

to_char(数据,FM999990.09) 格式化函数

• 其中 9 代表 如果存在数字则显示数字，不存在显示空格

• 其中 0 代表 如果存在数字则显示数字，不存在则显示0， 即占位符

• 其中 FM 代表 如果是因为9 带来的空格则删除

to_date日期转换函数

1. 在使用Oracle的to_date函数来做日期转换时，可能会直觉地采用“yyyy-MM-dd HH:mm:ss”的格式作为格式进行转换，但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。如：select to_date(‘2005-01-01 13:14:20’,‘yyyy-MM-dd HH24:mm:ss’) from dual;原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。select to_date(‘2005-01-01 13:14:20’,‘yyyy-MM-dd HH24:mi:ss’) from dual;
2. 另要以24小时的形式显示出来要用HH24

#具体转化为时间
to_date('2001/1/1 12:12:12','yyyy-mm-dd hh24:mi:ss')

#字符到日期
select to_date(’2003-10-17 21:15:37’,’yyyy-mm-dd hh24:mi:ss’) from dual  
#日期到字符
select sysdate,to_char(sysdate,’yyyy-mm-dd hh:mi:ss’) from dual   

months_between()函数

MONTHS_BETWEEN (date1, date2):用于计算date1和date2之间有几个月。

• 如果date1在日历中比date2晚，那么MONTHS_BETWEEN()就返回一个正数。
• 如果date1在日历中比date2早，那么MONTHS_BETWEEN()就返回一个负数。
• 如果date1和date2日期一样，那么MONTHS_BETWEEN()就返回一个0。

---

#计算两月之间的差别，正常是大月在前，小月在后
select months_between(to_date('2014-3-21','yyyy-mm-dd'), to_date('2014-1-10','yyyy-mm-dd')) months from dual;
#有时候在同一个月，0.2显示出来前面没有0，则需要字符串进行转换一下
Select name 姓名,to_char(months_between(to_date(sysdate),to_date(to_char(join,'yyyy-mm-dd'),'yyyy-mm-dd') ),'fm9990.09')||'月' 月份数 from emp1 where rownum <4;

输出样式:

round()与trunc()函数

总的归纳：

• 对于取数来说，如果位数足够trunc是取小数不进行四舍五入，round要进行四舍五入。
• 对于时间来说，round是根据下一位的值进行四舍五入，例如取月，当后面的日数＞15天，则月数+1。trunc则是直接取当前年月日，后面的值都为最初值，例如2021/11/18，取年就是2021/1/1，月则是2021/11/1

---

案例实现：

#对于日期取值，判断最准确的差距，应该使用round取到下一位，更加准确，比如月就取到天
select round(to_date('2001/7/18 ','yyyy-mm-dd'),'mm') from dual   #2021/8/1
select trunc(to_date('2001/7/18 ','yyyy-mm-dd'),'mm') from dual   #2021/7/1

#数取值
select round(5555,-2) from dual   #5600
select trunc(5555,-2) from dual   #5500

select round(5555.555,2) from dual  #5555.56
select trunc(5555.555,2) from dual  #5555.55