Oracle常用函数

Create Table Test6(
       id varchar2(30),
       name varchar2(30),
       age number(2),
       sex varchar2(2),
       ename varchar2(30)
)

一、函数的分类

    SQL函数一般分为两种

    单行函数 基于单行的处理，一行产生一个结果

    多行函数 基于多行的处理，对多行进行汇总，多行产生结果

以上是Oracle的一些常用的系统函数

二、常用的单行函数

1.1、字符函数

1、lower(字段名)   转小写

select id,lower(ENAME) from Test6

2、upper(字段名)  转大写

select id,upper(ENAME) from Test6

3、initcap(字段名) 首字母转大写

select id,initcap(ENAME) from Test6

4、concat(x,y)  功能与|| 符类似

select id, concat(ENAME,'aaa') as ENAME from Test6

 5、substr(字段名,strat,end)  取字段名的子串

select id, substr(ENAME,1,2) as ENAME from Test6

6、length(字段名) 取字段长度

select id, length(ENAME) as ENAME from Test6

7、lpad  (字符定长,不够长度时用指定的字符从左边填充)

select id, lpad(ENAME,10,'&') as ENAME from Test6

保证ENAME字段为10个字符长度,如果不够长,则从左边开始填充'&'符,如果字段超出长度,则截取。

8、rpad(字段订长,不够长度是用指定的字符从右边开始填充)和lpad相反

select id, rpad(ENAME,10,'&') as ENAME from Test6

9、trim(leading 字符 FROM  字段)   从字段头部删除指定字符

select id, trim(leading 'z' from ENAME) as ENAME from Test6

执行前：上

执行后:

字母z消失

 10、trim(trailing 字符 FROM  字段)   从字段尾部删除指定字符

select id, trim(trailing  'u' from ENAME) as ENAME from Test6

尾部的u字符消失了

11、ltrim(字段名,字符)  (等价于用  trim(leading 字符 FROM  字段)   从字段头部删除指定字符)   从字段的左侧开始删除字符

执行下面的sql前:

select id,ltrim(ENAME,'z') as ENAME from Test6

执行后,头部的字符'z'消失了

 12、rtrim(字段名,字符)  (等价于用  trim(trailing 字符 FROM  字段)   从字段尾部删除指定字符)   从字段的右侧开始删除字符,与ltrim相反

13、instr   instr(字段名,要查找的子串,开始查找的位置,查找的子串第几次出现)  从字段中查找我们要查找的子串(按照一定的规则)

select id, instr(ENAME,'w',1,2) as POSITION from Test6

查找ENAME字段中'w'字符的位置,并且从ENAME字段的第一个位置开始查找,并取第二个出现w的位置返回

14、replace(字段名,待查找的子串,用于替换查找到的子串)   从字段名中检索需要替换的子串,并使用用于替换查找到的子串进行替换

select id, replace(ENAME,'s','66') as ENAME  from Test6

将s替换成了66

注意:"&"很奇怪的一个问题！！！！！！

2、数值函数

1、round(字段名,要保留的小数位数)  四舍五入,并保留指定位数的小数

select id,name,
nvl(age,30)/7 as result  from Test6

对上面的结果进行四舍五入操作,结果保留0位小数

select id,name,
round(nvl(age,30)/7,0) as result   from Test6

2、trunc

(1)、截断数字

 trunc(n1,n2)   n1表示要截断的数字,n2表示要截断到哪一位,n2的位置根据小数点的位置来确定  注意:trunc的截取不进行四舍五入运算

i、不传n2,则n2默认为0

select trunc(66.6) from test6

ii、传入n2=1,表示截取到小数点的第一位.

select trunc(66.6,1) from test6

iii、传入n2为负数

select trunc(66.6,-1) from test6

表示从小数点左侧开始截取,把个位数全部去掉,保留十位数。

(2)trunc  用于截断日期

i、截取今天,去掉时分秒

select sysdate,trunc(sysdate,'dd') from dual

ii、截取本周第一天

select sysdate,trunc(sysdate,'d') from dual

iii、截取本月第一天

select sysdate,trunc(sysdate,'mm') from dual

vi、截取本年第一天

select sysdate,trunc(sysdate,'y') from dual

vii、截取到小时

select sysdate,trunc(sysdate,'hh') from dual

viii、截取到分钟

select sysdate,trunc(sysdate,'mi') from dual

vvi、获取上月第一天

select TRUNC(add_months(SYSDATE,1),'MM') from dual

 注:通过trunc函数和下面将会讲解的日期函数我们可以获取任意我们想要的时间(事件可以是任意格式,只要你这些函数用的六),所以非常方便

(3)mod(被除数,除数)  取余

select mod(12,5) from dual

