Oracle使用技巧

本人最初的几年工作，主要从事数据开发方向的工作，2015年流传着有钱的公司使用Oracle，没钱的公司使用开源MySQL。当时鄙人在无锡中国移动通信公司接触了一年多个Oracle，整理了一些笔记。

1、case when

update tb a set score = (
    case when a.product_level ='A' then 1.15
    when a.product_level ='B' then 1
    when a.product_level ='C' then 0.85 end
) 

update tb a set a.reward_bas =
    (case when a.lljz>70 then 70 else lljz end)

2、replace函数

update tb a set mon = (replace(mon,5,55555))
 where type is not null;

-- 将remark为空的情况替换成‘被子’
update tb a set a.remark = replace(a.remark,'被子');

3、trim&upper函数

-- 去除两边空格，并且属性转化为大写
select trim(upper(attr)) from tb

4、ddl

alter table tb add (type varchar(20));
alter table tb drop column type;
alter table tb rename column type_1 to type_2;
alter table tb modify xxx_id number;
alter table tb move tbspace_1 tbspace_2; -- 移动表空间

truncate table tb; -- 清空表中数据，包括自增的主键信息
drop table tb; -- 删除表
select * from tb for update;-- 编辑表，获得写锁，锁表
select a.rowid, a.* from tb a;-- 编辑表

5、number转化成varchar

select to_char(123, 'FM9999.999') from dual;

6、查询前10名

select * from
(
  select a.*, 
  row_number() 
  over (partition by id order by score asc, age desc) nn
  from tb a
) 
order by nn limit 10

7、存储过程

call com_p_Xxxxxx('A', 'B', 'C'); 唤醒测试存储过程

8、decode函数，比case when更好用，局限性非黑即白

select decode(sex, '男', 1, 0) a from tb

9、快速复制一个表

create table tb as
select cast(xxx_id as varchar2(100)) xxx_id -- 修改某一列
from tb where 1<>1
-- 说明：cast是将某一列转化成某个类型，数据类型转换

10、基于现有表新增四列创建新表

create table new_tb as
    select a.*,
        cast(null as number) col1,
        cast(null as varchar2(100)) col2,
        cast(null as varchar2(100)) col3,
        cast(null as varchar2(100)) col4
    from tb a where 1<>1

11、merge…into…合并更新

merge into tb_1 a using tb_2 b
   on (a.id = b.aid)
   WHEN MATCHED THEN UPDATE set a.org_id = b.org_id
   where a.org_id is null;

12、nvl

select nvl(attr, 0) from dual; -- 当attr字段为空时默认为0

13、dblink

指的是在不同实例下的数据库进行通信。

14、删除重复列

delete from tb a
 where exists(
    select 1 from tb b where a.id = b.id and a.rowid > b.rowid
 )

15、在某列有大量数据的情况下，修改某一列的格式

alter table tb add attr_1 varchar2(40);
update tb a set a.attr_1 = a.attr;
commit;
alter table tb drop column attr;
alter table tb rename column attr_1 to attr;

16、查看表空间大小及剩余

select a.a1 表空间名称,
       c.c2 类型,
       c.c3 区管理,
       b.b2 / 1024 / 1024 / 1024 表空间大小G,
       (b.b2 - a.a2) / 1024 / 1024 已使用M,
       substr((b.b2 - a.a2) / b.b2 * 100, 1, 5) 利用率
  from (select tablespace_name a1, sum(nvl(bytes, 0)) a2
          from dba_free_space
         group by tablespace_name) a,
       (select tablespace_name b1, sum(bytes) b2
          from dba_data_files
         group by tablespace_name) b,
       (select tablespace_name c1, contents c2, extent_management c3
          from dba_tablespaces) c
 where a.a1 = b.b1
   and c.c1 = b.b1;

17、取余函数mod

select mod(10, 3) from dual;

18、union all…

create table tb as
select * from tb_a 
union all
select * from tb_b c,tb_c d
  where c.custid=d.id(+) -- (+) 表示右关联查询

19、恢复删除的表(在commit之前)

flashback table tb to before drop

20、随机去除5W条数据排序

select * 
from (select * from tmp order by total desc)
where rownum <= 20000

21、分组函数wm_concat

select id,wm_concat(name)
  from tb a
group by id

22、并行执行

delete /*+parallel(a 4)*/ from tb a where id = 1