oracle基础|oracle函数的使用|oracle单行函数(字符函数、日期函数、数字函数、转换函数)的使用

浅浅的花香味﹌ 2022-09-14 03:09 229阅读 0赞

**目录**

一、 前言

二、什么是函数

1、哑表dual

2、字符函数

3、数字函数

4、日期函数

5、类型转换函数

6、千年虫:

--------------------

# 一、 前言 #

前面我们学了一些简单的用法，今天要学习oracl函数的使用，函数是oracle非常重要的功能，如果将函数学会了，那么基本上在项目的一些增删改查功能，就可以很轻松的上手了，所以这篇文章一定要认真的看

# 二、什么是函数 #

这里的函数相当于java中写好的一些方法，有名字，可以传递参数，实现某一项具体功能。  
函数分为:  
      1.单行函数  
          1.字符函数  
          2.日期函数  
          3.数字函数  
          4.转换函数  
      2.分组函数  
        
学习之前，我们先了解一下什么是哑表，后面我们会用到

# 1、哑表dual #

dual是一个虚拟表，辅助查找和运算。通常用在select语句中，作为查询的目标表结构，oracle保证dual里面永远只有一条记录。  
      例如:  
      显示1+1的结果,可以看出,dual很多时候是为了构成select的标准语法  
      select 1+1 from dual;

#        2、字符函数 #

1) LOWER:转换成小写  
        2) UPPER:转换成大写  
        3) INITCAP:首字母变成大写，其余都小写  
        4) CONCAT:字符串的连接  
        5) SUBSTR(str,start,length):字符串的截取  
        6) LENGTH:求字符串的长度  
        7) NVL : 转换null的值。(前边已经用过)  
           nvl2:  
      LOWER 把字符转为小写  
          例如:把'HELLO'转换为小写  
          select lower('HELLO')  
          from dual;  
            
          例如:把s\_emp表中的last\_name列的值转换为小写  
          select lower(last\_name)  
          from s\_emp;  
          
      upper 把字符转换为大写  
          例如:把'world'转换为大写  
          select upper('world')  
          from dual;

例如:把s\_emp表中的last\_name列的值转换为大写  
          select upper(last\_name)  
          from s\_emp;  
            
            
          例如:查询s\_emp表中名字为Ngao的人信息  
              这样是查不到:  
              select last\_name,salary,dept\_id  
              from s\_emp  
              where last\_name='NGAO';

这样就可以查询到了:  
              select last\_name,salary,dept\_id  
              from s\_emp  
              where upper(last\_name)='NGAO';

initcap 把字符串首字母大写  
          例如:把'hELLO'转换为首字母大写,其余字母小写  
          select initcap('hELLO')  
          from dual;

concat 把俩个字符串连接在一起(类似之前的||的作用)  
          例如:把'hello'和'world'俩个字符串连接到一起,并且起个别名为msg  
          select concat('hello','world') msg  
          from dual;  
            
          例如:把first\_name和last\_name俩个列的值连接到一起  
          select concat(first\_name,last\_name)  
          from s\_emp;

substr 截取字符串  
          例如:截取'hello'字符串,从第2个字符开始,截取后面的3个字符  
          select substr('hello',2,3)  
          from dual;

length 获得字符串长度  
          例如:获得'world'字符串的长度  
          select length('world')  
          from dual;

例如:获得s\_emp表中last\_name列的每个值的字符长度  
          select length(last\_name)  
          from s\_emp;

nvl 替换列中为null的值  
          在前面的章节已经使用过了  
例子：  
    1.查找last\_name全小写的值为velasquez的员工的lastname  
         select last\_name  
        from s\_emp  
        where lower(last\_name) = 'velasquez';

2.查找last\_name的长度>10的所有员工的last\_name  
        select last\_name  
        from s\_emp  
        where length(last\_name)>10;

# 3、数字函数 #

1) ROUND:四舍五入  
         2) TRUNC:截取，不进行四舍五入  
         3) MOD:取余

