oracle 内建函数（一）

数字函数

--输入和输出都是数字
--abs(x) 取绝对值
select abs(-1) from dual;--1

--ceil(x)  返回大于等于n的最小整数
select ceil(1.4),ceil(-2.5) from dual;--2,-2

--floor(x) 返回小于等于n的最大整数
select floor(1.4),floor(-2.3) from dual;--1,-3

--mod(a,b) 返回a/b的余数，如果b=0，返回a
select mod(3,2),mod(3,0) from dual; --1,3

--round(a,[b]) 四舍五入，b是指定四舍五入的位数,
--省略b，到整数；b是整数，小数点后b位；b是负数，小数点前b位
select round(23.78),round(23.78,1),round(23.78,-1) from dual;--24,23.8,20

--trunc(a,[b]) 截取数字，b同样代表截取位置，定位同上
select trunc(23.78),trunc(23.78,1),trunc(23.78,-1) from dual;--24,23.7,20

--平方根
select sqrt(3),sqrt(9) from dual;--1.732,3

--power(a,b) a的b次方
select power(sqrt(3),2),power(2,3) from dual;--3,8

--sign(number):number>0,return 1;number<0,return -1;number=0,return 0.
select sign(-7),sign(0),sign(7) from dual;

--greatest(a,b,c,d...) 返回最大的
select greatest(3,7,5,12) from dual; --12
--字符串比较 自然排序比较
select greatest('about','age','again') from dual;--age
--如果参数数据类型不通，都转换为第一个参数类型进行比较
select greatest('3',7,5,12) from dual; --7

--least(a,b,c,d...) 返回最小的，规则同greatest， 如果有一个为null，则返回null
select least(2,12,6) from dual; --2
select least('2','12','6'),least(2,34,null) from dual;--'12',null

字符函数

--ascii(char) 返回char的ascii码值
select ascii('a'),ascii('D') from dual;--97,68

--chr(n) 将ascii值转换成字符
select chr(97),chr(65) from dual;--a,A

--concat(a,b) 连接字符串a和b，作用和'||'相同
select concat('aa','bb'),'aa'||'bb' from dual; --aabb,aabb

--initcap(char) 将字符串中每个单词首字母大写 
select initcap('hello oracle') from dual;--Hello Oracle

--length(char)||lengthc(char) 返回字符串的长度
select length('hello我') from dual;--6
--vsize(char) 返回字符串的bytes长度 =lengthb(char)
select vsize('hello') from dual;
--查看字符集
select * from v$nls_parameters;
--
select length('我'),lengthb('我'),vsize('我') from dual;--1,2,2
select length('hello'),vsize('hello') from dual;--5,5


--instr(char,char1) char1在char中的位置，可以指定起始位置，和第几次出现
select instr('hello','l') from dual;--3,默认从第一个字符开始，第一次匹配结束
select instr('hello','l',1,2) from dual;--4，从第一个支付开始，第二次出现位置 

--lower(char) 字符串转换成小写
select lower('TOM') from dual;--tom

--upper(char) 字符转换成大写
select upper('Tom') from dual;--TOM

--lpad(char1,n,char2) 在char1字符串的左端添加char2字符串，直到插入的字符串长度达到n
--char2默认值为空格，如果char1长度大于n，返回char1左端的n个字符
select lpad('a',5,'b') from dual;--bbbba
select lpad('abcdefg',5,'h') from dual;--abcde 5<原字符串的长度，直接截取原来字符串返回

--rpad(char1,n,char2) 右端填充，规则同上面左端填充
select rpad('a',5,'big') from dual; --abigb


--ltrim(char1,char2) 从char1左端开始检测，如果当前位置字符在char2中，那么去掉该字符，直到
--遇到一个字符不在char2为止，返回char1
select ltrim('    hello') from dual;--hello 默认去除空格
select ltrim('hello','echo') from dual;--llo

--rtrim(char1,char2) 右端去掉字符，规则同上面左端去掉字符
select rtrim('oracle','leo') from dual;--orac

