Oracle sql常用方法(持续更新)

1. 字符处理

---

1.去空格

--去前空格
select ltrim('   1 1   ') aa from dual;  --1 1   
--去后空格
select rtrim('   1 1   ') aa from dual;  --   1 1
--去前后空格
select trim('   1 1   ') aa from dual;   --1 1
--去前后中空格
select replace('   aa  kk  ',' ','') abcd from dual;  --aakk

2.去回车符、换行符、空格符

最近碰到一条数据 值为:童勇\n 在java里面看不出来(显示:童勇 ),数据库里也看不出来(显示:童勇 ) 但在程序里面总不能出现想要的结果。

制表符 chr(9)
换行符 chr(10)
回车符 chr(13)

--去除换行
update zhzl_address t set t.add_administration_num=replace(t.add_administration_num,chr(10),'');
--去掉回车
update zhzl_address t set t.add_administration_num=replace(t.add_administration_num,chr(13),'');
--去掉空格
update zhzl_address t set t.add_administration_num=trim(t.add_administration_num);

**参考:oracle中去掉回车换行空格的方法详解

2. oracle表复制

---

复制表结构及其数据:

create table table_name_new as select * from table_name_old

只复制表结构：

create table table_name_new as select * from table_name_old where 1=2;
或者：
create table table_name_new like table_name_old

只复制表数据：

--如果两个表结构一样：
insert into table_name_new select * from table_name_old

--如果两个表结构不一样：
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

3. Oracle对字符的判断(不知原因)

---

每条数据可能有如下状态：

如表中现在的数据：

如果我们现在要取状态220_WAIT_FINANCIAL_PRE_APPROVE 到 390_HAS_IMPORT，sql如下:

select sbh.boe_header_id, sbh.boe_status
  from sie.sie_boe_headers sbh
 where sbh.boe_status >= '220'
   and sbh.boe_status <= '390'
 order by sbh.boe_header_id desc;

结果如下：

注意:

select sbh.boe_header_id, sbh.boe_status
  from sie.sie_boe_headers sbh
 where sbh.boe_status > '220'
   and sbh.boe_status <= '390'
 order by sbh.boe_header_id desc;

这个sql查询的结果跟上面的结果是一样的。可这里明明写的是 >220。
所以需要成如下才会出现我需要的结果(改为 >221)。

select sbh.boe_header_id, sbh.boe_status
  from sie.sie_boe_headers sbh
 where sbh.boe_status > '221'
   and sbh.boe_status <= '390'
 order by sbh.boe_header_id desc;

正确结果:

4. 字符串 splitstr

** splitstr函数不是oracle官方函数，是自定义的。参考：https://www.cnblogs.com/gnielee/archive/2009/09/09/1563154.html

select column_value as employee from table(splitstr( 'BGI5819,BGI11625,BGI11150,BGI11273,BGI7987',','))

日期处理

字符转date的特殊用法

select date '2017-01-24' from dual;

date的加减处理

参考：
https://www.cnblogs.com/xiao-yu/archive/2011/05/24/2055967.html
https://www.cnblogs.com/xyz0601/archive/2015/04/11/4417165.html

无论是DATE还是timestamp都可以进行加减操作。
可以对当前日期加年、月、日、时、分、秒，操作不同的时间类型，有三种方法：

1. 使用内置函数numtodsinterval增加小时，分钟和秒【常用的单位有 (‘day’,’hour’,’minute’,’second’)】
2. 使用内置函数numtoyminterval增加年和月【常用的单位有’year’,’month’】
3. 加一个简单的数来增加天
4. 使用内置函数add_months来增加年和月

-- numtodsinterval
SQL> select sysdate,sysdate+numtodsinterval(3,'hour') as res from dual;
SYSDATE             RES 
------------------- -------------------
2007-09-05 01:45:34   2007-09-05 04:45:34

--numtoyminterval
SQL> select sysdate,sysdate+numtoyminterval(3,'year') as res from dual;
SYSDATE             RES 
------------------- -------------------
2007-09-05 01:54:53   2010-09-05 01:54:53

--对当前日期增加45秒
SQL> select sysdate, sysdate+numtodsinterval(45,'second') from dual ;

SYSDATE             SYSDATE+NUMTODSINTE
——————- ——————-
2010-10-14 21:40:06 2010-10-14 21:40:51

--对当前日期增加3天
SQL> select sysdate, sysdate+3 from dual ;

SYSDATE             SYSDATE+3
——————- ——————-
2010-10-14 21:40:46 2010-10-17 21:40:46

--对当前日期增加4个月
SQL> select sysdate, add_months(sysdate,4) from dual ;

SYSDATE             ADD_MONTHS(SYSDATE,
——————- ——————-
2010-10-14 21:41:43 2011-02-14 21:41:43

--当前日期增加2年
SQL> select sysdate, add_months(sysdate,12*2) from dual ;

SYSDATE             ADD_MONTHS(SYSDATE,
——————- ——————-
2010-10-14 21:42:17 2012-10-14 21:42:17

5. 行转列 wm_concat

sbh表中一个ID 对应 bl表是的多条数据

select sbh.boe_num, bl.boe_line_id
  from SIE.SIE_BOE_HEADERS SBH, sie.sie_boe_lines bl
 where sbh.boe_header_id = bl.boe_header_id
   and sbh.boe_num = 'CRBC201506090057';

转列后

select sbh.boe_num, wm_concat(bl.boe_line_id)
  from SIE.SIE_BOE_HEADERS SBH, sie.sie_boe_lines bl
 where sbh.boe_header_id = bl.boe_header_id
   and sbh.boe_num = 'CRBC201506090057'
 group by sbh.boe_num;

start with connect by prior 递归查询用法

https://www.cnblogs.com/benbenduo/p/4588612.html