oracle 11g plsql解析json数据示例

以下代码仅作plsql解析json格式数据示例：

 

CREATE OR REPLACE PROCEDURE test_proc(p1 VARCHAR2) AS

    v_json_varchar2 VARCHAR2(4000);
    injson          json;
    paramlist       json_list;
    onejson         json;

    --第一层  
    v_marketcode VARCHAR2(8);
    v_marketname VARCHAR2(64);
    v_address    VARCHAR2(64);
    v_tel        VARCHAR2(11);
    --第二层  
    v_name      VARCHAR2(64);
    v_fruitcode VARCHAR2(8);

BEGIN
    v_json_varchar2 := '{  
    "marketcode": "123456",  
    "marketname": "好吃的水果店",  
    "address": "一个好地方",  
    "tel": "12345678901",  
    "fruitlist": {  
        "name": "apple",  
        "fruitcode": "223344",  
        "applelist": [  
            {  
                "applename": "redapple ",  
                "applecode": "111000",  
                "price": "10"  
            },  
            {  
                "applename": "greenapple ",  
                "applecode": "111111",  
                "price": "12"  
            },  
            {  
                "applename": "yellowapple ",  
                "applecode": "111222",  
                "price": "8"  
            }  
        ]  
    }  
}';
    injson := json(v_json_varchar2);
    --获取第一层json值  
    v_marketcode := json_ext.get_string(injson, 'marketcode');
    v_marketname := json_ext.get_string(injson, 'marketname');
    v_address := json_ext.get_string(injson, 'address');
    v_tel := json_ext.get_string(injson, 'tel');
    --第二层  
    v_name := json_ext.get_string(injson, 'fruitlist.name');
    v_fruitcode := json_ext.get_string(injson, 'fruitlist.fruitcode');

    --接下来获取第三层，使用json_list来存放json列表  
    paramlist := json_list();
    onejson := json();
    paramlist := json_ext.get_json_list(injson, 'fruitlist.applelist');

    --使用循环返回每个json部分的值  
    FOR i IN 1 .. paramlist.count LOOP
        --读取每个品种具体信息
        onejson := json(paramlist.get_elem(i));
        dbms_output.put_line(json_ext.get_string(onejson, 'applename'));
        dbms_output.put_line(json_ext.get_string(onejson, 'applecode'));
        dbms_output.put_line(json_ext.get_string(onejson, 'price'));
    
    END LOOP;

END;