{"id":962,"date":"2016-12-12T15:12:10","date_gmt":"2016-12-12T07:12:10","guid":{"rendered":"http:\/\/www.oracle.name\/?p=962"},"modified":"2016-12-14T09:50:57","modified_gmt":"2016-12-14T01:50:57","slug":"oracle-plsql-json%e7%b1%bb%e6%8e%a5%e5%8f%a3%e4%be%8b%e5%ad%90","status":"publish","type":"post","link":"https:\/\/www.llku.com\/?p=962","title":{"rendered":"Oracle PLSQL Json\u7c7b\u63a5\u53e3\u4f8b\u5b50"},"content":{"rendered":"<p>\n\t\u8bf4\u660e\uff1a\u6b64\u6bb5\u4f8b\u5b50\u7a0b\u5e8f\u6765\u6e90\u516c\u53f8\u95eb\u540c\u4e8b\uff0c\u9700\u8981json\u5b89\u88c5\u6587\u4ef6\u7684\uff0c\u53ef\u7559\u8a00\u7d22\u53d6\u3002\n<\/p>\n<p>\n\t\u4f8b\u5b50\u7a0b\u5e8f\uff1a\n<\/p>\n<p>\n\t&#8211;\u7269\u6599LIST\u7ed3\u679c\u89e3\u6790<br \/>\n\tDECLARE<br \/>\n\t&nbsp; l_item_info json;<br \/>\n\t&nbsp; l_item_list json_list;<br \/>\n\t&nbsp; l_item_line json;\n<\/p>\n<p>\n\t&nbsp; l_ret_code&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(100);<br \/>\n\t&nbsp; l_ret_msg&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(100);<br \/>\n\t&nbsp; l_json_list_data json_list;<br \/>\n\t&nbsp; l_temp_line&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; json;<br \/>\n\t&nbsp; l_erpcode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(100);<br \/>\n\t&nbsp; l_itemno&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(100);<br \/>\n\t&nbsp; l_type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(100);\n<\/p>\n<p>\n\tBEGIN<br \/>\n\t&nbsp; l_item_info := json();\n<\/p>\n<p>\n\t&nbsp; l_item_info.put(&#39;code&#39;,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;1&#39;);<br \/>\n\t&nbsp; l_item_info.put(&#39;message&#39;,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;\u8c03\u7528\u6210\u529f&#39;);\n<\/p>\n<p>\n\t&nbsp; l_item_list := json_list();\n<\/p>\n<p>\n\t&nbsp; &#8211;\u7269\u65991<br \/>\n\t&nbsp; l_item_line := json();<br \/>\n\t&nbsp; l_item_line.put(&#39;erpCode&#39;,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;10000001&#39;);<br \/>\n\t&nbsp; l_item_line.put(&#39;itemNo&#39;,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;22222222222&#39;);<br \/>\n\t&nbsp; l_item_line.put(&#39;type&#39;,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;4&#39;);<br \/>\n\t&nbsp; dbms_output.put_line(json_ext.get_string(l_item_line,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;erpCode&#39;) || &#39;\u662f:&#39;);<br \/>\n\t&nbsp; l_item_line.print;<br \/>\n\t&nbsp; dbms_output.put_line(&#39;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;&#39;);<br \/>\n\t&nbsp; l_item_list.append(l_item_line.to_json_value);\n<\/p>\n<p>\n\t&nbsp; &#8211;\u7269\u65992<br \/>\n\t&nbsp; l_item_line := json();<br \/>\n\t&nbsp; l_item_line.put(&#39;erpCode&#39;,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;10000003&#39;);<br \/>\n\t&nbsp; l_item_line.put(&#39;itemNo&#39;,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;333333333333&#39;);<br \/>\n\t&nbsp; l_item_line.put(&#39;type&#39;,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;2&#39;);<br \/>\n\t&nbsp; dbms_output.put_line(json_ext.get_string(l_item_line,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;erpCode&#39;) || &#39;\u662f:&#39;);<br \/>\n\t&nbsp; l_item_line.print;<br \/>\n\t&nbsp; dbms_output.put_line(&#39;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;&#39;);<br \/>\n\t&nbsp; l_item_list.append(l_item_line.to_json_value);\n<\/p>\n<p>\n\t&nbsp; &#8211;\u7269\u6599LIST\u6784\u6210<br \/>\n\t&nbsp; dbms_output.put_line(&#39;l_item_list\u662f\uff1a&#39;);<br \/>\n\t&nbsp; l_item_list.print;<br \/>\n\t&nbsp; dbms_output.put_line(&#39;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;&#39;);\n<\/p>\n<p>\n\t&nbsp; &#8211;\u8fd4\u56de\u7ed3\u679c\u6574\u4f53\u6784\u6210<br \/>\n\t&nbsp; l_item_info.put(&#39;data&#39;,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_item_list.to_json_value);<br \/>\n\t&nbsp; dbms_output.put_line(&#39;l_item_info\u662f\uff1a&#39;);<br \/>\n\t&nbsp; l_item_info.print;<br \/>\n\t&nbsp; dbms_output.put_line(&#39;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;&#39;);\n<\/p>\n<p>\n\t&nbsp; &#8211;\u8fd4\u56de\u7ed3\u679c\u89e3\u6790<br \/>\n\t&nbsp; l_ret_code := json_ext.get_string(l_item_info,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;code&#39;);<br \/>\n\t&nbsp; l_ret_msg&nbsp; := json_ext.get_string(l_item_info,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;message&#39;);\n<\/p>\n<p>\n\t&nbsp; dbms_output.put_line(&#39;\u89e3\u6790\u7ed3\u679c\uff1a&#39;);<br \/>\n\t&nbsp; dbms_output.put_line(&#39;l_ret_code: &#39; || l_ret_code);<br \/>\n\t&nbsp; dbms_output.put_line(&#39;l_ret_msg: &#39; || l_ret_msg);\n<\/p>\n<p>\n\t&nbsp; IF l_ret_code = &#39;1&#39; THEN<br \/>\n\t&nbsp;&nbsp;&nbsp; l_json_list_data := json_list(l_item_info.get(&#39;data&#39;));<br \/>\n\t&nbsp;<br \/>\n\t&nbsp;&nbsp;&nbsp; FOR i IN 1 .. l_json_list_data.count()<br \/>\n\t&nbsp;&nbsp;&nbsp; LOOP<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_temp_line := json(l_json_list_data.get(i));<br \/>\n\t&nbsp;&nbsp;&nbsp;<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_erpcode := json_ext.get_string(l_temp_line,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;erpCode&#39;);<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_itemno&nbsp; := json_ext.get_string(l_temp_line,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;itemNo&#39;);<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_type&nbsp;&nbsp;&nbsp; := json_ext.get_string(l_temp_line,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;type&#39;);<br \/>\n\t&nbsp;&nbsp;&nbsp;<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line(&#39;l_erpcode: &#39; || l_erpcode);<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line(&#39;l_itemNo: &#39; || l_itemno);<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line(&#39;l_type: &#39; || l_type);<br \/>\n\t&nbsp;&nbsp;&nbsp;<br \/>\n\t&nbsp;&nbsp;&nbsp; END LOOP;<br \/>\n\t&nbsp;<br \/>\n\t&nbsp; END IF;\n<\/p>\n<p>\n\tEND;\n<\/p>\n<p>\n\t&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;\n<\/p>\n<p>\n\t\u4ee5\u4e0b\u662f\u8f93\u51fa\u7ed3\u679c\uff1a\n<\/p>\n<p>\n\t10000001\u662f:<br \/>\n\t{<br \/>\n\t&nbsp; &quot;erpCode&quot; : &quot;10000001&quot;,<br \/>\n\t&nbsp; &quot;itemNo&quot; : &quot;22222222222&quot;,<br \/>\n\t&nbsp; &quot;type&quot; : &quot;4&quot;<br \/>\n\t}<br \/>\n\t&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<br \/>\n\t10000003\u662f:<br \/>\n\t{<br \/>\n\t&nbsp; &quot;erpCode&quot; : &quot;10000003&quot;,<br \/>\n\t&nbsp; &quot;itemNo&quot; : &quot;333333333333&quot;,<br \/>\n\t&nbsp; &quot;type&quot; : &quot;2&quot;<br \/>\n\t}<br \/>\n\t&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<br \/>\n\tl_item_list\u662f\uff1a<br \/>\n\t[{<br \/>\n\t&nbsp; &quot;erpCode&quot; : &quot;10000001&quot;,<br \/>\n\t&nbsp; &quot;itemNo&quot; : &quot;22222222222&quot;,<br \/>\n\t&nbsp; &quot;type&quot; : &quot;4&quot;<br \/>\n\t}, {<br \/>\n\t&nbsp; &quot;erpCode&quot; : &quot;10000003&quot;,<br \/>\n\t&nbsp; &quot;itemNo&quot; : &quot;333333333333&quot;,<br \/>\n\t&nbsp; &quot;type&quot; : &quot;2&quot;<br \/>\n\t}]<br \/>\n\t&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<br \/>\n\tl_item_info\u662f\uff1a<br \/>\n\t{<br \/>\n\t&nbsp; &quot;code&quot; : &quot;1&quot;,<br \/>\n\t&nbsp; &quot;message&quot; : &quot;\\u8C03\\u7528\\u6210\\u529F&quot;,<br \/>\n\t&nbsp; &quot;data&quot; : [{<br \/>\n\t&nbsp;&nbsp;&nbsp; &quot;erpCode&quot; : &quot;10000001&quot;,<br \/>\n\t&nbsp;&nbsp;&nbsp; &quot;itemNo&quot; : &quot;22222222222&quot;,<br \/>\n\t&nbsp;&nbsp;&nbsp; &quot;type&quot; : &quot;4&quot;<br \/>\n\t&nbsp; }, {<br \/>\n\t&nbsp;&nbsp;&nbsp; &quot;erpCode&quot; : &quot;10000003&quot;,<br \/>\n\t&nbsp;&nbsp;&nbsp; &quot;itemNo&quot; : &quot;333333333333&quot;,<br \/>\n\t&nbsp;&nbsp;&nbsp; &quot;type&quot; : &quot;2&quot;<br \/>\n\t&nbsp; }]<br \/>\n\t}<br \/>\n\t&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<br \/>\n\t\u89e3\u6790\u7ed3\u679c\uff1a<br \/>\n\tl_ret_code: 1<br \/>\n\tl_ret_msg: \u8c03\u7528\u6210\u529f<br \/>\n\tl_erpcode: 10000001<br \/>\n\tl_itemNo: 22222222222<br \/>\n\tl_type: 4<br \/>\n\tl_erpcode: 10000003<br \/>\n\tl_itemNo: 333333333333<br \/>\n\tl_type: 2<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u8bf4\u660e\uff1a\u6b64\u6bb5\u4f8b\u5b50\u7a0b\u5e8f\u6765\u6e90\u516c\u53f8\u95eb\u540c\u4e8b\uff0c\u9700\u8981json\u5b89\u88c5\u6587\u4ef6\u7684\uff0c\u53ef\u7559\u8a00\u7d22\u53d6\u3002 \u4f8b\u5b50\u7a0b\u5e8f\uff1a &#8211;\u7269\u6599LIST &hellip; <a href=\"https:\/\/www.llku.com\/?p=962\" class=\"more-link\">\u7ee7\u7eed\u9605\u8bfb<span class=\"screen-reader-text\">Oracle PLSQL Json\u7c7b\u63a5\u53e3\u4f8b\u5b50<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[13],"tags":[148],"_links":{"self":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/962"}],"collection":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=962"}],"version-history":[{"count":4,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/962\/revisions"}],"predecessor-version":[{"id":967,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/962\/revisions\/967"}],"wp:attachment":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=962"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=962"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=962"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}