企业官网建设流程全解析

简介：这份PDF文档聚焦Oracle数据库中截取JSON字符串内容的实用方法，面向需要处理JSON数据的数据库开发人员与运维工程师。内容围绕自定义函数parsejsonstr展开，详细讲解如何通过p_jsonstr、startkey、endkey三个参数从JSON字符串中提取指定键值对，并针对endkey是否为右花括号分别说明截取逻辑，配合完整SQL示例演示从INFO字段中提取AGE信息的过程。资源包共1个PDF文件，大小约32KB，篇幅精炼，便于快速查阅与复用。目前已有5259人学习下载，说明该方案在实际开发中具有较高的参考价值。读者可从中掌握自定义JSON截取函数的编写思路与调用方式，理解Oracle内置JSON_VALUE、JSON_QUERY等函数与自定义方案的适用边界，为处理复杂JSON结构提供可落地的技术参考。

1. 为什么老系统里还在用 parsejsonstr 截 JSON

很多 Oracle 老库（10g、11g 那批）根本没有JSON_VALUE、JSON_QUERY这些原生函数，可业务表里偏偏塞了一列 CLOB 或 VARCHAR2，里面躺着一段 JSON。你要从里面抠出AGE、HEIGHT这种字段，又不想动应用层，最省事的办法就是写一个 PL/SQL 函数，用INSTR定位、用SUBSTR截取。parsejsonstr就是干这个的：传进去整段 JSON、起始 key、结束 key，它把中间那段值还给你。它适合谁？适合维护 Oracle EBS、老 ERP、接口日志表的一线人，适合临时取数、做数据核对、写报表 SQL 的场景。它不优雅，但能跑，而且不用改表结构、不用升级数据库版本。下面我把它拆开讲清楚，包括参数怎么设、边界在哪、什么时候会翻车。

2. parsejsonstr 函数拆解：INSTR 定位与 SUBSTR 截取的参数逻辑

2.1 函数签名与三个入参到底怎么传

先把原始代码摆出来，这是整篇的核心，后面所有讨论都围绕它。

CREATE OR REPLACE FUNCTION PLATFROM.parsejsonstr( p_jsonstr varchar2, startkey varchar2, endkey varchar2 ) RETURN VARCHAR2 IS rtnVal VARCHAR2(1000); FindIdxS NUMBER(2); FindIdxE NUMBER(2); BEGIN if endkey = '}' then rtnVal := substr( p_jsonstr, (instr(p_jsonstr, startkey) + length(startkey) + 2), (instr(p_jsonstr, endkey, instr(p_jsonstr, startkey)) - instr(p_jsonstr, startkey) - length(startkey) - 2) ); else rtnVal := substr( p_jsonstr, (instr(p_jsonstr, startkey) + length(startkey) + 2), (instr(p_jsonstr, endkey, instr(p_jsonstr, startkey)) - instr(p_jsonstr, startkey) - length(startkey) - 4) ); end if; RETURN rtnVal; END parsejsonstr; /

三个参数的含义必须掰开说。p_jsonstr是目标 JSON 字符串，注意它可以是列、可以是变量，但类型得能隐式转成 VARCHAR2，CLOB 直接传会报错，得先TO_CHAR或DBMS_LOB.SUBSTR转一道。startkey是你要截取的那个 key 名，比如AGE，注意这里传的是不带引号的裸 key，函数内部靠INSTR找它在字符串里的位置。endkey是目标 key 的下一个 key，用来确定截到哪停，比如HEIGHT。

关键在+2和-4这两个魔数。+2是因为 JSON 里 key 后面跟着":，冒号加引号正好两个字符，所以起始位置要跳过key本身长度再加 2。-4出现在 else 分支，是因为截取长度算到endkey起始位置后，还要减掉","这四个字符（逗号、引号、冒号、引号），才能把值干净地切出来。当endkey是}时，说明目标 key 是对象里最后一个字段，后面没有逗号，只有}，所以只减 2。

2.2 两个分支的差异：endkey 是}还是普通 key

这个 if/else 是整个函数最容易看漏的地方。很多人复制完代码直接跑，发现最后一个字段截出来多一个引号或者少一个字符，就是没理解分支条件。

当endkey = '}'，意味着你要截的 key 是 JSON 对象里最后一个成员，它后面直接跟}，没有逗号。此时截取长度公式是：

instr(p_jsonstr, '}') - instr(p_jsonstr, startkey) - length(startkey) - 2

减 2 是去掉":两个字符。而当endkey是普通 key（比如HEIGHT），目标值后面跟着","四个字符，所以减 4。

我一般会先确认目标 key 在 JSON 里的位置：如果它后面还有别的 key，就传那个 key 当endkey；如果它是最后一个，就传}。这一步判断错了，截出来的值要么带尾巴，要么被砍掉一位。

2.3 一个可复现的调用例子

假设TTTT表里INFO列存了这样一段：

{"NAME":"张三","AGE":"28","HEIGHT":"175","CITY":"杭州"}

