Oracle中常用函数

发表于 | 分类于 ， |
| 字数统计: 2,776

case函数（case…when…）

case函数具有两种格式。简单Case函数和Case搜索函数。

简单Case函数

格式说明：

1
2
3
4
5
case 	列名
when	条件值1	then  选项1
when	条件值2	then  选项2
else	默认值      
end

比如：

1
2
3
4
5
6
7
8
select case fcountry
when '中国' then '亚洲'
when '日本' then '亚洲'
when '英国' then '欧洲'
when '德国' then '欧洲'
when '美国' then '北美洲'
else 'unknow' end as fcontinent
from t_test

Case条件函数

格式说明：

1
2
3
4
case  
when  指定列的条件1   then  选项1
when  指定列的条件2    then  选项2
else  默认值 end

示例：

1
2
3
4
5
6
select case 
when fscore >= 90 then 'A'
when fscore >= 75 and fscore < 90 then 'B'
when fscore >= 60 and fscore < 75 then 'C'
else 'D' end as flevel
from t_test01

tunc函数

TRUNC函数用于对值进行截断。
用法有两种：一种是截取数字，另一种是截取日期。

截取数字

格式：TRUNC（number,num_digits）
number：需要截尾取整的数字。
num_digits：用于指定取整精度的数字。num_digits 的默认值为 0。如果num_digits为正数，则截取小数点后num_digits位；如果为负数，则先保留整数部分，然后从个位开始向前数，并将遇到的数字都变为0。

TRUNC()函数在截取时不进行四舍五入，直接截取。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select trunc(123.458) from dual --123

select trunc(123.458,0) from dual --123

select trunc(123.458,1) from dual --123.4

select trunc(123.458,-1) from dual --120

select trunc(123.458,-4) from dual --0

select trunc(123.458,4) from dual --123.458

select trunc(123) from dual --123

select trunc(123,1) from dual --123

select trunc(123,-1) from dual --120
截取日期
1
2
3
4
5
6
7
8
9
10
11
12
13
select trunc(sysdate) from dual --2017/6/13  返回当天的日期

select trunc(sysdate,'yyyy') from dual   --2017/1/1  返回当年第一天.

select trunc(sysdate,'mm') from dual  --2017/6/1  返回当月第一天.

select trunc(sysdate,'d') from dual  --2017/6/11 返回当前星期的第一天(以周日为第一天).

select trunc(sysdate,'dd') from dual  --2017/6/13  返回当前年月日

select trunc(sysdate,'hh') from dual  --2017/6/13 13:00:00  返回当前小时

select trunc(sysdate,'mi') from dual  --2017/6/13 13:06:00  返回当前分钟

bitand函数

功能：返回两个数值型数值在按位进行与（AND）运算后的结果。
语法：BITAND(nExpression1, nExpression2)
参数 nExpression1, nExpression2 即是需要做按位与运算的两个数值。如果 nExpression1 和 nExpression2 为非整数型，那么它们在按位与运算之前转换为整数。
返回值：数值型
说明：BITAND( )函数会将 nExpression1 的每一位同 nExpression2 的相应位进行位比较。如果 nExpression1 和 nExpression2 的位都是 1，相应的结果位就是 1；否则相应的结果位是 0。
示例：
x = 5 //二进制为 0101
y = 6 //二进制为 0110
bitand(x,y)的结果是4 //二进制为 0100

wm_concat()函数

wm_concat()函数可以把列值以”,”作为分隔符连接起来，并显示成一行，实现列转行的功能。

例如shopping表结构及数据如下：

u_id goods num
1 苹果 2
2 梨子 5
1 西瓜 4
3 葡萄 1
3 香蕉 1
1 橘子 3

想要的结果为:

u_id goods_sum
1 苹果(2斤),西瓜(4斤),橘子(3斤)
2 梨子(5斤)
3 葡萄(1斤),香蕉(1斤)

可以使用wm_concat()函数编写sql如下：

1
select u_id, wm_concat(goods || '(' || num || '斤)')goods_sum from shopping group by u_id;

注意：在较新版本的Oracle版本中，已不再使用wm_concat()函数，而是使用性能更高的listagg函数。

listagg函数

