Oracle中常用函数

发表于 更新于 分类于 ， 本文字数： 4.9k 阅读时长 ≈ 18 分钟

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

TO_CHAR函数

功能：将数字、日期或者时间戳转成（指定格式的）字符串。
语法：

1
TO_CHAR( value, [ format_mask ], [nls_language ] )
  • value：要转换的数字或日期。
  • format_mask：定义输出字符串格式的可选参数。
  • nls_language：指定数字和货币的本地化可选参数。
日期转换
1
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

这将返回当前日期和时间，格式为’YYYY-MM-DD HH24:MI:SS’。
格式说明：

  • ‘YYYY’: 4位数的年份
  • ‘MM’: 月份（01-12）
  • ‘DD’: 日期（01-31）
  • ‘HH24’: 小时（00-23）
  • ‘MI’: 分钟（00-59）
  • ‘SS’: 秒（00-59）
数字转换
1
SELECT TO_CHAR(123456.789, '999,999.99') FROM DUAL;   --结果为：123,456.79

格式说明：’9’表示数字，’,’表示逗号分隔符，’.’表示小数点。
其中9也可以换成0，0和9的区别：

  • （1）9代表：如果整数位存在数字则显示数字，不存在则显示空格；小数位存在则显示数字，不存在则显示0。
  • （2）0代表：整数位和小数位存在数字则显示数字，不存在则显示0，即占位符。
  • （3）还可以在字符串加FM前缀，代表：删除因9带来的空格或0。

以下是一些演示示例，可以加深理解。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT TO_CHAR('.4', '999.99') FROM DUAL;             --结果：.40
SELECT TO_CHAR('.4', 'FM999.99') FROM DUAL;           --结果：.4

SELECT TO_CHAR('.4', '999.90') FROM DUAL;               --结果：.40
SELECT TO_CHAR('.4', 'FM999.90') FROM DUAL;             --结果：.40


SELECT TO_CHAR('0.4', '999.99') FROM DUAL;              --结果：.40
SELECT TO_CHAR('0.4', '990.99') FROM DUAL;              --结果：0.40


SELECT TO_CHAR('12.468', '999.99') FROM DUAL;           --结果：12.47
SELECT TO_CHAR('12.468', 'FM000.00') FROM DUAL;         --结果：012.47

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函数

作用是对字段求和，如果这组值中包含NULL,那么SUM函数将忽略这些NULL值,并返回非NULL值的总和。

但必须注意，在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函数是一个功能强大的条件判断函数，其作用是在 SQL 语句里进行条件判断与值替换。下面从基本语法、常见使用场景等方面详细介绍。

基本语法
1
2
3
4
DECODE(expression, value1, result1, 
               value2, result2, 
               ...
               [default])

  • expression：这是需要进行比较的表达式，通常是列名或者其他表达式。

  • value1, value2, …：这些是用于和 expression 进行比较的值。

  • result1, result2, …：当 expression 与对应的 value 值相等时，返回的结果。

  • default：这是可选参数，若 expression 与所有 value 值都不相等，就返回该默认值。若未指定 default，则返回 NULL。

    例子：

使用场景

简单的条件判断
假设存在一个 employees 表，包含 department_id 列，现在要将部门 ID 转换为部门名称。

1
2
3
4
5
6
7
SELECT employee_id,
       DECODE(department_id, 
              10, 'HR', 
              20, 'IT', 
              30, 'Finance', 
              'Other') AS department_name
FROM employees;

在这个例子中，DECODE 函数会依据 department_id 的值返回对应的部门名称。若 department_id 是 10，就返回 HR；若为 20，返回 IT；若为 30，返回 Finance；若都不匹配，返回 Other。

替代 CASE 语句
DECODE 函数能替代简单的 CASE 语句。以下是一个使用 CASE 语句的例子以及对应的 DECODE 函数实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 使用 CASE 语句
SELECT employee_id,
       CASE department_id
           WHEN 10 THEN 'HR'
           WHEN 20 THEN 'IT'
           WHEN 30 THEN 'Finance'
           ELSE 'Other'
       END AS department_name
FROM employees;

-- 使用 DECODE 函数
SELECT employee_id,
       DECODE(department_id, 
              10, 'HR', 
              20, 'IT', 
              30, 'Finance', 
              'Other') AS department_name
FROM employees;

统计不同条件下的数量
假设有一个 orders 表，包含 status 列，要统计不同订单状态的数量。

1
2
3
4
5
SELECT 
    SUM(DECODE(status, 'Pending', 1, 0)) AS pending_orders,
    SUM(DECODE(status, 'Shipped', 1, 0)) AS shipped_orders,
    SUM(DECODE(status, 'Cancelled', 1, 0)) AS cancelled_orders
FROM orders;

在这个例子中，DECODE 函数会根据 status 列的值返回 1 或者 0，然后使用 SUM 函数对这些值进行求和，从而得到不同订单状态的数量。

注意事项
  • 数据类型要一致：expression、search 值和 result 值的数据类型应该保持一致，不然可能会出现数据类型不匹配的错误。
  • 性能问题：在处理复杂的条件判断时，DECODE 函数的性能可能不如 CASE 语句，尤其是当条件较多时。所以，在复杂场景下，建议使用 CASE 语句。

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日，也同样是为了保持对应为最后一天。

cast函数

在Oracle数据库中，CAST函数用于将一种数据类型转换为另一种数据类型。它的基本语法如下：

1
CAST(expression AS datatype [(length)])

