ORACLE解析JSON数据示例_已上线程序修改

1:json数据格式如下

{
    "Order": [{
        "orderType": "LSCK",
        "amount": "376.00",
        "orderId": "18090501706539",
        "orderCreateTime": "2018-09-05 10:21:49",
        "brandID": null,
        "customerCode": "01.01.0002.001",
        "extendProps": "{"purotton":{"khmc":"nutrilon官方旗舰店","ecProvince":"江西省","xfClientCode":"","activity":[],"ecCity":"上饶市","sellerMsg":"","orderStatus":"已完成","actualFromLoc":"03.01","intOrderId":"160023","ecCounty":"鄱阳县","ecAddress":"江西省 上饶市 鄱阳县 昌洲乡昌洲大桥。","extraAmountFlag":"N","payMsg":"","otherDiscountFee":"0.00","xfTotalCashRecAmount":"376.00","ecCustomerName":"程青青","xfSellingAmountActual":"376.00","xfPostDate":"2018-09-05 14:39:06","xfSpecificedType":"alipay","chargeNum":"yjr青青","xfSalesman":"5","comments":"0","xfCreateTime":"2018-09-05 10:21:49","outChannelFlag":"Y","ecCustomerPhone":"15968140210","intMemberId":"121406","pointFlag":"N","docActualAmount":"376.00","lylx":"淘宝","transactionDate":"2018-09-05 10:26:29","xfChangeAmount":0,"xfSellingAmount":"376.00","xfNetqty":"2","docPaymentMethod":"3","transactionType":"ORD","codFLag":"N","docAmount":"376.00","createTime":"2018-09-05 10:21:49","xfDeliveryFee":"0.00","currencyCode":"RMB"},"receiverInfo":{"zip":"000000","country":"中国","shippingCode":"yunda","shippingName":"韵达快运","payTime":"2018-09-05 10:26:29","city":"上饶市","idCard":"","mobile":"15968140210","remark":"","receiverAddress":"江西省 上饶市 鄱阳县 昌洲乡昌洲大桥。","shippingSn":"3900261353988","shippingFee":"0.00","province":"江西省","createTime":"2018-09-05 10:21:49","district":"鄱阳县","name":"程青青","tel":"","shippingTime":"2018-09-05 14:39:06","payCode":"alipay","payName":"支付宝","account":"yjr青青"},"isGive":0,"dealCode":"213651422037732931"}",
        "orderCode": "18090501706539",
        "remark": "",
        "actualQty": "2",
        "warehouseCode": "03.01",
        "channelCode": "000"
    }],
    "orderLine": [{
        "colorName": "通色",
        "amount": "376",
        "orderId": "213651422037732931",
        "itemCode": "01.01.02.99.01.05.0001",
        "discount": "1.0000",
        "purchasePrice": null,
        "styleName": "诺优能婴儿配方奶粉 900g（0-6月龄，1段）",
        "styleCode": "01.01.02.99.01.05.0001",
        "actualQty": 2,
        "skuProperty": null,
        "itemId": "01.01.02.99.01.05.0001",
        "itemName": "诺优能婴儿配方奶粉 900g（0-6月龄，1段）",
        "sizeName": "通码",
        "stdprice": "188.00",
        "extendProps": "{"purotton":{"intOrderLineId":"179513","shareShippingFee":"0.00","shareOtherDiscountFee":"0.00","shopPrice":"188.00","actualPrice":"188.00","qty":"2","goodsPrice":"188.00","discount":"1.0000","discountAmount":"0.00","standardPrice":"0.00","productNum":"01.01.02.99.01.05.0001"}}",
        "colorCode": "000",
        "sizeCode": "000",
        "retailPrice": "0.00"
    }]
}

　　

2：传入如下存储过程，进行解析，并写入相关表中（其中解析extendProps关键字，此关建字内容为json格式内容，需转化为json格式，进行解析相关字段）

