ORACLE PLSQL解析JSON

ORACLE PLSQL解析JSON

下载pljson 地址：https://github.com/pljson/pljson 打开command window 执行：SQL> @C:\pljson-master\install.sql;

select grade, content from shop.t_point_grade_kg_test where grade = 1;

查询结果：

gradecontent1[{“serviceName”:”cashCouponProductImpl”,”serviceParam”:”{\”vouchersRuleId\”:\”1009\”,\”period\”:\”30\”}”},{“serviceName”:”interestCouponProductImpl”,”serviceParam”:”{\”rateRises\”:\”0.5\”,\”riseDays\”:\”5\”,\”productId\”:\”109\”,\”period\”:\”30\”}”}]

json：

[ { "serviceName":"cashCouponProductImpl", "serviceParam":"{"vouchersRuleId":"1009","period":"30"}" }, { "serviceName":"interestCouponProductImpl", "serviceParam":"{"rateRises":"0.5","riseDays":"5","productId":"109","period":"30"}" } ]

plsql

CREATE OR REPLACE PROCEDURE PRC_BIRTH_DATA IS v_content varchar2(300); v_smsContent varchar2(300); v_vouchersRuleId varchar2(50); v_period varchar(20); v_rateRises varchar2(20); v_riseDays varchar2(20); v_productId varchar2(20); jsonArray json_list; BEGIN select content into v_content from shop.t_point_grade_kg_test; --数据解析成json数组 jsonArray := json_list(v_content); --循环json数组解析每条记录 for i in 1..jsonArray.count loop --解析 v_smsContent := json_ext.get_string(json(jsonArray.get(i)),'serviceParam'); --再解析 v_vouchersRuleId := json_ext.get_string(json(v_smsContent),'vouchersRuleId'); v_period := json_ext.get_string(json(v_smsContent),'period'); v_rateRises := json_ext.get_string(json(v_smsContent),'rateRises'); v_riseDays := json_ext.get_string(json(v_smsContent),'riseDays'); v_productId := json_ext.get_string(json(v_smsContent),'productId'); dbms_output.put_line('serviceParam:'||v_smsContent||' vouchersRuleId='||v_vouchersRuleId||' period='||v_period||' rateRises='||v_rateRises||' riseDays='||v_riseDays||' productId='||v_productId); end loop; END PRC_BIRTH_DATA; SQL> set serveroutput on; SQL> set serveroutput on size 1000000; SQL> exec PRC_BIRTH_DATA; serviceParam:{"vouchersRuleId":"1009","period":"30"} vouchersRuleId=1009 period=30 rateRises= riseDays= productId= serviceParam:{"rateRises":"0.5","riseDays":"5","productId":"109","period":"30"} vouchersRuleId= period=30 rateRises=0.5 riseDays=5 roductId=109 PL/SQL procedu re successfully completed

set serveroutput on;控制台显示。 set serveroutput on size 1000000;控制台显示最大数。