oracle常用函数总结

1. 数值型函数

select abs(-100),mod(10,3),ceil(-1.23),round(5.9),floor(5.9),power(2,4) from dual;

执行结果

dual 是 oracle的零时表，因为oracle不像mysql等支持select xx 的写法，所以必须要from dual满足语法条件。

abs() : 绝对值
mod（a，b）: a对b取余结果
ceil（） : 向上取整
round（a,b）: 对a进行四舍五入，b为保留小数点位数
floor() : 向下取整
power(a,b) : a的b次方

2. 随机数，随机字符串，截取

select substr('abcde',2,2),trunc(3.1415,3),dbms_random.value(1,100),dbms_random.string('P',5) from dual;

执行结果

substr(str,a,b) : 对字符串str（当然也可以是数字）截取，从第a个字符开始，截取b个，也就是左开右闭
trunc(num,a) : 对数字num保留a位小数
dbms_random.value（1,100）: 带参数即1-100 左闭右开
dbms_random.value 这种用法没有参数，会返回一个具有38位精度的数值，范围从0.0到1.0，但不包括1.0
dbms_random.string(‘P’,5)：
参数1：:生成一个指定模式指定位数的随机字符串，可选模式如下：

• ‘u’或’U’，仅返回大写字母
• ‘l’或’L’，仅返回小写字母
• ‘a’或’A’，返回大小写字母混合
• ‘x’或’X’，返回大写字母和数字混合
• ‘p’或’P’，返回任意可显示字符

参数2：长度

3. 字符串连接，首字母大写，大小写

select concat('abc','cde'),initcap('hello world'),upper('abcDef'),lower('abCdeF') from dual;

concat(a,b) : 连接字符串a和b
initcap() : 每个单词首字母大写，分隔符随意
upper() : 全部转为大写字母
lower() : 全部转为小写字母

select instr('abcd ab ab','ab',2,2),length('abcdef'),lpad('10',2,'0'),rpad('9',3,'0') from dual;

instr(str1,str2,a,b) : 在str1中找str2，从第a个开始找第b次出现的位置（下标从1开始）
length() : 返回长度
lpad(str1,a,str2) : 在str1左边添加str2，长度达到a为止
rpad(str1,a,str2) : 在str1右边添加str2，长度达到a为止
（若出现lpad(‘ab’,3,‘cd’)，此类情况，只会添加一个c，而不是cd，以为此函数底层是按字符添加，添加完一次判断一次是否满足长度要求）

select rtrim(ltrim('***abc***','*'),'*'),trim('   abc   '),REPLACE('hello,world','hello','hehe'),translate('helloh,world','ho','a'),substr('abc',2,1),REVERSE('hello')from dual 

ltrim/rtrim(str,a) : 去除str中首/尾的a字符
trim(str) : 去除str首尾的空格
replace(str,a,b) : 将str中的所有a字符串整体替换成b字符串
translate(str,a,b) : 此方法也是替换，有所不同的是单个字符替换，若字符串a为abc，字符串b为cd，那么会将str中所有a替换成c，b替换成d，c删除,而如果字符串b长度大于字符串a，那么b多余部分不参与替换
reverse(str) : 顾名思义，讲字符串str倒序重组成新字符串

6. 时间日期相关函数(常用)

select sysdate,add_months(sysdate,36),sysdate+1,
last_day(to_date('2000-2-3','yyyy-MM-dd')),
months_between(sysdate,to_date('2000-2-3','yyyy-MM-dd')),
sysdate-to_date('2000-2-3','yyyy-MM-dd'),
ROUND(sysdate,'month'),trunc(sysdate,'month') from dual

sysdate : 返回当前时间，精确到秒，注意没有函数括号
add_months(sysdate,36) : 返回当前时间加36个月
sysdate+1 : 当前时间的后一天
last_day(sysdate) : 指定时间所在月的最后一天
to_date(‘2000-2-3’,‘yyyy-MM-dd’) : 将字符串转化为指定格式日期
months_between(a,b) : 返回两个日期之间相隔月份数
sysdate-to_date(‘2000-2-3’,‘yyyy-MM-dd’) : 计算当前时间和指定时间之间的天数
ROUND(sysdate,‘month’) : 计算距离指定日期最近的月份数 如8.15 则返回8.1，8.16则返回9.1
trunc(sysdate,‘month’) : month（返回本月第1日）可替换为year（今年的1月1日）和day（本周第一天（周日））

select next_day(sysdate,2),extract(day from sysdate),current_date,CAST('123' AS INT),
to_date('1999-5-6 16:42:33','yyyy-MM-dd hh24:mi:ss'),to_char(sysdate,'q')
 from dual

next_day(sysdate,2) : 当前日期的后面一个周一
extract(day from sysdate) : 本月天数转成字符串
current_date : 和sysdate一样显示当前日期
CAST(‘123’ AS INT) : 将特殊字符串转成int
to_date(‘1999-5-6 16:42:33’,‘yyyy-MM-dd hh24:mi:ss’) ： 字符串转化成日期格式，使用24小时制
to_char(sysdate,‘q’) : 查看当前日期的季度

select nvl(456,123),nvl2(null,456,789),
CASE 6 
	WHEN 1 THEN 'abc' 
	WHEN 2 THEN 'cde'
	WHEN 3 THEN 'def'
	ELSE 'ggg'
END,
case 
  when 1<1 then 'abc'
  when 2<3 then 'cde'
  when 3<4 then 'fff'
  else 'kkk'
end,
decode(6,1,'abc',2,'cde',3,'def','ggg')
 from dual

nvl(a,b) : 如果a为null，则结果b，否则为a
nvl2(a,b,c) : 如果a为null,则结果为c,否则为b
decode(a,b,str1,c,str2,d,str3,str4) : 如果a等于b则结果为str1；如果a等于c则结果为str2；如果a等于d则结果为str3,一个都不满足 则为str4

上班闲暇时间无聊随便写写，先去忙了，下次再写