-- 1.创建一个type,返回table类型
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2(4000);
-- 2.创建一个分割的函数,主要对json进行按照固定割串分割
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN ty_str_split
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
str_split ty_str_split := ty_str_split ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);
WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);
IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
str_split.EXTEND;
str_split (str_split.COUNT) := str;
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
str_split.EXTEND;
str_split (str_split.COUNT) := str;
END IF;
END LOOP;
RETURN str_split;
END fn_split;
3.创建一个函数,解析json对象{}
-- 3.创建一个函数,解析json对象{}
CREATE OR REPLACE FUNCTION parsejson(p_jsonstr varchar2, p_key varchar2)
RETURN VARCHAR2 IS
rtnVal VARCHAR2(4000);
i NUMBER(20);
jsonkey VARCHAR2(4000);
jsonvalue VARCHAR2(4000);
json VARCHAR2(4000);
BEGIN
IF p_jsonstr IS NOT NULL THEN
json := REPLACE(p_jsonstr, '{', '');
json := REPLACE(json, '}', '');
json := replace(json, '"', '');
FOR temprow IN (SELECT * FROM TABLE(fn_split(json, ','))) LOOP
IF temprow.column_value IS NOT NULL THEN
i := 0;
jsonkey := '';
jsonvalue := '';
FOR tem2 IN (SELECT *
FROM TABLE(fn_split(temprow.column_value, ':'))) LOOP
IF i = 0 THEN
jsonkey := tem2.column_value;
END IF;
IF i = 1 THEN
jsonvalue := tem2.column_value;
END IF;
i := i + 1;
END LOOP;
IF (jsonkey = p_key) THEN
rtnVal := jsonvalue;
END if;
END IF;
END LOOP;
END IF;
RETURN rtnVal;
END parsejson;
json示例:
/* DATA = {"thir":null,"loa":"","cName":888,"contacts":[{"name": "John", "age": 30},{"name": "John1", "age": 30}]}*/
SELECT parsejson (DATA,'cName') name FROM JSON_DATA_1;
4.解析JSON数组[{},{}]
-- 4.解析JSON数组[{},{}]
create function parsearray(p_jsonstr varchar2, p_key varchar2,p_keys varchar2)
return varchar2 is
rtnval varchar2(4000);
i number(20);
jsonkey varchar2(4000);
jsonvalue varchar2(4000);
json varchar2(4000);
json2 varchar2(4000);
begin
if p_jsonstr is not null then
json := replace(p_jsonstr, '[', '');
json := replace(json, ']', '');
json := replace(json, p_keys, '');
for temprow in (select * from table(fn_split(json, ','))) loop
if temprow.column_value is not null then
json2 := replace(temprow.column_value, '{', '');
json2 := replace(json2, '}', '');
json2 := replace(json2, '"', '');
json2 := replace(json2, p_keys, '');
for temprow2 in (select * from table(fn_split(json2, ','))) loop
i:=0;
jsonkey:= '';
jsonvalue:= '';
for tmpe2 in (select * from table(fn_split(temprow2.column_value, ':'))) loop
if i = 0 then
jsonkey := tmpe2.column_value;
end if;
if i = 1 then
jsonvalue := tmpe2.column_value;
end if;
i := i + 1;
end loop;
if (jsonkey = p_key) then
if rtnval is not null then
rtnval :=rtnval||','||jsonvalue;
else
rtnval :=jsonvalue;
end if;
end if;
end loop;
end if;
end loop;
end if;
return rtnval;
end parsearray;
/
示例:
/**
{"list":[{"name": "John", "age": 30},{"name": "John1", "age": 30}]}
*/
-- p_jsonstr 为数据, p_key 为需要的变量,p_keys 为list名称 (此处是未了替换为空)
SELECT parsearray(
'{"list":[{"name": "John", "age": 30},{"name": "John1", "age": 30}]}',
'name',
'"list":') as extend1
FROM JSON_DATA_1 l;
- 字符串转list
create or replace type split_table is table of varchar2 (4000);
/**
字符串转list
*/
create or replace function splitstr(p_string in varchar2,
p_delimiter in varchar2 := ',')
return split_table
pipelined as
v_length number := length(p_string);
v_start number := 1;
v_index number;
begin
while (v_start <= v_length) loop
v_index := instr(p_string, p_delimiter, v_start);
if v_index = 0 then
pipe row(substr(p_string, v_start));
v_start := v_length + 1;
else
pipe row(substr(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
end if;
end loop;
return;
end splitstr;
示例:
SELECT * FROM TABLE(SPLITSTR('1,2,3', ','));
SELECT *
FROM TABLE (SPLITSTR((SELECT parsearray(
'[{"name": "John", "age": 30},{"name": "John1", "age": 30}]',
'name') as extend1
FROM JSON_DATA_1 l), ','));