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;
