oracle11gplsql解析json数据⽰例3（新）
对于如下格式的Json数据，⽤下⾯的存储过程进⾏解析（存储过程中json path，即关键字需与json数据⼤⼩写保持⼀致）：
（注：'list'下是数组，包括多个款号，⽽每个款号下会有多个条码，所以对应条码的'LIST_PRODUCT_ALIAS'下为数组，包含多个条码信息）
1：Json数据
{
"msg_no": "EA",
"list": [{
"ORIENTATION": "男款",
"msg_no": "件",
"LIST_PRODUCT_ALIAS": [{
"SAP_ZZSTATUS": false,
"RECKON_TYPE": "20服饰",
"PROD_STATE": "A",
"msg_no": "件",
"table": "LIST_PRODUCT_ALIAS",
"PROD_GRID": "3000225",
"query_no": "2018080600000005",
"SPEC": "00",
"IS_SAP_MANAGE": true,
"DESC_SPEC": "00",
"DESC_COLOR": "银灰",
"PROD_CLS_ID": "120901",
"IS_SAMPLE": false,
"COLOR": "30",
"pK": "null"
}, {
"SAP_ZZSTATUS": false,
"RECKON_TYPE": "20服饰",
"PROD_STATE": "A",
"msg_no": "件",
"table": "LIST_PRODUCT_ALIAS",
"PROD_GRID": "3000225",
"query_no": "2018080600000005",
"SPEC": "00",
"IS_SAP_MANAGE": true,
"DESC_SPEC": "00",
"DESC_COLOR": "银灰",
"PROD_CLS_ID": "120901",
"IS_SAMPLE": false,
"COLOR": "30",
"pK": "null"
}],
"query_no": "2018080600000005",
plsql配置oracle主目录"PROD_ID": "120901",
"PROD_YEAR": "2018",
"BASIC_UOM": "件",
"pK": "120901",
"PROD_SEASON": "夏",
"STATUS1": "A",
"NAME": "120901测试",
"ON_SALE_DATE": 1512748800000,--时间戳
"ISSPESUPPLY": "2",
"BRAND_ID": "MB",
"table": "LIST_PRODUCT",
"MODEL": "J903",
"PLAN_BATCH": "3",
"ISAGT": "2",
"ON_BRAND_PRC": 29
}]
"query_no": "2018080600000005"
}
2：存储过程：
CREATE OR REPLACE PROCEDURE list_productalias_read(p_json    IN CLOB,
p_code    OUT NUMBER,
p_message OUT VARCHAR2) AS
-
---------------------------------------------------------
--author:xuyang
--date:20180809
--description：获取接⼝信息，解析并传⼊商品⽬录中间表
----------------------------------------------------------
v_injson    json;
v_bodylist  json_list; --款号json列表
v_bodylist1 json_list; --条码json列表
v_onejson  json; --⽤于解析每⼀个款号数据
v_onejson1  json; --⽤于解析每⼀个条码数据
BEGIN
-
-将接⼝信息转化为json格式
v_injson := json(p_json);
--获取款号和条码数据，插⼊到中间表list_product和list_product_alias表
v_bodylist := json_list();
v_onejson := json();
v_bodylist1 := json_list();
v_onejson1 := json();
v_bodylist := _json_list(v_injson, 'list');
--循环获取款号数据，并插⼊中间表中
FOR idx IN 1 .. unt LOOP
v_onejson := json(_elem(idx));
INSERT INTO list_product
(id, ad_client_id, ad_org_id, create_date, writedate, ediflag,
ownerid, modifierid, creationdate, modifieddate, isactive, prod_id,
NAME, basic_uom, model, orientation, on_brand_prc, precost, status1,
in_ctrl, kind, old_brand_id, puc_mode, isagt, isspesupply, remark,
prod_year, prod_season, for_season, prod_theme, plan_batch,
on_sale_date, series, themedesign, describemodule, sceneslife,
brand_id, describesys, prod_line, prod_partm, prod_group,
prod_sub_group, prod_sorts)
VALUES
(get_sequences('LIST_PRODUCT'), 37, 27, SYSDATE, SYSDATE, 80, 893,            893, SYSDATE, SYSDATE, 'Y',
_string(v_onejson, 'PROD_ID'),
_string(v_onejson, 'NAME'),
_string(v_onejson, 'BASIC_UOM'),
_string(v_onejson, 'MODEL'),
_string(v_onejson, 'ORIENTATION'),
_number(v_onejson, 'ON_BRAND_PRC'),
_number(v_onejson, 'PRECOST'),
_string(v_onejson, 'STATUS1'),
_string(v_onejson, 'IN_CTRL'),
_string(v_onejson, 'KIND'),
_string(v_onejson, 'OLD_BRAND_ID'),
_string(v_onejson, 'PUC_MODE'),
_string(v_onejson, 'ISAGT'),
_string(v_onejson, 'ISSPESUPPLY'),
_string(v_onejson, 'REMARK'),
_string(v_onejson, 'PROD_YEAR'),
_string(v_onejson, 'PROD_SEASON'),
_string(v_onejson, 'FOR_SEASON'),
_string(v_onejson, 'PROD_THEME'),
_string(v_onejson, 'PLAN_BATCH'),
substr(to_char(_number(v_onejson, 'ON_SALE_DATE') /
(1000 * 60 * 60 * 24) +
to_date('1970-01-01 08:00:00', 'YYYY/MM/DD HH:MI:SS'),
'YYYYMMDD HH24:MI:SS'), 1, 8),
_string(v_onejson, 'SERIES'),
_string(v_onejson, 'THEMEDESIGN'),
_string(v_onejson, 'DESCRIBEMODULE'),
_string(v_onejson, 'SCENESLIFE'),
_string(v_onejson, 'BRAND_ID'),
_string(v_onejson, 'DESCRIBESYS'),
_string(v_onejson, 'PROD_LINE'),
_string(v_onejson, 'PROD_PARTM'),
_string(v_onejson, 'PROD_GROUP'),
_string(v_onejson, 'PROD_SUB_GROUP'),
_string(v_onejson, 'PROD_SORTS'));
IF SQL%ROWCOUNT = 0 THEN
p_message := p_message || 'product_code' ||
_string(v_onejson, 'prod_id') ||
'failed!!';
CONTINUE;
END IF;
v_bodylist1 := _json_list(v_onejson, 'LIST_PRODUCT_ALIAS');
FOR idx1 IN 1 .. unt LOOP
--读取每个条码信息，并插⼊中间表
v_onejson1 := json(_elem(idx1));
INSERT INTO list_product_alias
(id, ad_client_id, ad_org_id, create_date, writedate, ediflag,
ownerid, modifierid, creationdate, modifieddate, isactive,
prod_cls_id, color, desc_color, spec, desc_spec, prod_grid,
addit_desc, inner_bc, intnl_bc, reckon_type, is_sap_manage,
is_sample, sap_zzstatus, prod_state, prodnum)
VALUES
(get_sequences('LIST_PRODUCT_ALIAS'), 37, 27, SYSDATE, SYSDATE,                80, 893, 893, SYSDATE, SYSDATE, 'Y',
_string(v_onejson1, 'PROD_CLS_ID'),
_string(v_onejson1, 'COLOR'),
_string(v_onejson1, 'DESC_COLOR'),
_string(v_onejson1, 'SPEC'),
_string(v_onejson1, 'DESC_SPEC'),
_string(v_onejson1, 'PROD_GRID'),
_string(v_onejson1, 'ADDIT_DESC'),
_string(v_onejson1, 'INNER_BC'),
_string(v_onejson1, 'INTNL_BC'),
_string(v_onejson1, 'RECKON_TYPE'),
_string(v_onejson1, 'IS_SAP_MANAGE'),
_string(v_onejson1, 'IS_SAMPLE'),
_string(v_onejson1, 'SAP_ZZSTATUS'),
_string(v_onejson1, 'PROD_STATE'),
_string(v_onejson1, 'PRODNUM'));
IF SQL%ROWCOUNT = 0 THEN
p_message := p_message || 'product_alias_code:' ||
_string(v_onejson1, 'PROD_CLS_ID') ||
'failed!!';
END IF;
END LOOP;
END LOOP;
p_code := 0;
END;