listagg函数是Oracle11gR2开始正式推出的字符串聚合函数，在Oracle11之前还可以使用WM_CONTACT函数，但是WM_CONTACT函数效率较低，Oracle12已经废弃不能再用。
listagg函数的主要功能是可以根据分组，将多个行的内容聚合为一条记录，并用指定的分隔符连接起来。

例如shopping表结构及数据如下：

u_id goods num
1 苹果 2
2 梨子 5
1 西瓜 4
3 葡萄 1
3 香蕉 1
1 橘子 3

想要的结果为:

u_id goods_sum
1 苹果(2斤),西瓜(4斤),橘子(3斤)
2 梨子(5斤)
3 葡萄(1斤),香蕉(1斤)

使用listagg函数编写的sql如下：

1
2
3
SELECT u_id, LISTAGG(goods||'('||num||'斤)', ',') WITHIN GROUP(ORDER BY u_id) AS goods_sum
FROM shopping
GROUP BY u_id;

如果不需要分隔符，也可以写成

1
2
3
SELECT u_id, LISTAGG(goods||'('||num||'斤)') WITHIN GROUP(ORDER BY u_id) AS goods_sum
FROM shopping
GROUP BY u_id;

总结：

  1. 使用该函数必须的进行分组(GROUP BY)
  2. listagg函数第一个参数表示需要进行枚举的字段，第二个参数表示枚举数据的分隔符
  3. 对于枚举的字段同时还需要排序和分组WITHIN GROUP(ORDER BY xx)

NVL()函数

介绍：格式为NVL(expr1,expr2)，若expr1为null, 返回expr2; 不为null,返回expr1。 注意：两者类型要一致。

NULLIF函数

介绍：格式为NULLIF(exp1,expr2)，如果exp1和exp2相等则返回空(NULL)，否则返回第一个值exp1。

mod(m,n)函数

