ORACLE数据库常用sql语句收集，方便使用时查询

目录

一、关于日期操作的相关的函数

（一）、日期函数

（二）、日期、字符串、时间戳之间的转换

二、递归函数

三、oracle中比较符号在mybatis中的转换

四、sys_guid()生成uuid函数及乱码解决

五、oracle删除重复数据保留一条

六、oracle行转列、列转行函数

七、把一个表中指定的字段数据更新到另外一张表里面

 

---

一、关于日期操作的相关的函数

（一）、日期函数

①、sysdate，该函数返回系统时间

②、months_between(d1,d2)，该函数用于计算日期d1和日期d1之间隔了多少个月，d1<d2则返回负数，反之则正数

select months_between(to_date('20200818', 'yyyymmdd'), to_date('20201018', 'yyyymmdd')) as months from dual --  -2
UNION all
select months_between(to_date('20201018', 'yyyymmdd'), to_date('20200818', 'yyyymmdd')) as months from dual -- 2

③、add_months(d,n)，该函数在日期d上增加n个月数，n为正数则加，n为负数则减

select add_months(TO_DATE('2020-08-31', 'yyyy-mm-dd'),-2) from dual --2020-06-30 00:00:00
UNION all
select add_months(TO_DATE('2020-08-31', 'yyyy-mm-dd'),2) from dual --2020-10-31 00:00:00

④、next_day(d, ‘星期*’)， 该函数是指定日期d下一个星期*对应的日期

select next_day(TO_DATE('2020-08-31', 'yyyy-mm-dd'), '星期五') as nextday from dual --2020-09-04 00:00:00

⑤、last_day(d)，该函数返回指定日期d所在月份的最后一天

select  last_day(TO_DATE('2020-09-01', 'yyyy-mm-dd')) from dual  --2020-09-30 00:00:00

⑥、extract(month from d)，该函数获取指定日期d的月份

SELECT extract(month from TO_DATE('2020-09-01', 'yyyy-mm-dd')) from dual -- 9
SELECT extract(month from TO_DATE('20201201', 'yyyymmdd')) from dual  --12

（二）、日期、字符串、时间戳之间的转换

①、to_char(d,format)：时间转字符串

select to_char(sysdate,'YYYY"年"MM"月"DD"日"') "时间转字符串" from dual  --2020年08月31日
UNION all
select to_char(sysdate,'yyyy-mm-dd') "时间转字符串" from dual  --2020-08-31

②、to_date(chr,format)：字符串转时间

select to_date('2020-08-31 17:08:21','yyyy-MM-dd HH24:mi:ss') "字符串转时间" from dual  --2020/8/31 17:08:21

③、to_char(systimestamp,format)：时间戳转字符串

select TO_CHAR(systimestamp,'YYYY-MM-DD hh24:mi:ss:ff3')from dual  --2020-08-31 17:04:32:028

④、TO_TIMESTAMP_TZ('2020-10-23 17:58:07.254000','YYYY-MM-DD HH24:MI:SS.FF')：字符串转时间戳

SELECT TO_TIMESTAMP_TZ('2020-10-23 17:58:07.254000','YYYY-MM-DD HH24:MI:SS.FF') from dual--2020-10-23 17:58:07.254000000 +08:00

二、递归函数

--select  * from  表   start with 条件1   connect by prior 条件2  
select  * from people   start with id='123'   connect by prior id=parentid   --向下查询
select  * from people   start with id='123'   connect by prior parentid=id   --向上查询

三、oracle中比较符号在mybatis中的转换

oracle	mybatis
>      (大于)	>
<      (小于)	&lt;
>=    (大于等于)	&gt;=
<=    (小于等于)	&lt;=
<>    (不等于)	&lt;&gt;

四、sys_guid()生成uuid函数及乱码解决

SELECT sys_guid() from dual ;  --+�>Ѭ�B|���W)+��
SELECT  rawtohex(sys_guid()) from dual;  --874455C82D3E4875853195AC6317FF85

使用sys_guid()在navicat上生成的uuid乱码时，可以利用数据库函数转换下（RAW是种二进制数据）即可 rawtohex(sys_guid())。

五、oracle删除重复数据保留一条

   id为2和5的数据重复

1、根据id查找表中重复的数据

                                                   

select * from a_demo where id in (select id from a_demo group by id having count(Id) > 1)

2、删除重复的数据，只保留一条（将查询语句改为delete即可删除）

                                                      

select * from a_demo WHERE (id) IN ( SELECT id FROM a_demo GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM a_demo GROUP BY id HAVING COUNT(*) > 1);

3、查询表中多个字段重复记录

                                                 

select * from a_demo a
where (a.Id,a.class) in (select Id,class from a_demo group by Id,class having count(*) > 1)

六、oracle行转列、列转行函数

1、行转列

                                                   

数据如上图，我需要将dict_label装换到一行中的一例里面，用逗号隔开，上sql

select listagg( to_char(DICT_LABEL),',') within group (order by DICT_LABEL) c from JS_SYS_DICT_DATA where DICT_TYPE='P_HONORTYPE'

结果如图  

2、列转行

数据如右图，，我需要将其转行为两行展示，上sql

select 
regexp_substr(honortype,'[^,]+',1,level) as honortype
from PROJPERSON where PROJPERSONID ='1341307905741766656'
connect by level<=regexp_count(honortype,'[^,]+') 
and rowid=prior rowid
and prior dbms_random.value is not null

结果如右图    

七、把一个表中指定的字段数据更新到另外一张表里面

merge into 表1 a
using  (select * from 表2 ) b
on (a.IDCARD = b.IDCARD)
when matched then
update set a.字段= b.字段

---------------------------------
update table1 set table1.colum= 
	(select table2.colum from table2 where table2.id = table1.id);

八、oracle去除富文本中的html标签

regexp_replace(  projectbrief,'</?[^>]*>|nbsp;|&','')