(4)ceil(x)  向上取整

select ceil(12/5) from dual

(5)floor(x)  向下取整

select floor(12/5) from dual

三、日期函数

下面的sysdate都是

1、sysdate  返回系统当前日期  

　　实际上Oracle内部存储日期的格式是：世纪，年，月，日，小，分钟，秒。

　　不管如何输入都这样

　　9i开始，默认的日期格式是：DD-MON-RR,之前是DD-MON-YY

　　RR 和YY 都是世纪后的两位，但有区别

　　ORACLE的有效日期范围是：公元前年月日－年月日

select sysdate from dual

    RR日期格式：

      1、如果当前年份最后两位是：－，并且指定年份的最后两位也为－，

        则返回本世纪

       例：当前年：， 01－－，表示2008 年

      2、如果当前年份最后两位是：－，指定年份最后两位为50－

        则返回上世纪。

       例：当前年：，01－－，表示1998

      3、如果当前年最后两位为：－，指定年份最后两位为0－，

        则返回下世纪。

       例：当前年：，－－表示的是年

      4、如果当前年最后两位是：－，指定年份最后两位为：－

        则返回本世纪。

       例：当前年：，－－表示的是年

2、months_between(x,y)  两个日期之间相差的月数

计算Test6表中的用户到目前位置,成为会员已经几个月了(注册即成为会员)

select months_between(sysdate,addtime)as diff_month from test6

3、add_months(日期,要加的月份)   返回在指定的日期后，加上n个月后的日期

select add_months(sysdate,3) from dual

4、last_day(日期)  返回指定日期所在月的最后一天

select last_day(sysdate) from dual

5、next_day(日期,下一天)  返回指定日期下一天，这个下一天可不是紧接着下一天的意思,而是下一个你指定的日子(比如星期一、星期二等等)。

星期日:0

星期一:1,

星期二:2,

.....

注意:这个函数有个需要注意的地方

NEXT_DAY()是和当前环境的LANGUAGE变量有关的。

So： 下一天最好用数字表示,否则你就会碰到莫名奇怪的问题。

6、 extract  日期提取函数

i、从日期中提取年份

SELECT EXTRACT(YEAR  FROM sysdate) FROM dual;

ii、从日期中提取月份

SELECT EXTRACT(Month  FROM sysdate) FROM dual;

iii、从日期中提取日

SELECT EXTRACT(DAY  FROM sysdate) FROM dual;

 vi、从日期中提取时

select extract(Hour  FROM addtime) from test6

 vii、从日期中提供分

select extract(MINUTE  FROM addtime) from test6

viii、从日期中提取秒

select extract(second  FROM addtime) from test6

四、转换函数

Year:       yy　　　　two digits 两位年                   显示值:07  yyy　　    three digits 三位年                 显示值:007  yyyy　　  four digits 四位年                   显示值:2007 

Month:       mm          number     两位月                 显示值:11  mon         abbreviated 字符集表示          显示值:11月,若是英文版,显示nov      month      spelled out 字符集表示           显示值:11月,若是英文版,显示november 

Day:       dd         number          当月第几天        显示值:02  ddd       number          当年第几天        显示值:02  dy         abbreviated    当周第几天简写    显示值:星期五,若是英文版,显示fri  day       spelled out     当周第几天全写    显示值:星期五,若是英文版,显示friday    

Hour:  hh         two digits 12小时进制            显示值:01  hh24     two digits 24小时进制            显示值:13 

Minute:  mi    two digits 60进制                显示值:45 

其它  Q        digit          季度                  显示值:4  WW    digit          当年第几周            显示值:44  W       digit          当月第几周            显示值:1 

Second:  ss    two digits 60进制                显示值:25 

1、TO_CHAR   将日期转换成字符串

i、将日期转换成字符串

select TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

ii、获取当前时间的年

select TO_CHAR(sysdate,'yyyy') from dual

iii、获取当前时间的月

select TO_CHAR(sysdate,'mm') from dual

vi、获取当前时间的日(当月的第几日)

select TO_CHAR(sysdate,'dd') from dual

vii、获取当前时间的日(当年的第几日)

select TO_CHAR(sysdate,'ddd') from dual

viii、获取当前时间的日(当周的第几日)

select TO_CHAR(sysdate,'dy') from dual

vvi、获取当前时间的时(24小时制)

select TO_CHAR(sysdate,'hh24') from dual

vvii、获取当前时间的分

select TO_CHAR(sysdate,'mi') from dual

vviii、获取当前时间的秒

select TO_CHAR(sysdate,'ss') from dual

2、TO_DATA 将字符串转换成为日期

select to_date('2017-03-14 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual

 3、TO_CHAR

TO_CHAR详解