round 四舍五入  
          round(arg1,arg2)  
          第一个参数表示要进行四舍五入操作的数字  
          第二个参数表示保留到哪一位(负数代表小数点之前，0，正数代表小数点之后)

例如:  
          保留到小数点后面2位  
          select round(45.923,2)  
          from dual;

保留到个位 (个十百千万...)  
          select round(45.923,0)  
          from dual;

保留到十位 (个十百千万...)  
          select round(45.923,-1)  
          from dual;

trunc 截取到某一位   
          trunc(arg1,arg2)  
          和round的用法一样,但是trunc只舍去不进位

例如:  
          截取到小数点后面2位  
          select trunc(45.923,2)  
          from dual;

截取到个位 (个十百千万...)  
          select trunc(45.923,0)  
          from dual;

截取到十位 (个十百千万...)  
          select trunc(45.923,-1)  
          from dual;

mod 取余  
          mod(arg1,arg2)      
          第一个参数表示要进行取余操作的数字  
          第二个参数表示参数1和谁取余  
            
          例如:  
          把10和3进行取余 (10除以3然后获取余数)  
          select mod(10,3)  
          from dual;

# 4、日期函数 #

1) MONTHS\_BETWEEN:两个日期之间的月数  
          2) ADD\_MONTHS:在指定日期上增加月数  
          3) NEXT\_DAY:指定日期的下一个星期几是哪天  
          4) LAST\_DAY:指定日期的最后一天  
          5) ROUND:对指定日期进行四舍五入  
          6) TRUNC:对指定日期进行截取

sysdate关键字  
        表示系统的当前时间      
        例如:  
        显示时间:当前时间  
        select sysdate from dual;

注意:sysdate进行加减操作的时候,单位是天  
        例如:  
        显示时间:明天的这个时候  
        select sysdate+1 from dual;

例如:  
        显示时间:昨天的这个时候  
        select sysdate-1 from dual;  
          
        例如:  
        显示时间:1小时之后的时候  
        select sysdate+1/24 from dual;

months\_between 俩个时间点之间相差多少个月(单位是月)  
        例如:  
        30天之后和现在相差多少个月  
        select months\_between(sysdate+30,sysdate)  
        from dual;

add\_months  返回一个日期数据:表示一个时间点,往后推x月的日期  
        例如:  
        '01-2月-2016'往后推2个月  
        select add\_months('01-2月-2016',2)  
        from dual;          
          
        例如:  
        当前时间往后推4个月  
        select add\_months(sysdate,4)  
        from dual;

next\_day 返回一个日期数据:表示一个时间点后的下一个星期几在哪一天  
        例如:  
        当前时间的下一个星期5是哪一个天  
        select next\_day(sysdate,'星期五')  
        from dual;  
        注意:  
        如果要使用'FRIDAY',那么需要把当前会话的语言环境修改为英文

last\_day 返回一个日期数据:表示一个日期所在月份的最后一天  
        例如:  
        当前日期所在月份的最后一天  
        select last\_day(sysdate)  
        from dual;

round  对日期进四舍五入,返回操作后的日期数据。逢16日往月份进一,逢7月往年份进一  
        round(sysdate,'year/y/yy/yyy/yyyy') 年   7月节点  
        round(sysdate,'mm/month')  月    16号节点  
        round(sysdate,'d/day')  星期   星期四节点  
        round(sysdate,'dd') :  天  -》 12点节点  
        例如:  
        把当前日期四舍五入到月  
        select round(sysdate,'MONTH')  
        from dual;  
          
        把当前日期四舍五入到年  
        select round(sysdate,'YEAR')  
        from dual;

trunc  对日期进行截取 和round类似,但是只舍弃不进位  
        trunc（sysdate,'yyyy/year'） --返回当年第一天。  
        trunc（sysdate,'mm/month'） --返回当月第一天。  
        trunc（sysdate,'d/day'） --返回当前星期的第一天。  
        trunc（sysdate,'dd'）--返回当前年月日

# 5、类型转换函数 #

