PL/SQL中不能直接调用JSON_VALUE或JSON_TABLE，必须用SELECT...INTO FROM DUAL；解析数组需用JSON_TABLE配合NESTED PATH或双层嵌套；路径为相对路径，须注意空值、类型匹配及引号处理。
PL/SQL里不能直接调用JSON_VALUE或JSON_TABLE
你在存储过程中写 l_name := JSON_VALUE(l_json, '$.name') 会立刻报错 PLS-00222: no function with name 'JSON_VALUE' in scope。因为这些是 SQL 引擎函数，不是 PL/SQL 内置函数。必须用 SELECT ... INTO 包裹执行。
常见错误写法：
DECLARE
l_json CLOB := '{"name":"Alice","items":[{"id":1},{"id":2}]}';
l_name VARCHAR2(50);
BEGIN
l_name := JSON_VALUE(l_json, '$.name'); -- ❌ 编译失败
END;
正确做法是强制走 SQL 引擎：
DECLARE
l_json CLOB := '{"name":"Alice","items":[{"id":1},{"id":2}]}';
l_name VARCHAR2(50);
BEGIN
SELECT JSON_VALUE(l_json, '$.name') INTO l_name FROM DUAL; -- ✅ 必须带 FROM DUAL
END;
- 单字段提取：用
SELECT JSON_VALUE(...) INTO最简洁 - 多字段提取：建议一次查出，避免多次 SQL 解析开销（
SELECT JSON_VALUE(...), JSON_VALUE(...) INTO ... FROM DUAL） - 别在循环里反复执行
SELECT ... FROM DUAL—— 每次都触发 SQL 解析，性能明显下降
解析数组必须用JSON_TABLE，且路径是相对的
想把 [{"id":1,"tags":["a","b"]}, {"id":2,"tags":["c"]}] 展开成三行（id=1+tag=a、id=1+tag=b、id=2+tag=c），只靠一层 JSON_TABLE 不行——它默认按数组元素“行”展开，但不会递归展开子数组。
Oracle 12.2+ 推荐用 NESTED PATH：
SELECT jt.id, jt.tag
FROM JSON_TABLE(
l_json,
'$[*]' COLUMNS (
id NUMBER PATH '$.id',
NESTED PATH '$.tags[*]' COLUMNS (tag VARCHAR2(10) PATH '$')
)
) jt;
Oracle 12.1 只能双层嵌套：
SELECT jt1.id, jt2.tag
FROM JSON_TABLE(l_json, '$[*]' COLUMNS (id NUMBER PATH '$.id', tags CLOB PATH '$.tags')) jt1,
JSON_TABLE(jt1.tags, '$[*]' COLUMNS (tag VARCHAR2(10) PATH '$')) jt2;
-
COLUMNS中所有PATH都是相对于上一级节点的，不是从根写起。比如NESTED PATH '$.tags[*]'的$指的是当前数组元素，不是原始 JSON 根 - 空数组或
"tags": null默认整行丢弃；加NULL ON EMPTY才保留 id 行、tag 为 NULL - 如果路径写错（如多写一个
$或漏掉[*]），可能静默返回 NULL 或报ORA-40442，不易排查
类型定义不匹配会导致截断或报错，不是总报错
JSON_TABLE 的 COLUMNS 子句对类型极其敏感。例如 JSON 里有 "price": 99.95：
- 定义为
price NUMBER(3)→ 直接报ORA-40473: invalid data type for JSON path - 定义为
price VARCHAR2(5)→ 不报错，但可能截成"99.9"（隐式转换 + 长度限制） - 定义为
price VARCHAR2(10)→ 安全，得"99.95"
再比如字段值是 "2023-10-26T14:30:00Z"，定义为 dt DATE PATH '$' 会失败；得用 TO_DATE(JSON_VALUE(...), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') 或先转字符串再显式转换。
关键点：
- 数值型字段优先用
NUMBER，但务必确认精度和小数位足够 - 字符串字段长度宁大勿小，尤其含中文时（UTF-8 下一个汉字占 3 字节）
- 日期时间尽量先用
VARCHAR2提取，再用TO_DATE/TO_TIMESTAMP转换，可控性强
空值、null 和缺失字段容易被忽略
JSON 中 "tags": null、"tags": []、甚至整个 "tags" 键都不存在，在 JSON_TABLE 中行为完全不同：
- 默认情况下，任意一级路径无匹配就整行丢弃（即该数组元素不产出结果行）
- 加
NULL ON ERROR或NULL ON EMPTY才让对应列返回 NULL -
ERROR ON ERROR会让整条 SQL 报错，不适合生产环境 - 对可选字段，推荐统一加
NULL ON ERROR NULL ON EMPTY
示例：
COLUMNS ( id NUMBER PATH '$.id', tag VARCHAR2(10) PATH '$.tags[0]' NULL ON ERROR NULL ON EMPTY )
这样即使 tags 不存在、为空或索引越界，tag 列仍为 NULL，不会丢数据行。
最易被忽略的是：JSON 中的字符串值本身带双引号（如 "name": "\"Alice\""），JSON_VALUE 返回的是已去引号的值，而 JSON_QUERY 返回带引号的 JSON 字符串——选错函数会导致后续处理异常。