CREATE OR REPLACE PROCEDURE oms_order_gen1(p_json_varchar IN CLOB,
                                           p_code         OUT NUMBER,
                                           p_message      OUT VARCHAR2) AS
    ----------------------------------------------------------
    --author:xuyang
    --date:20180718
    --description:
    --调用奇门[OMS订单明细]接口，如果传入的orderType为LSCK，则将对应的资料写入到中间表[OMS订单明细（正常零售） OMS_ORDER]；
    --如果orderType为LSTH，则将对应的资料写入到中间表[OMS订单明细（零售退货） OMS_RETORDER]

    --author:xuyang
    --date:20180906
    --description:客户需求变化:相关字段对应及取值变化
    ----------------------------------------------------------
    v_injson   json;
    v_bodylist json_list; --明细信息json列表
    v_onejson  json; --用于解析每一个明细数据

    --要插入的记录变量

    v_omsdocno oms_order.omsdocno%TYPE; --单据号
    v_billdate oms_order.billdate%TYPE; --单据日期
    v_doctype  oms_order.doctype%TYPE; --库存类型

    v_qty             oms_order.qty%TYPE; --数量
    v_productalias_no oms_order.productalias_no%TYPE;
    v_tot_amt_actual  oms_order.tot_amt_actual%TYPE;
    v_priceactual     oms_order.priceactual%TYPE;
    v_olddocno        oms_order.olddocno%TYPE;
    v_htprice         oms_order.htprice%TYPE;
    v_code            oms_order.code%TYPE;
    v_storecode       oms_order.storecode%TYPE;
    v_count           NUMBER(10);

    v_xftillid oms_order.omsdocno%TYPE;
    v_remark   oms_order.remark%TYPE;
    v_isgive   oms_order.isgive%TYPE;

    v_string VARCHAR2(4000);
    v_exdpos json;

    v_transactiondate oms_retorder.billdate%TYPE;

    v_injson_orderlist json_list;
    v_injson_order     json;

