oracle中一些常用的函数

oracle中一些常用的函数

--Oracle中,加号只可以负责数字相加 ( + - x /  加减乘除只用来计算  )
SELECT 1+2 FROM dual   --3
SELECT 1+'2' FROM dual  --3
SELECT '1'+'2' FROM dual  --3
SELECT '1'+'2' FROM dual  --3
SELECT '1'+'a' FROM dual  --这是错误的 会提示无效的Number类型




--Oracle的拼接Concat ||
SELECT CONCAT(1,2) FROM dual     --12
SELECT CONCAT(CONCAT(1,2),'a') FROM dual  ---12a
SELECT 1||2||'abnc' FROM dual  --12abnc




--Initcap 将单词的首字母改成大写
SELECT INITCAP('hello') FROM dual     --Hello
SELECT INITCAP('hello worLd') FROM dual    --Hello World
SELECT INITCAP('hello中国worLd') FROM dual   --Hello中国World






--Lower  Upper  全小写或者大写
SELECT Lower('hello') FROM dual  ---hello
SELECT Lower('HELLO') FROM dual  ---hello


SELECT Upper('hello') FROM dual   ---HELLO
SELECT Upper('HELLO') FROM dual   ---HELLO




--Lpad  Rpad   左填充，右填充 
SELECT LPAD('abc',20,'*') FROM dual   --*****************abc 
--详解：LPAD('abc',20,'*')  LPAD(你的字符串,填充后的长度，用什么字符填充)  'abc' 长度为3，要求填充后的长度为20，要用*填充  20-3就是*号的长度
 
SELECT RPAD('abc',20,'*') FROM dual   --abc*****************


SELECT RPAD('abcd',3,'*') FROM dual   --  abc
SELECT LPAD('abcd',3,'*') FROM dual   --  abc


SELECT RPAD('abc',20,'KF') FROM dual   --abcKFKFKFKFKFKFKFKFK


SELECT LPAD('123',9,'0') FROM dual   --000000123




--日常开发,严格的系统设计,主键一般都是定长的
--像我们海尔员工号： A0009782 [大写首字母，加数字，用0填充]
--如何使用标识设计定长的数据库主键
--序列   一组数, 类似抽号机
CREATE SEQUENCE userinfo_userno_seq    --创建SEQUENCE
SELECT userinfo_userno_seq.nextVal FROM dual    --查询下一个值
SELECT userinfo_userno_seq.currVal FROM dual    --查询当前值
SELECT LPAD(userinfo_userno_seq.nextVal,7,'0') FROM dual --0000001
SELECT 'A'||LPAD(userinfo_userno_seq.nextVal,'7','0') FROM dual  --A0000002






--Replace替换函数
SELECT REPLACE('abcdefam','a','qq') FROM dual -- qqbcdefqqm
--REPLACE(你的字符串，字符串要替换的值，替换后的值)
SELECT REPLACE('abcdefam','ab','vk') FROM dual    --vkcdefam
--详解：在字符串'abcdefam' 中 所有出现'ab' 的地方全部换成'vk' 类似于java里面的Replace（）方法






--TRANSLATE是在REPLACE基础上拓展的
SELECT TRANSLATE('abcdefam','ab','vk') FROM dual  --vkcdefvm
--注意这里不是把字符串出现'ab'的地方换成'vk' 而是把出现a的地方换成v，把出现b的地方换成k


SELECT TRANSLATE('abcdefam','abm','vkh') FROM dual  --vkcdefvh


SELECT TRANSLATE('abcdefam','abm','vk') FROM dual  --vkcdefv
--abm三个字母，vk两个字母，出现这样的情况直接把对应不起来的字母从原字符串'abcdefam' 删掉


SELECT TRANSLATE('abcdefam','ab','vkd') FROM dual  --vkcdefvm






--SubStr(s,n)   SubStr(s,n,m) 两种形式，这就是java里多态的表现：重载（方法名相同参数不同）
SELECT SUBSTR('abcde',2) FROM dual --bcde  从第2个字符开始截取
SELECT SUBSTR('abcde',3) FROM dual --cde  从第3个字符开始截取
SELECT SUBSTR('abcde',9) FROM dual --     超出字符串长度不会报错，结果只会是空
--所以说SubStr方法只有两个参数时，第2个参数是要开始截取的位置


SELECT SUBSTR('abcde',2,1) FROM dual    --b
SELECT SUBSTR('abcde',2,3) FROM dual   --bcd
--详解：SubStr(s,n,m) 从第n个开始截取，向后截取m个






--Instr(s,s1)    Instr(s,s1,n1)  在字符串中查找然后返回出现的位置
--0表示没有找到
SELECT INSTR('abcd','a') FROM dual　　--返回1
SELECT INSTR('abcd','b') FROM dual　　--返回2
SELECT INSTR('abcd','e') FROM dual　  --返回0






