从简易的json中提取数据

前言

某字段将业务数据以json格式进行了存储，且格式为简单的{k:v,k:v,…}，现在需要用存储过程从该字段中取部分值，于是有了下面的代码。
参数1代表json字符，参数2代表需要提取值的key,结果返回value

---

	FUNCTION TRANS_JSON(JSON IN VARCHAR2, P_KEY IN VARCHAR2) RETURN VARCHAR2 IS
		JSON_STR VARCHAR2(1024);
		TYPE TYPE_SPLIT IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;
		KEY_VALUE TYPE_SPLIT;
		ITEM      NUMBER;
		GROUP_NUM NUMBER;
		GROUP_STR VARCHAR2(50);
		KEY       VARCHAR2(50);
	BEGIN
		IF JSON IS NULL THEN
			RETURN NULL;
		END IF;
		JSON_STR  := TRANSLATE(JSON, '1"{}  ', '1'); --JSON字符串处理
		GROUP_NUM := LENGTH(JSON_STR) - LENGTH(REPLACE(JSON_STR, ',', '')) + 1; --数组长度
		--生成数组
		FOR I IN 1 .. GROUP_NUM LOOP
			ITEM := INSTR(JSON_STR || ',', ',', 1, 1);
			KEY_VALUE(I) := REPLACE(SUBSTR(JSON_STR, 0, ITEM), ',', '');
			JSON_STR := SUBSTR(JSON_STR, ITEM + 1, LENGTH(JSON_STR));
		END LOOP;
		--匹配数组
		FOR I IN 1 .. GROUP_NUM LOOP
			GROUP_STR := KEY_VALUE(I);
			ITEM      := INSTR(GROUP_STR, ':', 1, 1);
			KEY       := REPLACE(SUBSTR(GROUP_STR, 0, ITEM - 1), CHR(10), '');
			IF TRIM(KEY) = TRIM(P_KEY) THEN
				RETURN TRIM(SUBSTR(GROUP_STR, ITEM + 1, LENGTH(GROUP_STR)));
			END IF;
		END LOOP;
		RETURN NULL;
	EXCEPTION
		WHEN OTHERS THEN
			RETURN NULL;
	END;