BEGIN
    --将接口信息转化为json格式
    v_injson := json(p_json_varchar);
    --begin modified by xy 20180906
    v_injson_orderlist := json_ext.get_json_list(v_injson, 'Order');
    v_injson_order := json(v_injson_orderlist.get_elem(1));

    --获取要插入头表的字段信息
    v_doctype := json_ext.get_string(v_injson_order, 'orderType');
    v_olddocno := json_ext.get_string(v_injson_order, 'orderId');

    v_storecode := json_ext.get_string(v_injson_order, 'warehouseCode');
    v_remark := json_ext.get_string(v_injson_order, 'remark');
    v_code := json_ext.get_string(v_injson_order, 'customerCode');

    --begin added by xy 20180905
    v_string := json_ext.get_string(v_injson_order, 'extendProps');
    v_exdpos := json(v_string);

    v_billdate := to_number(REPLACE(substr(json_ext.get_string(v_exdpos,
                                                               'purotton.shippingTime'),
                                           1, 10), '-', ''));
    v_omsdocno := json_ext.get_string(v_exdpos, 'purotton.dealCode');
    v_isgive := json_ext.get_string(v_exdpos, 'purotton.isGive');

    --retorder columns  
    v_transactiondate := to_number(REPLACE(substr(json_ext.get_string(v_exdpos,
                                                                      'purotton.transactionDate'),
                                                  1, 10), '-', ''));
    v_xftillid := json_ext.get_string(v_exdpos, 'purotton.xfTillid');
    --end added by xy 20180905

    IF v_doctype = 'LSCK' THEN
        --订单号已存在,不允许
        BEGIN
            SELECT COUNT(a.id)
            INTO v_count
            FROM oms_order a
            WHERE a.olddocno = v_olddocno;
        EXCEPTION
            WHEN no_data_found THEN
                v_count := 0;
        END;
        IF v_count >= 1 THEN
            p_code := 0;
            p_message := 'failed,Omsorder existed in system';
            RETURN;
        END IF;
    
        --获取数据，插入到表oms_order中
        v_bodylist := json_list();
        v_onejson := json();
    
        --获取orderLine
        v_bodylist := json_ext.get_json_list(v_injson, 'orderLine');
    
        --循环获取明细数据，并插入数据库中
        FOR idx IN 1 .. v_bodylist.COUNT LOOP
            --读取每个明细信息
            v_onejson := json(v_bodylist.get_elem(idx));
            v_productalias_no := json_ext.get_string(v_onejson, 'itemCode');
            v_qty := to_number(json_ext.get_string(v_onejson, 'actualQty'));
            v_htprice := to_number(json_ext.get_string(v_onejson, 'retailPrice'));
        
            v_priceactual := to_number(json_ext.get_string(v_onejson, 'stdprice'));
            v_tot_amt_actual := to_number(json_ext.get_string(v_onejson,
                                                              'amount'));
        
            --插入表
            INSERT INTO oms_order
                (id, ad_client_id, ad_org_id, billdate, omsdocno, doctype,
                 olddocno, productalias_no, qty, htprice, priceactual,
                 tot_amt_actual, recivedate, recivestatus, errmsg, ownerid,
                 creationdate, modifierid, modifieddate, isactive, code,
                 storecode, remark, isgive)
            VALUES
                (get_sequences(upper('oms_order')), 37, 27, v_billdate,
                 v_omsdocno, v_doctype, v_olddocno, v_productalias_no, v_qty,
                 v_htprice, v_priceactual, v_tot_amt_actual, SYSDATE, 80, NULL,
                 893, SYSDATE, 893, SYSDATE, 'Y', v_code, v_storecode, v_remark,
                 v_isgive);
        
        END LOOP;
    
    ELSIF v_doctype = 'LSTH' THEN
        --订单号已存在,不允许
        BEGIN
            SELECT COUNT(a.id)
            INTO v_count
            FROM oms_retorder a
            WHERE a.olddocno = v_olddocno;
        EXCEPTION
            WHEN no_data_found THEN
                v_count := 0;
        END;
        IF v_count >= 1 THEN
            p_code := 0;
            p_message := 'failed,Retorder existed in system';
            RETURN;
        END IF;
    
        --获取数据，插入到表oms_retorder中
        v_bodylist := json_list();
        v_onejson := json();
    
        --获取body:orderBody
        v_bodylist := json_ext.get_json_list(v_injson, 'orderLine');
    
        --循环获取明细数据，并插入数据库中
        FOR idx IN 1 .. v_bodylist.COUNT LOOP
            --读取每个明细信息
            v_onejson := json(v_bodylist.get_elem(idx));
            v_productalias_no := json_ext.get_string(v_onejson, 'itemCode');
            v_qty := to_number(json_ext.get_string(v_onejson, 'actualQty'));
            v_htprice := to_number(json_ext.get_string(v_onejson, 'retailPrice'));
        
            v_priceactual := to_number(json_ext.get_string(v_onejson, 'stdprice'));
            v_tot_amt_actual := to_number(json_ext.get_string(v_onejson,
                                                              'amount'));
        
            --插入表
            INSERT INTO oms_retorder
                (id, ad_client_id, ad_org_id, billdate, omsdocno, doctype,
                 olddocno, productalias_no, qty, htprice, priceactual,
                 tot_amt_actual, recivedate, recivestatus, errmsg, ownerid,
                 creationdate, modifierid, modifieddate, isactive, origcode,
                 storecode, remark, isgive)
            VALUES
                (get_sequences(upper('oms_retorder')), 37, 27,
                 v_transactiondate, v_xftillid, v_doctype, v_olddocno,
                 v_productalias_no, v_qty, v_htprice, v_priceactual,
                 v_tot_amt_actual, SYSDATE, 80, NULL, 893, SYSDATE, 893, SYSDATE,
                 'Y', v_code, v_storecode, v_remark, v_isgive);
        
        END LOOP;
    END IF;
    --end modified by xy 20180906

    p_code := 1;
    p_message := 'success';

END;