ORACLE之常用函数

plantegg

　　oracle函数的分类
　　1.REPLACE(‘string’,’s1′,’s2′)
　　string   希望被替换的字符或变量
　　s1       被替换的字符串
　　s2       要替换的字符串
　　SQL> select replace(‘he love you’,’he’,’i’) from dual;
　　REPLACE(‘H
　　———-
　　i love you
　　案例：把emp表里ename名字中KING替换为King
　　sql>select  replace(ename,’KING’,’King’)  from  emp;
　　结果是，看到KING替换为King
　　sql>select  ename  from  emp;
　　结果是，看到KING还是KING
　　总结replace替换只在查询里展现替换的结果。
　　2.INSTR(C1,C2,I,J)
　　在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
　　C1    被搜索的字符串
　　C2    希望搜索的字符串
　　I     搜索的开始位置,默认为1
　　J     出现的位置,默认为1
　　SQL> select instr(‘oracle traning’,’ra’,1,2) instring from dual;
　　INSTRING
　　———
　　9
　　3.ASCII
　　返回与指定的字符对应的十进制数;
　　SQL> select ascii(‘A’) A,ascii(‘a’) a,ascii(‘0’) zero,ascii(‘ ‘) space from dual;
　　A         A      ZERO     SPACE
　　——— ——— ——— ———
　　65        97        48        32
　　4.CHR
　　给出整数,返回对应的字符;
　　SQL> select chr(54740) zhao,chr(65) chr65 from dual;
　　ZH C
　　— –
　　赵 A
　　5.CONCAT
　　连接两个字符串;
　　SQL> select concat(‘010-‘,’88888888′)||’转23’  电话 from dual;
　　电话
　　—————-
　　010-88888888转23
　　6.INITCAP
　　返回字符串并将字符串的第一个字母变为大写;
　　SQL> select initcap(‘smith’) upp from dual;
　　UPP
　　—–
　　Smith
　　7.SUBSTR(string,start,count)
　　取子字符串,从start开始,取count个
　　SQL> select substr(‘13088888888’,3,8) from dual;
　　SUBSTR(‘
　　——–
　　08888888
　　8.LOWER
　　返回字符串,并将所有的字符小写
　　SQL> select lower(‘AaBbCcDd’)AaBbCcDd from dual;
　　AABBCCDD
　　——–
　　aabbccdd
　　9.UPPER
　　返回字符串,并将所有的字符大写
　　SQL> select upper(‘AaBbCcDd’) upper from dual;
　　UPPER
　　——–
　　AABBCCDD
　　10.RPAD和LPAD(粘贴字符)
　　RPAD  在列的右边粘贴字符
　　LPAD  在列的左边粘贴字符
　　SQL> select lpad(rpad(‘gao’,10,’*’),17,’*’)from dual;
　　LPAD(RPAD(‘GAO’,1
　　—————–
　　*******gao*******
　　不够字符则用*来填满
　　案例：
　　一共15位长，左边不够用*补满。
　　select lpad(‘yuemu.blog’,15,’*’) from dual;
　　一共17位长，右边不够用*补满
　　select rpad(lpad(‘yuemu.blog‘,15,’*’),17,’*’ )from dual;
　　11.LTRIM和RTRIM
　　LTRIM  删除左边出现的字符串
　　RTRIM  删除右边出现的字符串
　　SQL> select ltrim(rtrim(‘   gao qian jing   ‘,’ ‘),’ ‘) from dual;
　　LTRIM(RTRIM(‘
　　————-
　　gao qian jing
　　案例：去掉左右边的*。
　　select rtrim (ltrim (‘***yuemu.blog***’,’*’),’*’)from dual
　　12.TRIM(leading[trailing]  ‘s’ from ‘string’)
　　LEADING   剪掉前面的字符
　　TRAILING  剪掉后面的字符
　　如果不指定,默认为空格符
　　案例：删掉y
　　select trim (‘y’ from ‘yuemu.blog‘) from dual
　　13.CEIL(向上取整)
　　返回大于或等于给出数字的最小整数
　　SQL> select ceil(3.1415927) from dual;
　　CEIL(3.1415927)
　　—————
　　4
　　14.EXP
　　返回一个数字e的n次方根
　　SQL> select exp(2),exp(1) from dual;
　　EXP(2)    EXP(1)
　　——— ———
　　7.3890561 2.7182818
　　15.FLOOR
　　对给定的数字取整数
　　SQL> select floor(2345.67) from dual;
　　FLOOR(2345.67)
　　————–
　　2345
　　16.MOD(n1,n2)
　　返回一个n1除以n2的余数
　　SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
　　MOD(10,3)  MOD(3,3)  MOD(2,3)
　　——— ——— ———
　　1         0         2
　　17.POWER
　　返回n1的n2次方根
　　SQL> select power(2,10),power(3,3) from dual;
　　POWER(2,10) POWER(3,3)
　　———– ———-
　　1024         27
　　18.ROUND和TRUNC
　　按照指定的精度进行舍入
　　round(四舍五入)，trunc(截取，可以指定截取到哪一位，默认截取到整数)
　　SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
　　ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
　　———– ———— ———– ————
　　56          -55          55          -55
　　select trunc(127.177,2) from dual  ——-显示127.17
　　select trunc(127.177,-2) from dual  ———显示100
　　19.SIGN
　　取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
　　SQL> select sign(123),sign(-100),sign(0) from dual;
　　SIGN(123) SIGN(-100)   SIGN(0)
　　——— ———- ———
　　1         -1         0
　　20.ADD_MONTHS
　　增加或减去月份
　　SQL> select to_char(add_months(to_date(‘199912′,’yyyymm’),2),’yyyymm’) from dual;
　　TO_CHA
　　——
　　200002
　　SQL> select to_char(add_months(to_date(‘199912′,’yyyymm’),-2),’yyyymm’) from dual;
　　TO_CHA
　　——
　　199910
　　案例：如果想查看最近几个月入职的员工
　　查询34年内入职的
　　select * from emp  where add_months(hiredate,408)>=sysdate
　　21.LAST_DAY
　　返回日期的最后一天
　　SQL> select to_char(last_day(sysdate),’yyyy-mm-dd’)from dual;
　　LAST_DAY(S
　　———-
　　2015-10-21
　　22.MONTHS_BETWEEN(date2,date1)
　　给出date2-date1的月份
　　SQL> select months_between(’19-12月-1999′,’19-3月-1999′) mon_between from dual;
　　MON_BETWEEN
　　———–
　　9
　　SQL>select months_between(to_date(‘2000.05.20′,’yyyy.mm.dd’),to_date(‘2005.05.20′,’yyyy.mm.dd’)) mon_betw from dual;
　　MON_BETW
　　———
　　-60
　　23.NEW_TIME(date,’this’,’that’)
　　给出在this时区=other时区的日期和时间
　　SQL> select to_char(sysdate,’yyyy.mm.dd hh24:mi:ss’) bj_time,to_char(new_time
　　2  (sysdate,’PDT’,’GMT’),’yyyy.mm.dd hh24:mi:ss’) los_angles from dual;
　　BJ_TIME             LOS_ANGLES
　　——————- ——————-
　　2004.05.09 11:05:32 2004.05.09 18:05:32
　　24.NEXT_DAY(date,’day’)
　　给出日期date和星期x之后计算下一个星期的日期
　　SQL> select next_day(’18-5月-2001′,’星期五’) next_day from dual;
　　NEXT_DAY
　　———-
　　25-5月 -01
　　25.CONVERT(c,dset,sset)
　　将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
　　SQL> select convert(‘strutz’,’we8hp’,’f7dec’) “conversion” from dual;
　　conver
　　——
　　strutz
　　26.TO_CHAR(atde,’format’)
　　将某种格式转化为其他格式，有选择的显示，根据你的预期显示内容
　　SQL> select to_char(sysdate,’yyyy/mm/dd hh24:mi:ss’) from dual;
　　TO_CHAR(SYSDATE,’YY
　　——————-
　　2004/05/09 21:14:41
　　案例1：
　　select ename,to_char(sal,’L99G999D99′) from emp案例2：:
　　select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) from emp
　　案例3：查询1982年以后入职的员工
　　select ename from emp where to_char(hiredate,’yyyy’)>=1982
　　27.TO_DATE(string,’format’)
　　将字符串转化为ORACLE中的一个日期
　　28.TO_NUMBER
　　将给出的字符转换为数字
　　SQL> select to_number(‘1999’) year from dual;
　　YEAR
　　———
　　1999
　　29.decode
　　根据表中某一列编号显示不同信息
　　select ename,decode(deptno,10,’10号部门’
　　20，’20号部门’) from emp
　　30.GREATEST
　　返回一组表达式中的最大值,即比较字符的编码大小.
　　SQL> select greatest(‘AA’,’AB’,’AC’) from dual;
　　GR
　　—
　　AC
　　SQL> select greatest(‘啊’,’安’,’天’) from dual;
　　GR
　　—
　　天
　　31.LEAST
　　返回一组表达式中的最小值
　　SQL> select least(‘啊’,’安’,’天’) from dual;
　　LE
　　—
　　啊
　　32.UID
　　返回标识当前用户的唯一整数
　　SQL> show user
　　USER 为”GAO”
　　SQL> select username,user_id from dba_users where user_id=uid;
　　USERNAME                         USER_ID
　　—————————— ———
　　GAO                                   25
　　33.AVG(DISTINCT|ALL)
　　all表示对所有的值求平均值,distinct只对不同的值求平均值
　　SQLWKS> create table table3(xm varchar(8),sal number(7,2));
　　语句已处理。
　　SQLWKS>  insert into table3 values(‘gao’,1111.11);
　　SQLWKS>  insert into table3 values(‘gao’,1111.11);
　　SQLWKS>  insert into table3 values(‘zhu’,5555.55);
　　SQLWKS> commit;
　　SQL> select avg(distinct sal) from gao.table3;
　　AVG(DISTINCTSAL)
　　—————-
　　3333.33
　　SQL> select avg(all sal) from gao.table3;
　　AVG(ALLSAL)
　　———–
　　2592.59
　　34.MAX(DISTINCT|ALL)
　　求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
　　SQL> select max(distinct sal) from scott.emp;
　　MAX(DISTINCTSAL)
　　—————-
　　5000
　　35.MIN(DISTINCT|ALL)
　　求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
　　SQL> select min(all sal) from gao.table3;
　　MIN(ALLSAL)
　　———–
　　1111.11
　　这里我只给大家介绍一些常用函数，还有很多函数大家可以自行查找.