要取AGE，因为AGE后面还有HEIGHT，所以endkey传HEIGHT：

SELECT parsejsonstr(INFO, 'AGE', 'HEIGHT') AS AGE_VAL FROM TTTT;

返回结果是28。如果要取CITY，它是最后一个字段，endkey传}：

SELECT parsejsonstr(INFO, 'CITY', '}') AS CITY_VAL FROM TTTT;

返回杭州。注意这里假设值本身不带引号嵌套，如果值是"28"这种带引号的字符串，截出来会连引号一起带出来，需要自己再REPLACE或TRIM处理。这是这个函数的一个天然边界，后面避坑章节会细说。

2.4 为什么不用 Oracle 原生 JSON 函数

有人会问，现在 Oracle 12c 以上都有JSON_VALUE了，为什么还用这个土办法。原因很现实：一是老库版本不够，12c 之前的库根本没有 JSON 函数；二是有些列存的是「伪 JSON」，格式不规范，原生函数直接报 ORA-40441 之类的错，反而这种字符串截取能硬扛过去；三是临时取数场景，写个SELECT parsejsonstr(...)比构造JSON_TABLE快得多。但边界也清楚：它只适合结构简单、key 不重复、值里不含嵌套对象的 JSON。复杂结构还是得上原生函数或应用层解析。

3. 把函数用进 SQL 与批量取数：从单条到整表的落地写法

3.1 建函数与权限：别在错误的 schema 下创建

原始代码里函数名带了PLATFROM.前缀，说明它建在PLATFROM这个 schema 下。如果你直接在自己的 schema 里执行，要么去掉前缀，要么先确认有CREATE PROCEDURE权限。常见做法是：

-- 切换到目标 schema 或加上 schema 前缀 CREATE OR REPLACE FUNCTION PLATFROM.parsejsonstr( p_jsonstr varchar2, startkey varchar2, endkey varchar2 ) RETURN VARCHAR2 IS rtnVal VARCHAR2(1000); BEGIN -- 函数体同上，此处省略 RETURN rtnVal; END; /

建完之后要授权，否则别的用户查不了：

GRANT EXECUTE ON PLATFROM.parsejsonstr TO YOUR_USER;

这里有个坑：rtnVal声明的是VARCHAR2(1000)，如果截出来的值超过 1000 字符，会直接报 ORA-06502 值错误。JSON 里如果有个长文本字段，这个函数就废了。我一般会把它改成VARCHAR2(4000)或者干脆返回 CLOB，但改返回类型会影响调用方，得评估。

3.2 在 SELECT 里批量提取字段

单条取数只是验证，真正干活是整表批量提。假设TTTT表有ID和INFO两列，要一次性把AGE和HEIGHT都拉出来：

SELECT ID, parsejsonstr(INFO, 'AGE', 'HEIGHT') AS AGE_VAL, parsejsonstr(INFO, 'HEIGHT', 'CITY') AS HEIGHT_VAL, parsejsonstr(INFO, 'CITY', '}') AS CITY_VAL FROM TTTT WHERE INFO IS NOT NULL;

这里每个字段都要单独调一次函数，意味着同一段 JSON 被INSTR扫了多遍。数据量小无所谓，几十万行以上就会明显变慢。优化思路是先用一次SUBSTR把目标对象整段切出来存到临时表或 WITH 子句，再在子集上反复截取。但多数取数场景数据量不大，直接这么写够用。

3.3 处理 key 重复与大小写问题

INSTR是大小写敏感的，JSON 里写的是"age"，你传AGE，永远找不到，返回 NULL。这是最常见的「函数没报错但取不到值」的原因。解决办法有两个：一是传参时严格匹配 JSON 里的 key 大小写；二是改函数内部，把INSTR换成INSTR(UPPER(p_jsonstr), UPPER(startkey))，但这样位置会偏移，因为UPPER不改变长度，位置还对得上，可以这么改。

另一个问题是 key 重复。如果 JSON 里有两个AGE，INSTR只找第一个，截出来的是第一段。这种脏数据在老系统里不少见，函数本身没法区分，只能靠数据清洗或换用能解析完整 JSON 的方案。

3.4 用 WITH 子句做中间结果，减少重复扫描

如果一条 SQL 里要取五六个字段，可以把 JSON 先物化一次：

WITH src AS ( SELECT ID, INFO FROM TTTT WHERE INFO IS NOT NULL ) SELECT ID, parsejsonstr(INFO, 'NAME', 'AGE') AS NAME_VAL, parsejsonstr(INFO, 'AGE', 'HEIGHT') AS AGE_VAL, parsejsonstr(INFO, 'HEIGHT', 'CITY') AS HEIGHT_VAL FROM src;

WITH子句在 Oracle 里不一定物化，但至少让 SQL 结构清晰，方便后续加过滤条件。真正要提速，还是得把 JSON 拆成关系表存起来，那是另一个话题了。

4. 避坑与排查：parsejsonstr 最容易翻车的五个场景