--substr(char,from,length) 截取字符串，from截取位置，length截取长度，
select substr('oracle',0,2) from dual;--or
select substr('oracle',2,3) from dual;--rac
select substr('oracle',2) from dual; --racle
select substr('oracle',-2,3) from dual; --le,从右面第2位数，截取3位

--trim(char1 from char2) 从char2的两边去掉char1
select trim('a' from 'abaa') from dual;--b

--translate
/*
    translate(str,str_to_relpace,replace_str)

    str:被操作对象
    str_to_replace:在str中寻找需要被替换的字符
    replace_str:对应str_to_replace串中被替换成的字符
    例：translate(str,'abcd','123')
        将str中所有'a'替换成'1','b'替换成'2','c'替换成'3','d'没有对应的替换字符，直接舍去.
        translate方法替换和被替换字符是一一对应的。
*/
select translate('oracleoracle','ole','123') from dual;--1rac231rac23
select translate('oracleoracle','ole','12') from dual;--1rac21rac2

--replace 
/*
    replace(str,substr1,substr2)
    将str中的所有substr1子串用substr2替换
*/
--自己体会和translate的区别
--单一字符和translate的效果相同
select replace('1111abc','1','A') from dual;--AAAAabc
select replace('1111abc','1') from dual;--abc
--多个字符就不同喽
select replace('1111abc','11','AB') from dual;--ABABabc
select translate('1111abc1','11','AB') from dual;--AAAAabcA

--translate是针对一个字符一个字符的替换，replace是字符子串的替换

日期时间函数

--to_date(str,format)将字符串转换成日期
/*
    format参数：
    YYYY 4位数字年份
    YYY,YY,Y年份的最后3|2|1为数
    MM 月份（01-12）
    DD day of month(1-31)
    HH=HH12 :hour of day(1-12)
    HH24 :hour of day(0-23)
    MI :minute(0-59)
    ss:second(0-59)

*/
select to_date('2011-10-01:22:35:24','YYYY-MM-DD:HH24:MI:SS') from dual;

--to_char(date,format)将日期转换成字符串
select to_char(sysdate,'MM-DD-YY') from dual;--08-22-11

--add_months(date,months)加整个月数
select sysdate,add_months(sysdate,3),add_months(sysdate,-2) from dual;

--last_day(date) 计算date所在时间月份的最后一天
select last_day(to_date('1988/08/08','yyyy/mm/dd')),last_day(sysdate) from dual;--1988-8-31,2011-8-31 22:49:23

--当前时间
select current_date,current_timestamp, sysdate from dual;
/* 2011-8-22 23:00:24 ,22-8月 -11 11.00.23.765000 下午 +08:00,2011-8-22 23:00:23*/

--时区 没大用吧
select dbtimezone from dual;

--从日期中提取信息
select extract(year from sysdate),extract(month from sysdate),extract(day from sysdate) from dual;

--返回相差的月数
select months_between(sysdate,to_date('2011-02-15','YYYY-MM-DD')) from dual;

其他

--nvl(exp1,exp2) 如果exp1=null，返回exp2，否则返回exp1
select nvl(null,1),nvl(2,1) from dual;--1,2

--nvl2(exp1,exp2,exp3) 如果exp1=null，返回exp3；exp1 !=null,返回 exp3
select nvl2(null,1,2),nvl2(3,1,2) from dual;--2,1

--coalesce(exp1,exp2,exp3...)返回第一个不是null的exp的值
select coalesce(null,null,null,null,2,null,5) from dual;--2

--cast类型转换函数
select cast(2345 as varchar2(10)), cast(sysdate as varchar2(30)) from dual;

--decode(exp,search1,result1,search2,result2...,default);
/*
    exp:被操纵对象
    如果exp=searchN字符串，就返回resultN,和case语句执行差不多
    default如果找不到就默认值
*/
select decode('100','99','九九','100','一百','101','一百零一',0) from dual;--一百