1).TO\_CHAR 将日期或者数值转换成字符串  
2).TO\_NUMBER 将字符串转换成数字  
3).TO\_DATE 将日期字符串转换成日期

to\_char 把日期转换为字符  
      to\_char('日期字符串','fmt');  
        例如:  
        把当前日期按照指定格式转换为字符串  
        select to\_char(sysdate,'yyyy')  
        from dual;  
          
          日期格式：  
          yyyy/YYYY：四位数的年份  
          rrrr：四位数的年份  
          yy：两位数的年份  
          rr：两位数的年份  
          mm：两位数的月份（数字）  
          D：一周的第几天  
          DD：一月的第几天  
          DDD ：一年的第几天  
          YEAR：英文的年份  
          MONTH：英文全称的月份  
          mon：英文简写的月份  
          ddsp：英文的第几天  
          ddspth：英文序列数的第几天  
          DAY：全英文的星期  
          DY：简写的英文星期  
          hh：小时  
          mi：分钟  
          ss：秒  
          AM:上午  
          PM:下午

例如:  
        测试常见的一些日期数据转换为字符串的格式  
        select to\_char(sysdate,'yyyy MM D DD DDD YEAR MONTH ddsp ddspth DAY DY') from dual;

select to\_char(sysdate,'dd-mm-yy')  
        from dual;

select to\_char(sysdate,'dd-mm-yy HH24:MI:SS AM')  
        from dual;

#    6、千年虫: #

在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说，假如是1971年，在计算机里就会被表示为71，但是到了2000年的时候这个情况就出现了问题，计算机就会将其年份表示为00。这样的话计算机内部对年份的计算就会出现问题。这个事情当时被称为千年虫  
      
        数据库中表示日期中年份的有俩种: yy和rr  
        之前一直使用的时候yy格式,后来才有的rr格式  
        yy表示使用一个俩位数表示当前年份:  
        1990 ---yy数据库格式---> 90  
        1968 ---yy数据库格式---> 68  
        1979 ---yy数据库格式---> 79  
          
        rr格式表示: 另外参照图片:rr日期格式规则.png  
        如果日期中的年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定：提供年份的两位数字(指定年)，数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年所在世纪的规则如下：  
        规则1 如果指定年在00~49之间，并且当前年份在00~49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015。  
        规则2 如果指定年在50~99之间，并且当前年份在00~49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975。  
        规则3 如果指定年在00~49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115。  
        规则4 如果指定年在50~99之间，并且当前年份在50~99之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055。

注意:rr格式并没有完全的解决俩位数年份保存的问题,思考里面还有哪些问题存在。

to\_char 把数字转换为字符  
        L : 本地货币符号  
        $ : $  
        . : 小数点  
        , : 千分符  
        9 :  0-9  
        0 :  0-9, 如果位数不足，强制补0  
          
        例如:  
        select to\_char(salary,'$999,999.00')   
        from s\_emp;

fm表示去除结果显示中的开始的空格  
        select to\_char(salary,'fmL999,999.00')   
        from s\_emp;

L表示系统本地的货币符号  
        select to\_char(salary,'fmL999,999.00')   
        from s\_emp;

to\_number 把字符转换为数字  
        例如:  
        select to\_number('1000')   
        from dual;  
          
        //这个写法是错的 abc不能转换为数字  
        select to\_number('abc')  
        from dual;

to\_date 把字符转换为日期  
        .TO\_DATE(char, \['fmt'\]):例如    
        select TO\_DATE ('10-September-1992','dd-Month-YYYY') from dual  
         .使用format的元素格式  
        例如:  
        select to\_date('10-12-2016','dd-mm-yyyy')  
        from dual;

select to\_date('25-5月-95','dd-month-yy')  
        from dual;

//session语言环境设置为英文下面可以运行  
        select to\_date('25-MAY-95','dd-MONTH-yy')  
        from dual;

oracle数据库中表示一个日期数据的几种方式  
      1.sysdate  
      2.oracle默认的日期格式 例如:'25-MAY-95'  
      3.to\_date函数转换