Oracle SQL 模式匹配

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

Oracle SQL 模式匹配[1]：

方式：

正则表达式
LIKE / REGEXP_LIKE
MATCH_RECOGNIZE（12c+）
简单模式

2. LIKE

2.1 基本

SELECT * FROM employees WHERE name LIKE 'Smith%';
SELECT * FROM employees WHERE name LIKE '%Smith';
SELECT * FROM employees WHERE name LIKE '%Smith%';
SELECT * FROM employees WHERE name LIKE '_mith';

2.2 转义

SELECT * FROM t WHERE col LIKE '100\%' ESCAPE '\';

3. 正则表达式

3.1 REGEXP_LIKE

SELECT * FROM employees WHERE REGEXP_LIKE(name, '^A.*');
SELECT * FROM employees WHERE REGEXP_LIKE(name, '^[A-Z][a-z]+$');
SELECT * FROM employees WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

3.2 REGEXP_REPLACE

-- 简单替换
SELECT REGEXP_REPLACE('Hello World', 'o', '0') FROM dual;

-- 分组
SELECT REGEXP_REPLACE(
  '2026-07-21', 
  '([0-9]{4})-([0-9]{2})-([0-9]{2})', 
  '\3/\2/\1'
) FROM dual;
-- 21/07/2026

-- 位置
SELECT REGEXP_REPLACE(
  'hello world', 
  'o', 
  '0', 
  1,  -- 起始位置
  1,  -- 第几个
  'i' -- 不区分大小写
) FROM dual;

3.3 REGEXP_SUBSTR

-- 提取
SELECT REGEXP_SUBSTR('a,b,c', '[^,]+', 1, 2) FROM dual;
-- b

-- 分组
SELECT REGEXP_SUBSTR(
  '2026-07-21',
  '([0-9]{4})-([0-9]{2})-([0-9]{2})',
  1, 1, 'i', 1  -- 第 1 组
) FROM dual;
-- 2026

3.4 REGEXP_INSTR

SELECT REGEXP_INSTR('abc123', '[0-9]') FROM dual;
-- 4

SELECT REGEXP_INSTR('abc123def', '[0-9]+', 1, 1, 0, 'i') FROM dual;
-- 4

-- 返回结束位置
SELECT REGEXP_INSTR('abc123def', '[0-9]+', 1, 1, 1, 'i') FROM dual;
-- 7

3.5 REGEXP_COUNT

SELECT REGEXP_COUNT('a,b,c,d', ',') FROM dual;
-- 3

4. 正则元字符

4.1 字符类

.     任意字符
\d    数字 [0-9]
\D    非数字
\w    字母数字下划线 [A-Za-z0-9_]
\W    非 \w
\s    空白
\S    非空白

4.2 量词

*     0 或多
+     1 或多
?     0 或 1
{n}   n
{n,}  n 或多
{n,m} n 到 m

4.3 锚点

^     行首
$     行尾
\b    单词边界
\B    非单词边界

4.4 分组

(...)     分组
(?:...)   非捕获
(?=...)   前瞻
(?!...)   负前瞻

5. 匹配模式

i  不区分大小写
c  区分大小写
n  . 匹配换行
m  多行模式
x  忽略空白

SELECT REGEXP_LIKE('Hello', 'hello', 'i') FROM dual;  -- TRUE

6. MATCH_RECOGNIZE（12c+）

6.1 概述

复杂模式
时序数据
股票分析

6.2 语法

SELECT *
FROM sales_history
MATCH_RECOGNIZE (
  PARTITION BY product_id
  ORDER BY sale_date
  MEASURES 
    STRT.sale_date AS start_date,
    LAST(sale_date) AS end_date,
    COUNT(*) AS days
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO LAST UP
  PATTERN (STRT UP+)
  DEFINE
    UP AS UP.amount > PREV(UP.amount)
);

6.3 V 形

SELECT *
FROM stocks
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY trade_date
  MEASURES
    STRT.trade_date AS start_date,
    BOTTOM.trade_date AS bottom_date,
    LAST(trade_date) AS end_date
  PATTERN (STRT DOWN+ BOTTOM UP+)
  DEFINE
    DOWN AS DOWN.price < PREV(DOWN.price),
    BOTTOM AS BOTTOM.price < PREV(BOTTOM.price) AND BOTTOM.price < NEXT(BOTTOM.price),
    UP AS UP.price > PREV(UP.price)
);

6.4 双顶

SELECT *
FROM stocks
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY trade_date
  PATTERN (PEAK1 DOWN+ UP+ PEAK2)
  DEFINE
    PEAK1 AS PEAK1.price > PREV(PEAK1.price) AND PEAK1.price > NEXT(PEAK1.price),
    PEAK2 AS PEAK2.price > PREV(PEAK2.price) AND PEAK2.price > NEXT(PEAK2.price)
);

7. ONE ROW vs ALL ROWS

7.1 ONE ROW PER MATCH

- 每匹配一行
- 汇总

7.2 ALL ROWS PER MATCH

- 所有匹配行
- 详细

SELECT *
FROM sales
MATCH_RECOGNIZE (
  PARTITION BY product_id
  ORDER BY sale_date
  ALL ROWS PER MATCH
  PATTERN (UP+)
  DEFINE UP AS UP.amount > PREV(UP.amount)
);

8. AFTER MATCH SKIP

8.1 选项

- SKIP TO NEXT ROW
- SKIP PAST LAST ROW
- SKIP TO FIRST var
- SKIP TO LAST var
- SKIP TO var

9. 函数

9.1 跨行

PREV(expr, n)   前 n 行
NEXT(expr, n)   后 n 行
FIRST(expr)     第一行
LAST(expr)      最后一行

9.2 聚合

SUM(expr), AVG(expr), COUNT(*), MAX(expr), MIN(expr)

10. 应用场景

10.1 趋势

- 上升
- 下降
- V 形
- W 形

10.2 异常

- 突变
- 间断

10.3 序列

- 连续 N 天
- 累计

11. 性能

11.1 索引

ORDER BY 列索引
PARTITION BY 列索引

11.2 执行计划

EXPLAIN PLAN FOR SELECT ... MATCH_RECOGNIZE ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));

12. 常见坑与排错

12.1 正则回溯

- 复杂正则慢
- 优化

12.2 MATCH_RECOGNIZE 内存

- 大数据内存
- PARTITION BY 控制

12.3 大小写

- i 模式
- c 模式

13. 最佳实践

REGEXP 替代 LIKE：复杂
REGEXP_SUBSTR：提取
REGEXP_REPLACE：转换
MATCH_RECOGNIZE：时序
索引 ORDER BY：性能
测试正则：正确
避免复杂回溯：性能
PARTITION BY：分组
ONE vs ALL ROWS：选择
文档化：模式

14. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Pattern Matching” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/pattern-matching-conditions.html