Oracle 函数

Oracle SQL语句中经常使用到Oracle自带的函数，这些函数丰富了SQL的语言功能，为Oracle SQL提供了更多的操作性。Oracle函数可以接受零个或者多个输入参数，并返回一个输出结果。 Oracle 数据库中主要使用两种类型的函数：

1、单行函数：对每一个函数应用在表的记录中时，只能输入一行中的列值作为输入参数(或常数)，并且返回一个结果。

例如1：MOD(X,Y) 是求余函数，返回的X除以Y的余数，其中X和Y可以是列值，也可以是常数。

例如2：TO_CHAR(X,'YYYYMMDD')是时间类型转字符串的函数，其中X可以是行中某一时间类型（date）的列，也可以是一个时间类型的常数。

常用的单行函数大致以下几类：

字符串函数：对字符串进行操作，例如：TO_CHAR()、SUBSTR()、DECODE()等等。

数值函数：对数值进行计算或操作，返回一个数字。例如：ABS()、MOD()、ROUND()等等。

转换函数：将一种数据类型转换成另外一种类型：例如：TO_CHAR()、TO_NUMBER()、TO_DATE()等等。

日期函数：对时间和日期进行操作的函数。例如：TRUNC()、SYSDATE()、ADD_MONTHS()等等。

2、聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如 SUM(x)返回结果集中 x 列的总合。

一、Oracle字符型函数

Oracle字符型函数是单行函数当中的一种，是用来处理字符串类型的函数，通过接收字符串参数，然后经过操作返回字符串结果的函数。

常用的函数如下表：

二、Oracle日期型函数

Oracle日期类型函数是操作日期、时间类型的相关数据，返回日期时间类型或数字类型结果，常用的函数有：SYSDATE()、ADD_MONTHS（）、LAST_DAY（）、TRUNC()、ROUND()等等。

1、系统日期、时间函数：

·SYSDATE函数：该函数没有参数，可以得到系统的当前时间。

案例代码：

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

结果：

·SYSTIMESTAMP函数：该函数没有参数，可以得到系统的当前时间，该时间包含时区信息，精确到微秒。

案例代码：

select systimestamp from dual;

结果：

2、数据库时区函数：

·DBTIMEZONE函数：该函数没有输入参数，返回数据库时区。

案例代码：

select dbtimezone from dual;

结果：

3、给日期加上指定的月份函数：

ADD_MONTHS（r,n）函数：该函数返回在指定日期r上加上一个月份数n后的日期。其中

r：指定的日期。

n：要增加的月份数，如果N为负数，则表示减去的月份数。

案例代码：

select to_char(add_months(to_date('2018-11-12','yyyy-mm-dd'),1),'yyyy-mm-dd'),
       to_char(add_months(to_date('2018-10-31','yyyy-mm-dd'),1),'yyyy-mm-dd'),
       to_char(add_months(to_date('2018-09-30','yyyy-mm-dd'),1),'yyyy-mm-dd')        
  from dual;

结果：（如果指定的日期是月份的最后一天，返回的也是新的月份的最后一天，如果新的月份比指定的月份日期少，将会自动调回有效日期）

4、月份最后一天函数:

LAST_DAY(r)函数：返回指定r日期的当前月份的最后一天日期。

案例代码：

select last_day(sysdate) from dual;

结果：

5、指定日期后一周的日期函数:

NEXT_DAY(r,c)函数：返回指定R日期的后一周的与r日期字符（c：表示星期几）对应的日期。

案例代码：

select next_day(to_date('2018-11-12','yyyy-mm-dd'),'星期四') from dual;

结果：

6、返回指定日期中特定部分的函数：

EXTRACT（time）函数：返回指定time时间当中的年、月、日、分等日期部分。

案例代码：

select  extract( year from timestamp '2018-11-12 15:36:01') as year,
        extract( month from timestamp '2018-11-12 15:36:01') as month,        
        extract( day from timestamp '2018-11-12 15:36:01') as day,  
        extract( minute from timestamp '2018-11-12 15:36:01') as minute,
        extract( second from timestamp '2018-11-12 15:36:01') as second
 from dual;

结果：

