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