Oracle 使用技巧（一）

一、字段字符串拼接（wm_concat）

 

--wm_concat(字符串拼接) 相当于（('1', '2', '3', '4'....)）
select wm_concat(chr(39) || 拼接字段名称 || chr(39)) from 表名;

 

二、特殊字符或者符号检查（regexp_like）（regexp_like(字段名称,'^.*[a-zA-Z0-9]|\?|测试|[[:punct:]]|[[:space:]].*$')）

select *
  from 表名称 a
 where regexp_like(校验字段名称,'^.*[a-zA-Z0-9]|\?|测试|[[:punct:]]|[[:space:]].*$')
    or a.aac003 like '%' || chr(29) || '%';--chr(29)表示特殊空格符号

 

--将含有正则表达式的值替换成空
select regexp_replace(需要替换的字段名称,
                      '^*[[:space:]]|[【】!~\▌"’；·,''*+-./;=`、。‘*."]|[]]|*$',
                      '')
  from 表名称 t;

 

 

 

三、将字段值中含有 数字 的提取出来（translate）

--将含有数字的信息提取出来
select translate('100字段名称或者替换值123',
                 '#' || translate('100字段名称或者替换值', '0123456789', '#'),
                 '/')
  from dual;

四、Oracle查看表名称（USER_TAB_COMMENTS）、表字段名称（USER_COL_COMMENTS）

--查看表中的字段名称  
select * from USER_COL_COMMENTS T where t.comments like '%农村%'; --字段名称关键字
--查看表字段
select y.table_name, y.comments, t.column_name, t.comments
  from USER_COL_COMMENTS T, USER_TAB_COMMENTS y
 where t.table_name = y.table_name
   and t.column_name = 'AAB001' --字段名称
   and length(t.table_name) = 4;

五、统计两个字段之间的月数、如年龄。（months_between）

 1、months_between 函数返回两个日期之间的月份数。如果两个日期月份内天数相同，或者都是某个月的最后一天，返回一个整数，否则，返回数值带小数，以每天1/31月来计算月中剩余天数。

 

 2、months_between(sysdate, 字段名称) --取两个时间段的值
 3、trunc（） 函数是对时间类型或者数字进行截取操作的，默认截取0
TRUNC(months_between(sysdate, AAC006) / 12) --截取整月

select months_between(sysdate, sysdate - 62) 月数 from dual; --两个月零一天
select months_between(sysdate, sysdate - 61) 月数 from dual; --正好两个月
select sysdate - 12000 from dual;
select months_between(sysdate, sysdate - 12000) 月数,
       TRUNC(months_between(sysdate, sysdate - 12000) / 12) AS age1,
       TRUNC((to_char(sysdate, 'yyyyMMdd') -
             to_char(sysdate - 12000, 'yyyyMMdd')) / 10000) as age2
  from dual t;

六、统计两个字段之间的时间差（ROUND）

ROUND(TO_NUMBER(结束时间 - 开始时间))  天
mod(ROUND(TO_NUMBER(结束时间 - 开始时间) * 24), 24) 时
mod(ROUND(TO_NUMBER(结束时间 - 开始时间) * 24 * 60), 60) 分
mod(ROUND(TO_NUMBER(结束时间 - 开始时间) * 24 * 60 * 60), 60) 秒

select ROUND(TO_NUMBER(结束时间 - 开始时间)) || '天' ||
       ROUND(TO_NUMBER(结束时间 - 开始时间) * 24)|| '时' ||
       ROUND(TO_NUMBER(结束时间 - 开始时间) * 24 * 60) || '分' ||
       ROUND(TO_NUMBER(结束时间 - 开始时间) * 24 * 60 * 60) || '秒'
  from 表名称 t;    
    
select ROUND(TO_NUMBER(sysdate+5 - sysdate)) as 天,
       mod(ROUND(TO_NUMBER(sysdate+5 - sysdate) * 24), 24) 时,
       mod(ROUND(TO_NUMBER(sysdate+5 - sysdate) * 24 * 60), 60) 分,
       mod(ROUND(TO_NUMBER(sysdate+5 - sysdate) * 24 * 60 * 60), 60) 秒,
       ROUND(TO_NUMBER(sysdate+5 - sysdate)) || '天' ||
       mod(ROUND(TO_NUMBER(sysdate+5 - sysdate) * 24), 24) || '时' ||
       mod(ROUND(TO_NUMBER(sysdate+5 - sysdate) * 24 * 60), 60) || '分' ||
       mod(ROUND(TO_NUMBER(sysdate+5 - sysdate) * 24 * 60 * 60), 60) || '秒' 用时
  from dual t;