--Length(str)  顾名思义这个就是查询字符串的长度
SELECT LENGTH('abc你好') FROM dual   --5
SELECT LENGTH('abc你好 ') FROM dual   --6
SELECT LENGTH(' abc你好') FROM dual   --6
SELECT LENGTH(' abc你好 ') FROM dual   --7
--根据结果可以看出：oracle中一个空格也占一个字符，
--所以必要时需要使用trim函数了，OK 马上了解trim






--LTRIM   RTRIM   TRIM  去掉空格
SELECT TRIM( 'abc 你好') FROM dual;       --[abc 你好]
SELECT TRIM( 'abc 你好 ') FROM dual;      --[abc 你好]
SELECT RTRIM( ' abc你好 ') FROM dual;     --[ abc你好]
SELECT LTRIM( ' abc你好 ') FROM dual;     --[abc你好 ]
--总结：TRIM是去掉两边的空格，RTRIM去掉右边的空格，LTRIM去掉左边的空格 
--R  RIGHT；L  LEFT 
--如果要去掉所有空格我一般都有replace方法，呵呵~ 
--你们如果有更好的方法可以@我  互相学习下噢
SELECT REPLACE( 'abc 你好 ',' ','') FROM dual;   --[abc你好]  








--To_Char()   Oracle时间格式是固定的,如果我们需要自定义格式的时间,可以转换为字符串
SELECT sysdate FROM dual
SELECT TO_CHAR(sysdate,'yyyy-mm-dd') FROM dual    --2017-04-07
SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss day') FROM dual   --2017-04-07 00:35:00 星期五
SELECT TO_CHAR(sysdate,'yyyymmddhh24miss') FROM dual   --20170407003510
SELECT TO_CHAR(sysdate,'day') FROM dual  --星期五
--To_Date()   把字符串转换为时间







--Add_Months(Date,N) 当前月份+N个月
SELECT ADD_MONTHS(sysdate,1) FROM dual  --2017/5/7 0:38:18
SELECT ADD_MONTHS(sysdate,9) FROM dual  --2018/1/7 0:38:27


--MONTHS_BETWEEN 月份差
SELECT MONTHS_BETWEEN(ADD_MONTHS(sysdate,9),sysdate) FROM dual   --9 


--凌晨了，已经27周岁了，看看我是哪一年出生的 哈哈~
SELECT ADD_MONTHS(sysdate,12*-27) FROM dual --1990/4/7 0:42:55 




--LAST_DAY 查询本月的最后一天
SELECT LAST_DAY(sysdate) FROM dual     --2017/4/30 0:47:37
SELECT LAST_DAY(sysdate-25) FROM dual   --2017/3/31 0:47:46




--看下下周二，周六是几号
SELECT NEXT_DAY(sysdate,'星期二') FROM dual  --2017/4/11 0:49:33
SELECT NEXT_DAY(sysdate,'星期六') FROM dual  --2017/4/8 0:49:42




--Ceil Floor    
--ceil(n) 取大于等于数值n的最小整数
--floor(n)取小于等于数值n的最大整数
SELECT CEIL(88.1) FROM dual  --89
SELECT FLOOR(88.1) FROM dual --88 
SELECT CEIL(-1.9) FROM dual  -- -1
SELECT FLOOR(-1.9) FROM dual  -- -2





--Mod(s,n)  就是求余数 
SELECT MOD(2,2) FROM dual  --0
SELECT MOD(3,2) FROM dual  --1
SELECT MOD(2,3) FROM dual  --2
SELECT MOD(-2,3) FROM dual -- -2
SELECT MOD(5,-3) FROM dual --2




--Round(n)    Round(n,N)  四舍五入 这里的N表示保留N位小数
SELECT ROUND(123.456) FROM dual   --123
SELECT ROUND(123.556) FROM dual   --124
SELECT ROUND(123.456,2) FROM dual  --123.46
SELECT ROUND(123.456,0) FROM dual  --123
SELECT ROUND(123.456,-1) FROM dual --120
SELECT ROUND(125.456,-1) FROM dual --130
SELECT ROUND(123.456,-2) FROM dual --100


--Trunc(n)    Trunc(n,N)    +-N  跟ROUND用法完全相同,只不过是截断,不是四舍五入
SELECT TRUNC(123.45,1) FROM dual --123.4
SELECT TRUNC(123.678,2) FROM dual  --123.67





--Power(s,n) 这里表示s的n次方
SELECT POWER(2,3) FROM dual   --8
SELECT POWER(2,5) FROM dual   --32
SELECT POWER(2,-2) FROM dual  --0.25