PLSQL 解析嵌套json数据
1:解析如下json格式数据，嵌套部分：TEST1,TEST2
{
"Order": [{"TEST1":{"TEST2":{
"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": [{
"sizeCode": "000",
"retailPrice": "0.00"
}]
}
2:解析程序,使用包json_ext下的方法get_json(v_json json,path varchar2)获取固定path下的json
CREATE OR REPLACE PROCEDURE oms_order_gen1(p_json_varchar IN CLOB,
p_code OUT NUMBER,
p_message OUT VARCHAR2) AS
----------------------------------------------------------
--author:xy
----------------------------------------------------------
v_injson json;
v_bodylist json_list; --明细信息json列表
v_onejson json; --用于解析每一个明细数据
--要插入的记录变量
v_omsdocno VARCHAR2(80); --单据号
v_billdate NUMBER(10); --单据日期
v_doctype VARCHAR2(80); --库存类型
v_qty VARCHAR2(80); --数量
v_productalias_no VARCHAR2(80);
v_tot_amt_actual NUMBER(20, 2);
v_priceactual NUMBER(18, 4);
v_olddocno VARCHAR2(80);
v_htprice NUMBER(18, 4);
v_code VARCHAR2(80);
v_storecode VARCHAR2(80);
v_count NUMBER(10);
v_xftillid VARCHAR2(80);
v_remark VARCHAR2(80);
v_isgive VARCHAR2(80);
v_string VARCHAR2(4000);
v_exdpos json;
v_transactiondate NUMBER(10);
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_injson_order := json_ext.get_json(v_injson_order, 'TEST1');
v_injson_order := json_ext.get_json(v_injson_order, 'TEST2');
--获取要插入头表的字段信息
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
raise_application_error(-20201,
'v_storecode:' || v_storecode || ', v_doctype:' ||
v_doctype);
--获取数据，插入到表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'));
p_code := 1;
p_message := 'success';
END LOOP;
END;
3:测试用例
declare
p_json_varchar clob := '{
"Order": [{"TEST1":{"TEST2":{
"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": [{
"sizeCode": "000",
"retailPrice": "0.00"
}]
}';
p_code number;
p_message varchar2(1024);
begin
oms_order_gen1(p_json_varchar,
p_code,
p_message);
end;
4:测试结果
