oracle常用函数

表空间相关

–查看数据文件空间使用情况
select 　b.file_name 物理文件名,
　　b.tablespace_name 表空间,
　　b.bytes / 1024 / 1024 大小M,
　　(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
　　substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率 　　
from dba_free_space a,
dba_data_files b 　
where a.file_id = b.file_id 　　
group by b.tablespace_name, b.file_name,b.bytes order by b.tablespace_name;

–表空间文件扩容
alter database datafile ‘/opt/oracle/oradata/HDC_ODS2.dbf’ resize 30000M;

–查看表空间路径
select * from dba_data_files

–新增表空间文件
alter tablespace HDC_ODS add datafile ‘/opt/oracle/oradata/HDC_ODS3.dbf’ size 30000M;

日期相关

1. 当前日期

   sysdate

2. 日期转字符串

   to_char(sysdate,‘YYYY’)

   to_char(sysdate,‘yyyy-mm-dd hh:mm:ss’)

3. 字符串转日期

   to_date(‘2022-4-1’,‘yyyy-mm-dd’)

4. 日期加减，加用+，减用-

   当前日期加一年：add_months(sysdate,12)

   当前日期加一月：add_months(sysdate,1)

   当前日期加一周：sysdate+7

   当前日期加一天：sysdate+1

   当前日期加一小时：sysdate+1/24

   当前日期加一分钟：sysdate+1/24/60

   当前日期加一秒：sysdate+1/24/60/60

5. 日期格式转换

   trunc(sysdate,‘yyyy’-mm) 精确到月

分支函数

1. decode

   deocde(age,18,‘aa’,‘19’,‘bb’,‘cc’)

   age=18为aa，age=19为bb，其他为cc

2. case when then else end

   case when age < 18 then ‘aa’ when age > 18 and age <60 ‘bb’ else ‘cc’ end

   age<18为aa，18<age<60为bb，其他为cc

字符串相关

1. 字符串拼接

   concat(x,y) 只能连接两个字符串

2. 字符串截取

   ltrim(x,‘a’) 删除左边开头的所有a

   rtrim(x,‘a’) 删除左边开头的所有a

   trim(x,‘a’) 删除两侧的所有a

   substr(str,start,[length])截取从start开始的length长度，没有length截取到尾

3. 字符串替换

   replace(str,old,new) 替换所有old为new

4. 字符串查找

   instr(X,str,[start])从start开始位置查找str，返回第一次出现的位置

排名相关

1. 根据AA分区，BB正序排序，序号为 1 2 3 4

   row_number() over(partition by AA order by BB)

2. 根据AA分区，BB正序排序，序号为 1 2 2 4

   rank() over(partition by AA order by BB)

3. 根据AA分区，BB正序排序，序号为 1 2 2 3

   dense_rank() over(partition by AA order by BB)