JSON文件全解析

DECLARE
l_response CLOB := '{
"success": true,
"message": "query success",
"data": [
123,
"asd",
null,
{
"currency": "CNY"
},
{
"currency": "USD",
"sales_order": [
{
"order_num": "4221746211",
"order_line": "000010"
}
],
"items": [
{
"itemcode": "001"
},
{
"itemcode": "002"
}
]
}
]
}';

l_response_json json;

l_parse_count NUMBER := 0;
PROCEDURE parse_json(p_json json
,p_level VARCHAR2 DEFAULT '1') IS

l_json_value json_value;

l_key VARCHAR2(255);
l_value_type VARCHAR2(10);

l_json_list json_list;
l_json_temp json;
BEGIN
l_parse_count := l_parse_count + 1;
--dbms_output.put_line(p_json.json_data.count);
FOR i IN 1 .. p_json.json_data.count
LOOP

dbms_output.put_line(l_parse_count || '.' || p_level || '.' || i);

l_key := p_json.json_data(i).mapname;
dbms_output.put_line('key:' || l_key);

l_json_value := p_json.get(l_key);

l_value_type := l_json_value.get_type;
--dbms_output.put_line('value type:' || l_value_type);

IF l_value_type = 'bool' THEN
dbms_output.put_line('value:' || sys.diutil.bool_to_int(l_json_value.get_bool));
ELSIF l_value_type = 'number' THEN
dbms_output.put_line('value:' || l_json_value.get_number);
ELSIF l_value_type = 'string' THEN
dbms_output.put_line('value:' || l_json_value.get_string);
ELSIF l_value_type = 'array' THEN
--array to list
l_json_list := json_list(l_json_value);
dbms_output.put_line('list size: ' || l_json_list.count);

FOR m IN 1 .. l_json_list.count
LOOP
--dbms_output.put_line('list ' || m || ' key:' || l_json_list.list_data(m).mapname);
--dbms_output.put_line('list ' || m || ' value type:' || l_json_list.list_data(m).get_type);
dbms_output.put_line(p_level || '.' || i || '.' || m);

IF l_json_list.list_data(m).get_type = 'number' THEN
dbms_output.put_line('list ' || m || ' value:' || l_json_list.list_data(m).get_number);
ELSIF l_json_list.list_data(m).get_type = 'string' THEN
dbms_output.put_line('list ' || m || ' value:' || l_json_list.list_data(m).get_string);
ELSIF l_json_list.list_data(m).get_type = 'object' THEN
l_json_temp := json(l_json_list.list_data(m));
--解析成 JSON， 然后从头开始
parse_json(l_json_temp,
p_level || '.' || i || '.' || m);
END IF;

END LOOP;
ELSIF l_value_type = 'object' THEN
l_json_temp := json(l_json_value);
parse_json(l_json_temp);
END IF;
END LOOP;
END;
BEGIN
dbms_output.put_line('字符长度：' || dbms_lob.getlength(l_response));
l_response_json := json(l_response);

parse_json(l_response_json);
END;