7、返回两个日期间的月份数：

MONTHS_BETWEEN(r1,r2)函数：该函数返回r1日期和r2日期直接的月份。当r1>r2时，返回的是正数，假如r1和r2是不同月的同一天，则返回的是整数，否则返回的小数。当r1<r2时，返回的是负数。

案例代码：

select months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
                      to_date('2017-11-12', 'yyyy-mm-dd')) as zs, --整数
       months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
                      to_date('2017-10-11', 'yyyy-mm-dd')) as xs, --小数
       months_between(to_date('2017-11-12', 'yyyy-mm-dd'),
                      to_date('2018-10-12', 'yyyy-mm-dd')) as fs --负数
  from dual;

结果：

8、日期截取函数：

·ROUND（r[,f]）函数：将日期r按f的格式进行四舍五入。如果f不填，则四舍五入到最近的一天。

案例代码：

select sysdate, --当前时间
       round(sysdate, 'yyyy') as year, --按年
       round(sysdate, 'mm') as month, --按月
       round(sysdate, 'dd') as day, --按天
       round(sysdate) as mr_day, --默认不填按天
       round(sysdate, 'hh24') as hour --按小时
  from dual;

结果：

·TRUNC（r[,f]）函数：将日期r按f的格式进行截取。如果f不填，则截取到当前的日期。

案例代码：

select sysdate, --当前时间
       trunc(sysdate, 'yyyy') as year, --按年
       trunc(sysdate, 'mm') as month, --按月
       trunc(sysdate, 'dd') as day, --按天
       trunc(sysdate) as mr_day, --默认不填按天
       trunc(sysdate, 'hh24') as hour --按小时
  from dual;

结果：

三、Oracle数值型函数

Oracle数值型函数可以是输入一个数值，并返回一个数值的函数，我们经常用到函数如下表：

四、Oracle转换函数

Oracle转换函数是进行不同数据类型转换的函数，是我们平常数据库开发过程当中用的最多的内置函数。常用的函数有to_char()、to_number()、to_date()等等。详细分析如下表：

提醒：其中数值的格式f可以参考下表：

五、Oracle聚合函数

Oracle聚合函数同时可以对多行数据进行操作，并返回一个结果。比如经常用来计算一些分组数据的总和和平均值之类，常用函数有AVG()、SUM()、MIN()、MAX()等等。

下列案例所需表结构参考：学生信息系统。

1、AVG函数

AVG([distinct ] expr)：该函数可以求列或列组成的表达式expr的平均值，返回的是数值类型。其中 distinct是可选参数，表示是否去掉重复行。

案例1、求学生信息表（stuinfo）中学生的平均年龄，代码如下：

select * from stuinfo;
select avg(t.age) from stuinfo t;

结果：

使用该函数和其他聚合函数时，都可以和where条件语句和分组GROUP BY 查询组合使用，得到特定的结果。

案例2,、按照班级求各班学生的平均年龄（其中年龄大于等于30岁的不计入在内）。代码：

select classno, avg(t.age)
  from stuinfo t
 where t.age < 30
 group by t.classno;

结果：

2、COUNT函数

count（*|[distinct]expr）函数可以用来计算查询结果的条数或行数。函数中必须指定列名或者表达式expr，不然就要全选使用*号。

案例3、查询学生信息表中所有的学生的个数。代码如下：

select count(*) from stuinfo;

结果：

3、MAX/MIN函数

MAX([distinct] expr)、MIN([distinct] expr)函数可以返回指定列或列组成的表达式expr中的最大值或最小值。该函数也通常和where条件、group by分组结合在一起使用。

案例4、求学生信息表中年龄最大的学生的年龄：

select max(age) from stuinfo;

结果：

4、SUM函数

SUM([distinct] expr)函数可以对指定列或列组成的表达式expr进行求和，假如不使用分组group by ,那就是按照整表作为一个分组。

案例5、正好利用sum函数求和乘以人数进行求学生的平均年龄

select classno, sum(age), count(*), sum(age) / count(*), avg(age)
  from stuinfo t
 where t.age < 30
 group by t.classno;

结果：

转载本站内容时，请务必注明来自W3xue，违者必究。