mod(m,n)函数是取模函数，意思是数值m取n的模，即：mod(m，n)=m/n的余数，如mod(5,3）=2，mod(6,3)=0；取模运算值为正数，若是负数取模后等于余数和模数的和值，mod(-7,3)=2（即-1+3=2）。

ROUND函数

ROUND函数是用来对一个数字做四舍五入的截取。
格式：ROUND（number[,decimals]）

  • number：需要做截取处理的数值
  • decimals：指明需保留小数点后面的位数。这是一个可选项，如果没有该参数则默认截去所有的小数部分，并四舍五入。注：如果为负数则表示从小数点开始左边的位数，相应整数数字用0填充，小数被去掉。

示例：

1
2
3
4
5
6
7
8
9
10
11
SQL>   select  ROUND(1234.5678,3)   from   dual;

ROUND(1234.5678,3)
——————
1234.568

SQL>   select ROUND(1234.5678,-2)   from   dual;

ROUND(1234.5678,-2)
——————-
1200

前面已经介绍过，TRUNC函数也可以对数字进行截取，但是TRUNC函数截取的时候不会四舍五入。

FLOOR函数

对给定的数字取整数位。

1
2
3
4
5
SQL> select floor(2345.67) from dual;

FLOOR(2345.67)
--------------
2345

CEIL函数

返回大于或等于给出数字的最小整数。

1
2
3
4
5
SQL> select ceil(3.1415927) from dual;

CEIL(3.1415927)
---------------
4

TO_DATE函数

将一个字符串转为时间格式。

1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT TO_DATE('2018-08-20 14:48:02', 'YYYY-MM-DD hh24:mi:ss') FROM DUAL; 

TO_DATE('2018-08-20 14:48:02', 'YYYY-MM-DD hh24:mi:ss')
---------------
2018/8/20 14:48:02


SQL> SELECT TO_DATE('2018-08-20', 'YYYY-MM-DD') FROM DUAL; 

TO_DATE('2018-08-20', 'YYYY-MM-DD')
---------------
2018/8/20

substr函数

substr函数用来截取字符串，格式有两种：

  • 格式1：substr(string str, int a, intb)；
    1. str是需要截取的原字符串。
    2. a是截取字符串的开始位置。注意：当a等于0或1时，都是从第一位开始截取；当a为负数时，表示从后往前数的第-a个位置。
    3. b是要截取的字符串的长度。
  • 格式2：substr(string str, int a)；
    1. str是需要截取的原字符串。
    2. a是截取字符串的开始位置，一直到字符串的最后位置。

举例：

1
2
3
4
5
6
substr('HelloWorld',0,3);  //返回结果：Hel，截取从“H”开始3个字符 
substr('HelloWorld',1,3);  //返回结果：Hel，截取从“H”开始3个字符 
substr('HelloWorld',2,3);  //返回结果：Hel，截取从“e”开始3个字符 
substr('HelloWorld',0,100);  //返回结果：HelloWorld，100虽然超出待处理的字符串最大长度，但不会影响返回结果，按最大长度返回

substr('HelloWorld',2,3);  //返回结果：elloWorld，截取从“e”开始之后的所有字符

lower、upper函数

作用：将文本内容全部转成小写或者大写

sum函数

作用是对字段求和，但必须注意，在oracle中，null+任何数=null，因此select sum(3+null) from dual;的结果会是空的。

所以比如两个字段v1和v2，如果v2有可能为空，那么对这两个字段的和求和的话，需要写成如下：

1
select sum(v1+nvl(v2,0)) from XXX;

count函数

count函数是用来统计记录的数量。
count函数的用法可以一个简单示例来说明一下，对于如下测试数据

其中，表中ID和NAME都是不重复的数据，HOME、TEL、PATH中存在重复数据，其中PATH中存在空数据。
执行sql语句：

1
SELECT COUNT(*) , COUNT(1)  ,COUNT( DISTINCT HOME) , COUNT( DISTINCT TEL) , COUNT(PATH) ,  COUNT( DISTINCT PATH) FROM TEST;

COUNT(*) ：统计表中所有的记录数量，包括null数据。
COUNT(1) ：统计表中所有数据数量，和上面的一样，不过效率要比COUNT（*）快。
COUNT( DISTINCT HOME) ：统计表中去重之后的home数据
COUNT( DISTINCT TEL) ：统计表中去重之后的tel数据，此表中tel值只有两个不同的数据。
COUNT(PATH) ：统计表中所有的path值，但是会自动去除null数据，此表中有三条null数据。
COUNT( DISTINCT PATH)统计表中去重之后的数据总数（肯定不会包括null数据）。

查询结果如下：

sign函数

sign(数值)。如果数值大于0返回1，等于0返回0，小于0返回-1；

1
select sign(-15.5),sign(0),sign(15.5) from dual;

decode函数

decode函数有两种语法格式。

  1. decode(expression,value,result1,result2)
    含义：如果expression=value，则输出result1，否则输出result2

    例子：

  2. decode(expression,value1,result1,value2,result2,value3,result3……,default)
    含义：如果expression=value1，则输出result1，expression=value2，输出reslut2，expression=value3，输出result3，若expression不等于所列出的所有value，则输出为default

    例子：

LEAST、GREATEST函数

LEAST函数用于在给定的表达式中返回最小值。
GREATEST函数用于在给定的表达式中返回最大值。
这两个函数的参数可以是2个或者多个。

1
2
3
SELECT LEAST(5, 10) FROM DUAL;

SELECT GREATEST(1, 5, 2, 4, 3) FROM DUAL;

需要注意的是，LEAST、GREATEST函数只能用于比较数值、日期或字符类型的值，并且所有参数的类型必须相同。如果传递的参数类型不同，Oracle将尝试隐式转换类型，但是可能会产生错误结果。

add_months函数

语法格式如下：

1
ADD_MONTHS(date,months)

其中：
date：某个日期。
months：要加上的月份数，要减去的月份数用负数。

日期中的日是不变的。如果开始日期是某月的最后一天，那么，结果将会调整以使返回值仍对应新的一月的最后一天。如果，结果月份的天数比开始月份的天数少，那么，也会向回调整以适应有效日期。

示例：

1
2
3
ADD_MONTHS(TO_DATE(’15-Nov-1961’,’d-mon-yyyy’),1) =’15-Dec-1961'
ADD_MONTHS(TO_DATE(’30-Nov-1961’,’d-mon-yyyy’),1) =’31-Dec-1961'
ADD_MONTHS(TO_DATE(’31-Jan-1999’,’d-mon-yyyy’),1) =’28-Feb-1999'

注，在上面的第三个例子中，函数会将31日往回调整为28日，使结果对应新一月的最后一天；在第二个例子中，则是从30往后调整到31日，也同样是为了保持对应为最后一天。

------ 本文完 ------