4.1 取不到值，返回 NULL

现象：SQL 不报错，但结果列全是空。原因：startkey大小写和 JSON 里不一致，或者 JSON 里 key 带了空格、换行。解决：先用SELECT INFO FROM TTTT WHERE ID = xxx把原始 JSON 打出来，肉眼确认 key 的准确拼写，包括引号位置。如果 JSON 是格式化过的（带换行缩进），INSTR找 key 没问题，但+2的偏移会因为换行符而错位，截出来带一堆空白。这种情况建议先REPLACE(INFO, CHR(10), '')去掉换行再传。

4.2 截出来的值多一个引号或逗号

现象：结果是"28"而不是28，或者28,带个逗号。原因：endkey传错，或者值本身在 JSON 里就是带引号的字符串。解决：确认endkey是目标 key 的下一个 key，不是目标 key 自己。如果值本身带引号，外面套一层REPLACE(parsejsonstr(...), '"', '')去掉。但要注意，如果值内部本来就有引号（比如文本里含"），REPLACE会误伤，得用TRIM(BOTH '"' FROM ...)只去首尾。

4.3 ORA-06502 值错误

现象：函数执行报 ORA-06502: PL/SQL: numeric or value error。原因：截出来的值超过VARCHAR2(1000)上限。解决：把rtnVal改成VARCHAR2(4000)，或者返回 CLOB。改完记得重新编译函数，并检查调用方有没有对返回值长度做假设。

4.4 JSON 里有嵌套对象，截取结果错乱

现象：目标 key 的值本身是个{...}对象，截出来只有一半。原因：INSTR找endkey时，如果嵌套对象里也有同名的 key，会定位到内层去。解决：这种结构parsejsonstr处理不了，别硬扛。要么用 Oracle 12c 以上的JSON_QUERY，要么在应用层用 Python、Java 解析。我一般遇到嵌套超过一层的 JSON，直接放弃 SQL 截取，走 ETL 抽到中间表再处理。

4.5 性能问题：全表扫描加多次 INSTR

现象：几十万行的表，取三个字段跑了十几分钟。原因：每行每字段都做多次INSTR，且INFO列如果是 CLOB，INSTR在 LOB 上效率更低。解决：先加WHERE条件缩小范围，比如按日期分区过滤；或者把 JSON 解析结果落到临时表，用INSERT INTO ... SELECT一次性算完，后续查询走临时表。如果INFO是 CLOB，考虑加函数索引或全文索引，但函数索引对parsejsonstr这种自定义函数支持有限，得实测。

5. 进阶：把 parsejsonstr 改造成更稳的版本

原始函数能跑，但边界太脆。我在生产里一般会做三处加固，这里把改法写出来，你可以直接抄。

第一处，处理大小写和换行：

CREATE OR REPLACE FUNCTION PLATFROM.parsejsonstr_v2( p_jsonstr varchar2, startkey varchar2, endkey varchar2 ) RETURN VARCHAR2 IS v_json VARCHAR2(32767); v_start NUMBER; v_end NUMBER; rtnVal VARCHAR2(4000); BEGIN -- 去掉换行和回车，统一大写便于定位 v_json := UPPER(REPLACE(REPLACE(p_jsonstr, CHR(10), ''), CHR(13), '')); v_start := INSTR(v_json, UPPER(startkey)); IF v_start = 0 THEN RETURN NULL; -- key 不存在直接返回空，不报错 END IF; v_end := INSTR(v_json, UPPER(endkey), v_start); IF v_end = 0 THEN RETURN NULL; END IF; -- 统一按带引号值的格式截取，再去掉首尾引号 rtnVal := SUBSTR(v_json, v_start + LENGTH(startkey) + 2, v_end - v_start - LENGTH(startkey) - 4); RETURN TRIM(BOTH '"' FROM rtnVal); END; /

改动点说明：UPPER统一大小写，REPLACE去换行，v_start = 0和v_end = 0做空值保护，避免SUBSTR负数长度报错。TRIM(BOTH '"' FROM ...)去掉值首尾的引号，比REPLACE安全。返回类型放宽到 4000。

第二处，如果值可能是数字或布尔，截出来是字符串，需要转换时在调用层做：

SELECT ID, TO_NUMBER(parsejsonstr_v2(INFO, 'AGE', 'HEIGHT')) AS AGE_NUM FROM TTTT WHERE REGEXP_LIKE(parsejsonstr_v2(INFO, 'AGE', 'HEIGHT'), '^[0-9]+$');

加REGEXP_LIKE是为了防止空值或非数字导致TO_NUMBER报错，这是血泪经验，直接转经常翻车。

第三处，验证方法。改完函数别急着上生产，先造几条边界数据测：

跑完这五条，基本能确认函数在你的数据上稳不稳。从那以后我每次改这类字符串截取函数，都强制走一遍这个边界表，不再靠「看着差不多」上线。希望帮到你。

本文还有配套的精品资源，点击获取