如何使用PL/SQL提取CLOB中的before和after字段？

针对你提供的JSON格式CLOB数据，可通过Oracle内置的JSON函数快速提取before和after字段值，以下是两种实用方案：

方案1：提取完整数组字符串 #

如果需要直接获取before和after对应的整个数组内容，使用JSON_VALUE函数：

SELECT
  JSON_VALUE(json_clob_column, '$.sequence.before' RETURNING VARCHAR2(4000)) AS before_sequence,
  JSON_VALUE(json_clob_column, '$.sequence.after' RETURNING VARCHAR2(4000)) AS after_sequence
FROM your_table -- 替换为你的表名
WHERE id = 1; -- 替换为你的筛选条件

说明：若数组长度超过4000字符，可将RETURNING VARCHAR2(4000)改为RETURNING CLOB。

方案2：拆分数组为单行元素 #

如果需要将数组中的每个值单独提取为一行记录，使用JSON_TABLE函数：

SELECT
  'before' AS sequence_type,
  value AS sequence_value
FROM your_table,
     JSON_TABLE(json_clob_column, '$.sequence.before[*]' COLUMNS value VARCHAR2(20) PATH '$')
WHERE id = 1 -- 替换为你的筛选条件
UNION ALL
SELECT
  'after' AS sequence_type,
  value AS sequence_value
FROM your_table,
     JSON_TABLE(json_clob_column, '$.sequence.after[*]' COLUMNS value VARCHAR2(20) PATH '$')
WHERE id = 1; -- 替换为你的筛选条件

注意：以上方法要求Oracle数据库版本为12c及以上，该版本开始原生支持JSON处理函数。

内容的提问来源于stack exchange，提问作者Stefano Sguotti