参考链接

Oracle中replace函数的使用
Oracle round函数是什么意思?怎么运用?
oracle的nvl
Oracle 中 decode 函数用法
[oracle] to_date() 与 to_char() 日期和字符串转换
Oracle的Cast的用法
Oracle 大小写转换函数——博客园@Twang

正文

常用函数

判断是否为数字

# 注意只能判断纯数字，不带小数,判断带小数方式请查看下文“常用sql”创建函数
SELECT nvl2(translate('123','/1234567890','/'),'CHAR','NUMBER')   
FROM   dual ;

add_months()日期增加，以月为单位

add_months(sysdate,12)--增加一年
add_months(sysdate,-12)--减去一年
sysdate+1 --加一天

to_date()

to_date("要转换的字符串","转换的格式")
to_date(t.access_date,'yyyy-mm-dd hh24:mi:ss')--2005-12-25 13:25:59
TO_DATE('17-DEC-1980', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=American')--日期语言

replace替换字符

replace(原字段，'原字段旧内容','原字段新内容')--替换字符串

round四舍五入

`round(number)`
`round(number, decimal_places )`

number ---需要四舍五入的数字
decimal_places ---从哪里开始四舍五入，此参数是下标，预设为0

select round(123.456, 0) from dual;     --- 123 

nvl如果为空返回新值

 nvl(字段名，'新的返回的值')
如果提供的字段的值为空，则将返回这个新值，注意：只是返回了这个值，并不是update到表中

 nvl(name,'小明')---name为空，返回小明

decode逻辑判断简化

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

decode(字段或字段的运算，值1，值2，值3）

该函数的含义如下：
 这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
 当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

注意：值2和值3的数据类型必须一致

sys_guid()生成唯一32位字符串

sys_guid()

CAST(expr AS type_name) 数值类型转换

--例
cast(R.MONTH as int)--将月份转换为整型类型

大小写转换

select UPPER('Test') as u from dual; --转大写
select LOWER('Test') as l from dual;--转小写

截取字符串

--截取身份证出生日期
to_date(substr('XXXXXXXXXXXXXXXXX',7,8),'YYYYMMDD') 

删除左右字符、添加左右字符

ltrim(原字符,'需要删除的字符')--删除左边字符
rtrim(原字符,'需要删除的字符')--删除右边字符
LPAD(原字符,'需要添加的字符') --添加字符在左边
RPAD(原字符,'需要添加的字符') --添加字符在右边
--例
ltrim('abcdefg','abc')--删除左边abc，输出defg
ltrim('abqwert','abc')--删除左边ab，输出qwert

sql语句备份(注意以下sql语句需要根据实际修改，认真查看)

判断数字

create or replace function isNumber(p in varchar2)
return number
is
result number;
begin
result := to_number(p);
return 1;
exception
when VALUE_ERROR then return 0;
end;

时间处理

导出表结构

SELECT B.TABLE_NAME     AS "表名",
       C.COMMENTS       AS "表说明",
       B.COLUMN_ID      AS "字段序号",
       B.COLUMN_NAME    AS "字段名",
       B.DATA_TYPE      AS "字段数据类型",
       B.DATA_LENGTH    AS "数据长度",
       B.DATA_PRECISION AS "整数位",
       B.DATA_SCALE     AS "小数位",
       A.COMMENTS       AS "字段说明"
  FROM ALL_COL_COMMENTS A, ALL_TAB_COLUMNS B, ALL_TAB_COMMENTS C

WHERE A.TABLE_NAME IN (SELECT U.TABLE_NAME FROM USER_ALL_TABLES U)
   AND A.OWNER = B.OWNER
   AND A.TABLE_NAME = B.TABLE_NAME
   AND A.COLUMN_NAME = B.COLUMN_NAME
   AND C.TABLE_NAME = A.TABLE_NAME
   AND C.OWNER = A.OWNER
   AND A.OWNER = 'PYE'
ORDER BY A.TABLE_NAME, B.COLUMN_ID;

修改不符合的时间，修改年份和月份

--更新有/的时间、有两个/的日期、月份为1位数的，改为两位数
select  (substr(t.stime,1,5)||'0'||substr(t.stime,6,length(t.stime))),substr(t.stime,1,5)||'0'||substr(t.stime,6,length(t.stime)),(length(substr(t.stime,0,7))-length(replace(substr(t.stime,0,7),'/',''))),t.stime,t.*,t.rowid From t_test_cc_all_b20181212 t --where substr(t.stime,length(t.stime),length(t.stime)-1)='-'
WHERE length(t.stime)<10 and  (length(t.stime)-length(replace(t.stime,'/','')))>=2 and  (length(substr(t.stime,0,7))-length(replace(substr(t.stime,0,7),'/','')))=2

update  t_test_cc_all_b20181212 t set t.stime=(substr(t.stime,1,5)||'0'||substr(t.stime,6,length(t.stime))) --where (length(t.stime)-length(replace(t.stime,'-',''))) =1
WHERE length(t.stime)<10 and  (length(t.stime)-length(replace(t.stime,'/','')))>=2 and  (length(substr(t.stime,0,7))-length(replace(substr(t.stime,0,7),'/','')))=2

--更新有/的时间、有两个/的日期、年份为1位数的，改为两位数
select (substr(t.stime,1,length(t.stime)-1)||'0'|| substr(t.stime,length(t.stime),1)), t.stime,t.*,t.rowid From t_test_cc_all_b20181212 t --where substr(t.stime,length(t.stime),length(t.stime)-1)='-'
WHERE length(t.stime)<10 and  (length(t.stime)-length(replace(t.stime,'/','')))>=2 and   (length(substr(t.stime,length(t.stime)-1,2))-length(replace(substr(t.stime,length(t.stime)-1,2),'/','')))=1

update  t_test_cc_all_b20181212 t set t.stime=(substr(t.stime,1,length(t.stime)-1)||'0'|| substr(t.stime,length(t.stime),1)) --where (length(t.stime)-length(replace(t.stime,'-',''))) =1
WHERE length(t.stime)<10 and  (length(t.stime)-length(replace(t.stime,'/','')))>=2 and   (length(substr(t.stime,length(t.stime)-1,2))-length(replace(substr(t.stime,length(t.stime)-1,2),'/','')))=1


--更新有/的时间、有两个-的日期、月份为1位数的，改为两位数
select  (substr(t.stime,1,5)||'0'||substr(t.stime,6,length(t.stime))),t.stime,t.*,t.rowid From t_test_cc_all_b20181212 t --where substr(t.stime,length(t.stime),length(t.stime)-1)='-'
WHERE length(t.stime)<10 and  (length(t.stime)-length(replace(t.stime,'-','')))>=2 and  (length(substr(t.stime,0,7))-length(replace(substr(t.stime,0,7),'-','')))=2

update  t_test_cc_all_b20181212 t set t.stime=(substr(t.stime,1,5)||'0'||substr(t.stime,6,length(t.stime))) --where (length(t.stime)-length(replace(t.stime,'-',''))) =1
WHERE length(t.stime)<10 and  (length(t.stime)-length(replace(t.stime,'-','')))>=2 and  (length(substr(t.stime,0,7))-length(replace(substr(t.stime,0,7),'-','')))=2

--更新有-的时间、有两个-的日期、年份为1位数的，改为两位数
select (substr(t.stime,1,length(t.stime)-1)||'0'|| substr(t.stime,length(t.stime),1)), t.stime,t.*,t.rowid From t_test_cc_all_b20181212 t --where substr(t.stime,length(t.stime),length(t.stime)-1)='-'
WHERE length(t.stime)<10 and  (length(t.stime)-length(replace(t.stime,'-','')))>=2 and   (length(substr(t.stime,length(t.stime)-1,2))-length(replace(substr(t.stime,length(t.stime)-1,2),'-','')))=1

update  t_test_cc_all_b20181212 t set t.stime=(substr(t.stime,1,length(t.stime)-1)||'0'|| substr(t.stime,length(t.stime),1)) --where (length(t.stime)-length(replace(t.stime,'-',''))) =1
WHERE length(t.stime)<10 and  (length(t.stime)-length(replace(t.stime,'-','')))>=2 and   (length(substr(t.stime,length(t.stime)-1,2))-length(replace(substr(t.stime,length(t.stime)-1,2),'-','')))=1