oracle之常用函数及sql语句
2017-11-20 本文已影响10人
iszengmh
参考链接
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
