Oracle 常用函数大全

一、空校验

1. NVL 空校验

NVL(exp1, exp2) ：若 exp1 不为 NULL，则返回 exp1；若 exp1 为 NULL，则返回 exp2。

无论 exp1 是否为 NULL ，均会计算 exp2。

2. COALESCE 空校验

COALESCE(exp1, exp2, …, expn) ：返回第一个非空表达式，如果全是 NULL，则返回 NULL。

顺序计算表达式的值，直到不为 NULL。

二、排序

1. ORDER BY 排序

ORDER BY column ASC

简单排序。

SELECT
	t1.USER_ID,
	t1.USER_NAME
FROM
	SYS_USER t1
ORDER BY
	t1.CREATE_TIME DESC

2. ORDER BY DECODE 指定值排序

ORDER BY DECODE(column, ‘特定值1’, 1, ‘特定值2’, 2, …, ‘其他值’, n) ASC

指定特定值的权重进行排序。

SELECT
	t1.USER_ID,
	t1.USER_NAME,
	t1.USER_STATUS
FROM
	SYS_USER t1
ORDER BY
	DECODE(t1.USER_STATUS, '特定值1', 1, '特定值2', 2, ..., '其他值', n) ASC

在这个例子中，USER_STATUS 中 ‘特定值1’ 的排序权重为1，‘特定值2’ 的排序权重为2，依此类推，并指定 ASC 顺序排序。

三、排名

1. RANK 排名

RANK() OVER (ORDER BY column DESC) AS column_rank

用于字段排名。多行数据的字段值相同时，排名相同。当前排名 = 上一个排名 + 上一个排名的总记录数。

SELECT
	t1.USER_ID,
	t1.USER_NAME,
	t1.SCORE,
	RANK() OVER (ORDER BY t1.SCORE DESC) AS SCORE_RANK
FROM
	SYS_USER t1

2. DENSE RANK 密集排名

DENSE RANK() OVER (ORDER BY column DESC) AS column_rank

用于字段排名。多行数据的字段值相同时，排名相同。当前排名 = 上一个排名 + 1。

SELECT
	t1.USER_ID,
	t1.USER_NAME,
	t1.SCORE,
	DENSE RANK() OVER (ORDER BY t1.SCORE DESC) AS SCORE_RANK
FROM
	SYS_USER t1

四、限制条数

1. ROWNUM 限制

WHERE ROWNUM <= n

只获取 n 条记录。ROWNUM 写在 WHERE 条件中，只能获取 n 条记录后再进行 ORDER BY 排序，无法在 ORDER BY 排序后获取前 n 条记录。

SELECT
	t1.USER_ID,
	t1.USER_NAME
FROM
	SYS_USER t1
WHERE
	ROWNUM <= 5

2. FETCH 限制

FETCH FIRST n ROWS ONLY

只获取前 n 条记录。可以在 ORDER BY 排序后获取前 n 条记录。

SELECT
	t1.USER_ID,
	t1.USER_NAME
FROM
	SYS_USER t1
ORDER BY
	t1.CREATE_TIME DESC
FETCH FIRST 5 ROWS ONLY

五、字符串处理

1. TO_CHAR 字符串转换

TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’)

参数1：转换的对象（数字、日期或时间戳）。
参数2：格式模型（可选），指定如何格式化转换后的字符串。

SELECT 
	TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
FROM 
	SYS_USER t1

2. || 字符串拼接

str1 || str2 || …

|| 将 NULL 视为一个空字符串。|| 运算符优先级高于 CONCAT 函数。|| 可以用于连接字符串、数字或日期等不同类型的数据。

SELECT
	t1.USER_ID,
	t1.USER_NAME || '=' || t1.SCORE
FROM
	SYS_USER t1

3. CONCAT 字符串拼接

CONCAT(CONCAT(str1, str2), str3)

如果任何输入字符串为 NULL，CONCAT 返回 NULL。Oracle 的 CONCAT 函数只允许两两字符串拼接。

SELECT
	t1.USER_ID,
	CONCAT(CONCAT(t1.USER_NAME, '='), t1.SCORE)
FROM
	SYS_USER t1

4. INSTR 字符串包含

INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)。

例如：INSTR(‘string’, ‘in’, -1, 1) 。源字符串为 ‘string’，目标字符串为 ‘in’，查询起始位置为 -1（可省略，默认 -1），取第 1 个匹配项的位置（可省略，默认 1）。

<!-- 查询姓名包含‘王’的用户 -->
SELECT
	t1.USER_ID,
	t1.USER_NAME
FROM
	SYS_USER t1
WHERE
	INSTR(t1.USER_NAME, '王') > 0

<!-- 查询姓名包含‘王’的所有用户数量 -->
SELECT
	INSTR(t1.USER_NAME, '王') AS NUM
FROM
	SYS_USER t1

实现与 MySQL 函数 FIND_IN_SET 相同用法，即根据逗号分隔的筛选条件进行查询：

<!-- 查询姓名包含在逗号分隔的筛选条件中的所有用户 -->
SELECT
	t1.USER_ID,
	t1.USER_NAME
FROM
	SYS_USER t1
WHERE
	INSTR(',' || #{query.Names} || ',', ',' || t1.USER_NAME || ',') > 0

但是 INSTR 会导致索引失效，查询效率较低，建议转换成 IN 查询。

5. REGEXP_SUBSTR 正则截取字符串

REGEXP_SUBSTR(源字符串, 正则表达式 [, 开始位置 [, 匹配序号 [, 匹配模式 ] ] ] )

例如：REGEXP_SUBSTR(‘string’, ‘[^,]+’, 1, 1, ‘c’)。源字符串为 ‘string’，正则表达式为 ‘[^,]+’，开始搜索的位置为 1（可省略，默认 1），返回第一个匹配项（可省略，默认 1），‘c’ 表示区分大小写（可省略，默认 ‘c’。‘i’ 表示不区分大小写，‘c’ 表示区分大小写，‘n’ 表示不匹配换行符，‘m’ 表示多行模式等。）。

SELECT
	t1.USER_ID,
	t1.USER_NAME
FROM
	SYS_USER t1
WHERE
	t1.USER_NAME IN (
		SELECT
			REGEXP_SUBSTR(#{query.Names}, '[^,]+', 1, LEVEL)
		FROM
			DUAL
		CONNECT BY
			REGEXP_SUBSTR(#{query.Names}, '[^,]+', 1, LEVEL) IS NOT NULL
    )

六、日期处理

1. TO_DATE 日期转换

其他

LISTAGG 多行拼接

LISTAGG( 待处理列, 连接符号 ) WITHIN GROUP(ORDER BY 拼接排序依据字段)
col：待处理列。
连接符号
拼接排序依据字段。

ROUND 四舍五入

ROUND(number，num_digits)

number 需要进行四舍五入的数字。
num_digits 指定需要四舍五入的小数位数。

LISTAGG

REGEXP_SUBSTR

REGEXP_REPLACE

CONNECT BY

OVER

TRUNC