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;

查询结果：

grade content 1 [{“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=109PL/SQL procedure successfully completed

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