其中：

  • expression：是要转换的表达式，可以是列名、变量、函数返回值等。
  • datatype：是目标数据类型，即你想要将expression转换成的数据类型。
  • length：是可选参数，表示目标数据类型的长度。并非所有数据类型都需要指定长度，但对于某些数据类型（如VARCHAR2）则是必需的。

示例1
将日期类型的表达式转换为字符类型：

1
SELECT CAST(SYSDATE AS VARCHAR2(10)) AS date_string FROM DUAL;

这将返回当前日期的字符串表示形式，例如字符串“2023-01-01”（具体格式可能因Oracle的配置和会话的日期格式设置而异）。
示例2
将字符类型的表达式转换为日期类型（假设日期字符串格式正确，参考上面sql语句的返回格式）：

1
SELECT CAST('2023-01-01' AS DATE) AS date_value FROM DUAL;

这将返回一个日期类型的值，即日期“2023-01-01”。
示例3
将数字类型的列转换为字符类型（例如，假设你有一个名为salary的数字列，并希望将其转换为字符类型以进行某些操作）：

1
SELECT CAST(salary AS VARCHAR2(10)) AS salary_string FROM employees;

注意事项

  • 如果转换失败（例如，尝试将包含非数字字符的字符串转换为数字类型），CAST函数将抛出一个异常。因此，在使用CAST函数时，确保源数据类型和目标数据类型是兼容的。
  • Oracle还提供了其他一些类似于CAST函数的类型转换函数，如TO_CHAR、TO_NUMBER、TO_DATE等。这些函数提供了更多的灵活性和选项，因此在实际应用中可能会更常用。可以根据具体的需求选择合适的函数使用。

COALESCE函数

COALESCE() 函数接受一个或多个参数，并返回第一个非 NULL 的参数。
语法：

1
COALESCE(argument1, argument2,...);

COALESCE 函数从左到右计算其参数，并返回第一个非 NULL 的参数。
如果所有输入参数都为 NULL，则 COALESCE 函数将返回 NULL。

示例

1
2
SELECT COALESCE(1, 2, 3) AS result FROM DUAL;       --输出结果：1
SELECT COALESCE(NULL, 'SQL', 'Tutorial') AS result FROM DUAL;       --输出结果：SQL

注意：以下语句返回的是 1 而不是错误：

1
SELECT COALESCE(1, 1 / 0) result FROM DUAL;

原因是 COALESCE 函数使用了短路求值。这意味着 COALESCE 函数在遇到第一个非 NULL 参数后，不会再计算其余的参数。

与NVL函数比较*

Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。
区别与选择：

  • 参数数量：NVL函数只能接受两个参数，而COALESCE函数可以接受多个参数。
  • 效率：COALESCE函数在处理多个参数时效率更高，因为它类似于条件语句，可以快速找到第一个非空值。
  • 兼容性：NVL函数主要用于Oracle数据库，而COALESCE函数在多种数据库系统中都可用。

示例
假设有一个视图v，其数据如下：

1
2
3
4
CREATE OR REPLACE VIEW v AS
SELECT NULL AS C1, NULL AS C2, 1 AS C3, NULL AS C4, 2 AS C5, NULL AS C6 FROM DUAL
UNION ALL
SELECT NULL AS C1, NULL AS C2, NULL AS C3, 8 AS C4, NULL AS C5, 5 AS C6 FROM DUAL;

使用COALESCE函数：

1
SELECT COALESCE(C1, C2, C3, C4, C5, C6) AS c FROM v;

结果：

1
2
3
4
c
-
1
8

使用NVL函数：

1
SELECT NVL(NVL(NVL(NVL(NVL(C1, C2), C3), C4), C5), C6) AS c FROM v;

结果相同，但语法较为冗长。
总结：在处理多个可能为空的值时，建议使用COALESCE函数，因为它更简洁且效率更高。

INSTR函数

语法：

1
INSTR( string1, string2 [, start_position [, occurrence ]] )

解释：在string1源字符串中查找string2子串，返回子串首字符所在位置；找不到返回 0。
注意：下标从 1 开始，不是 0；且区分大小写！

参数说明：

参数 含义 默认值 说明
string1 源字符串（被查找） 必填 VARCHAR2 / CHAR / CLOB 类型
string2 要查找的子串 必填 子串为空串时返回 NULL
start_position 起始查找位置 1 > 0：从左向右查找
< 0：从右往左，从倒数第 abs(position) 个字符开始反向查找
occurrence 取第几次匹配 1 必须 > 0，不能为负数，表示取第 N 次出现的子串位置

注意：start_position为负数，只是表示起点从右侧倒数，匹配扫描方向是向左；但返回位置永远是相对于字符串最左侧的序号。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 1. 默认：查找第1次出现'l'
SELECT INSTR('helloworld','l') FROM DUAL; --返回3

-- 2. 从第4位开始，查找第1次'l'
SELECT INSTR('helloworld','l',4) FROM DUAL; --返回4

-- 3. 查找第2次出现'l'
SELECT INSTR('helloworld','l',1,2) FROM DUAL; --返回4

-- 4. 反向查找：从右侧倒数第1位开始，找第1次'o'（取最后一个o）
SELECT INSTR('helloworld','o',-1,1) FROM DUAL; --返回7

-- 5. 找不到子串返回0
SELECT INSTR('helloworld','x') FROM DUAL; --0

-- 6. 官方示例：从第3字符开始，找第2次OR
SELECT INSTR('CORPORATE FLOOR','OR',3,2) FROM DUAL; --14

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