oracle常用函数整理

SQL Select语句完整的执行顺序： 
1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；(where条件从右往左解析)
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、select 的字段；
8、使用order by对结果集进行排序。


1.ASCII:返回指定的字符对应的十进制数字
SELECT ASCII('A') A,ASCII('a') a, ASCII('0') AS ZERO ,ASCII(' ')  FROM DUAL;
                  65           97                 48         32
2.chr:返回对应的ASCII码
select chr(54725) zhang,chr(51453) san, chr(65) chr65 from dual;  
           张                     三        A            
3.CONCAT:字符串的拼接
select concat(123,concat('a','b')) from dual;--123ab


4.INSTR:返回指定的字符串从哪里开始，第几次出现的下标
instr('str',target,start,times):要搜索的字符串，被搜索的字符串，从哪里开始，第几次出现
select instr('think in java','in',1,2) from dual;--7
        
5.LENGTH:返回制定字符串的长度
select length('think in java') from dual;--13


6.INITCAP第一个字母大写
select initcap('empty') from dual;--Empty


7.LOWER:字母全部变成小写
select lower('THINK IN JAVA') from dual;--think in java


8.UPPER:全部小写转大写
select upper('think in java') from dual;--THINK IN JAVA


9.RPAD:函数从右边对字符串使用指定的字符进行填充
select rpad('wang',10,'$') from dual;--wang$$$$$$
select rpad('wang',2,'$') from dual;--wa
select rpad('wang',0,'$') from dual;--空
  LPAD:函数从左边对字符串使用指定的字符进行填充


10:LTRIM:去除左边重复的字符串
select length(rtrim(ltrim('think in java','t'),'java')) from dual;--8
   RTRIM:去除右边重复的字符串
    TRIM:去除字符串两边的空字符串，不能去除字符串中间的空字符串(和java中的trim一样)
：只能去除单个字符
select trim('think in java    ') from dual;--think in java
SELECT trim('1' from '123sfd111') FROM dual; --23sfd默认去除两边的
SELECT trim(leading '1' from '123sfd111') FROM dual; --23sfd111去除前边的
SELECT trim(both '1' from '123sfd111') FROM dual; --23sfd--去除两边的
SELECT trim(trailing '1' from '123sfd111') FROM dual;--123sfd去除尾部的
11：SUBSTR:截取制定的字符串，从哪里开始，截取几个
select length(substr('think in java',3,4)) from dual;--ink --4


12:REPLACE('string','s1','s2');
string:指定的字符串
s1：   需要替换的字符串
s2：   被替换为的字符串
select REPLACE('think in java','in','PHP') from dual;--thPHPk PHP java


13:ABS:返回的绝对值
select abs(9) ,abs(-9)from dual;--9--9


14:CELL:返回大于等于指定数字的最小值
select ceil(3.1415926) from dual;-- 4
select ceil(-3.1415926) from dual;-- -3


15:FLOOR:给定数字取整数(不四舍五入)
select floor(3.7414) from dual;---3


16:ROUND:四舍五入
select round(3.7414) from dual;--4
select round(-3.5) from dual;-- -4
   TRUNC:截取整数部分
select trunc(3.7414) from dual;--3
select trunc(-3.7414) from dual;-- -3
select trunc(123.123,-2) trunc1,trunc(123.123,2) from dual;--100  123.12


17:SIGN指定的数字，大于0返回1，小于0返回-1，等于0返回0
select SIGN(1),SIGN(-1),SIGN(0)from dual;-- 1,-1,0


18:ADD_MONTHS:增加月份，为负数的时候就是减去月份
select add_months(sysdate,-2) from dual;--2017/6/14 20:00:51
select add_months(to_date('2008-08-08','yyyy-mm-dd'),-2) from dual;--2008-06-08


19.last_day:返回指定日期 -月份--的最后一天
select last_day(to_date('2015-02','yyyy-mm')) from dual;


20.months_between(date2,date1):返回指定日期之间相差的月份数(date2-date1)(有小数)
select months_between(to_date('2007.10','yyyy.mm')
    ,to_date('2007.08','yyyy.mm')) mon_betw from dual;--2


21.new_time:给出当前时区的日期，和other时区的日期
select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,
       to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
--bj_time 2017.08.14 20:20:17
--los_angles  2017.08.15 03:20:17


22.NEXT_DAY(),从给定日期开始，计算下一个周几是什么时候：
select next_day(sysdate,'星期日') next_day,
       next_day(sysdate,'星期六'),
       next_day(sysdate,'星期一'),
       next_day(sysdate,'星期五') from dual;


23.SYSDATE:当前系统时间
---mysql  :now()


24.TO_CHAR(date,'format')
yy,yyy,yyyy年
select to_char(sysdate,'mm') from dual;--08
select to_char(sysdate,'month') from dual;--8月
day
d周内天
dd月内天
ddd年内天
day周几
其他
q季度
w月内周
ww年内周
判断是不是工作日
select case when 
to_char(to_date('20170813','yyyymmdd'),'day') in ('星期六','星期日' )
then '休息日' else '工作日' end 
from dual;
将数字按照固定格式输出
--0表示强迫0显示
select to_char('1','0000000009') from dual;-- 0000000001


select to_char(1234.123,'9999.99')
      ,to_char(1234.123,'9,999.99')
      ,to_char(1234.123,'$9,999.99') 
--L强制显示当地的货币符号
      ,to_char(1234.123,'L9,999.99') from dual;
---1234.12  1,234.12  $1,234.12


25.TO_DATE(string,'format'):将字符串转换成日期格式
select to_char(to_date('2017-01-01','yyyy-mm-dd'),'day') from dual;--星期日


select count(*)    
--select *  
from ( 
--  0-27
     select rownum-1 rnum     
         from all_objects  --50543   
         where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-02-01','yyyy-mm-dd')+1--小于28    
      )     
--不是周六或者周天
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) not in ( '1', '7' );    
26:TO_NUMBER()
将给出的字符转换成数字
select to_NUMBER('￥123,123.12','L999,999.99')from dual;
select to_NUMBER('$123,123.12','$999,999.99')from dual;
9显示整数，0强制显示0，$显示美元符号，L显示当地货币符号，.小数点，,千分位分隔符
27.USER:
返回当前用户的名字
select user from  dual;


28.rownum:
对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，
第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的
名称作为前缀。
select count(*)    
--select *  
from ( 
--  0-27
     select rownum-1 rnum  
 --存储的是所有的对象   
         from all_objects  --50543   
         where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-02-01','yyyy-mm-dd')+1--小于28    
      )     
--不是周六或者周天
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' ) not in ( '1', '7' );    


29.空值函数:
1.NVL(expr1,expr2)
当表达式1为null的时候，则显示表达式2的值，否则是表达式1自己
2.NVL2(expr1,expr2,expr3)
当表达式1为null的时候，显示表达式3的值，否则显示表达式2的值
3.NULLIF(expr1,expr2)
当表达式1等于表达式2的时候，返回null，不相等则返回第一个表达式
4.COALESCE(expr1,expr2，expr3,...)
当全部为null的时候，则返回null，若有一项不为null，则返回该项，若都不为null，则返回第一个表达式
5.CASE WHEN 条件 THEN 值 else 值 end
相当于java中的if..else 的用法
30.聚合函数:
sum(),avg(),max(),min(),count()统计数据表选中行x列的合计值。