使用 PL/SQL 将 JSON 解析为 Oracle 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27122612/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parse JSON into Oracle table using PL/SQL
提问by Hawk
I have created the following script in order to read data from Mobile App DB (which is based on MongoDB) from Oracle SQL Developer:
我创建了以下脚本，以便从 Oracle SQL Developer 的 Mobile App DB（基于 MongoDB）读取数据：
DECLARE
l_param_list VARCHAR2(512);
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_response_text VARCHAR2(32767);
BEGIN
-- service's input parameters
-- preparing Request...
l_http_request := UTL_HTTP.begin_request ('https://api.appery.io/rest/1/db/collections/Photos?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D'
, 'GET'
, 'HTTP/1.1');
-- ...set header's attributes
UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');
--UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));
-- ...set input parameters
-- UTL_HTTP.write_text(l_http_request, l_param_list);
-- get Response and obtain received value
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.read_text(l_http_response, l_response_text);
DBMS_OUTPUT.put_line(l_response_text);
insert into appery values(l_response_text);
-- finalizing
UTL_HTTP.end_response(l_http_response);
EXCEPTION
WHEN UTL_HTTP.end_of_body
THEN UTL_HTTP.end_response(l_http_response);
END;
/
The response (l_response_text)is a JSON-like string. For example:
响应(l_response_text)是一个类似 JSON 的字符串。例如：
[{"Postcode":"47100","OutletCode":"128039251","MobileNumber":"0123071303","_createdAt":"2014-11-10 06:12:49.837","_updatedAt":"2014-11-10 06:12:49.837"}, {"Postcode":"32100","OutletCode":"118034251", ..... ]
The code works fine, and inserts the response into one column-table called appery. However, I need to parse this response such that each array goes into into its specific column in a table called appery_test. The table appery_testhas a number of columns same as the number of JSON pairs and in the same order.
代码运行良好，并将响应插入到一个名为appery 的列表中。但是，我需要解析此响应，以便每个数组都进入名为appery_test的表中的特定列。表appery_test的列数与 JSON 对的数量相同且顺序相同。
I searched and I found most of the results about parsing Oracle table into JSON and not the opposite. I found, though, thislink which is somewhat similar to my issue. However, the suggested library in the answer does not have any example on how to use it to insert JSON into conventional table using PL/SQL.
我搜索并发现了大部分关于将 Oracle 表解析为 JSON 而不是相反的结果。不过，我发现这个链接与我的问题有些相似。但是，答案中建议的库没有关于如何使用它使用 PL/SQL 将 JSON 插入到常规表中的任何示例。
N.B.:I'm using 11g and not 12c. So the built infunctions are not available for me.
注意：我使用的是 11g 而不是 12c。所以内置函数对我不可用。
采纳答案by Hawk
I used PL/JSON library. Specifically, JSON_EXT package functions in order to parse it.
我使用了PL/JSON 库。具体来说，JSON_EXT 包函数以解析它。
The following script inspired by Oracle Community answer worked for me
以下脚本受 Oracle 社区答案启发对我有用
DECLARE
l_param_list VARCHAR2(512);
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_response_text VARCHAR2(32767);
l_list json_list;
A_id VARCHAR2(200);
UserId VARCHAR2(100);
UserName VARCHAR2(100);
OutletCode VARCHAR2(100);
OutletName VARCHAR2(100);
MobileNumber VARCHAR2(100);
PhoneNumber VARCHAR2(100);
Address VARCHAR2(100);
City VARCHAR2(100);
State VARCHAR2(100);
Postcode VARCHAR2(100);
Email VARCHAR2(100);
UpdateCount VARCHAR2(100);
loginCount VARCHAR2(100);
ReferencePhoto VARCHAR2(100);
Updates VARCHAR2(100);
AccountLocked VARCHAR2(100);
Oracle_Flag VARCHAR2(100);
acl VARCHAR2(100);
BEGIN
-- service's input parameters
-- preparing Request...
l_http_request := UTL_HTTP.begin_request('https://api.appery.io/rest/1/db/collections/Outlet_Details?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D'
, 'GET'
, 'HTTP/1.1');
-- ...set header's attributes
UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');
--UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));
-- ...set input parameters
-- UTL_HTTP.write_text(l_http_request, l_param_list);
-- get Response and obtain received value
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.read_text(l_http_response, l_response_text);
DBMS_OUTPUT.put_line(l_response_text);
l_list := json_list(l_response_text);
FOR i IN 1..l_list.count
LOOP
A_id := json_ext.get_string(json(l_list.get(i)),'_id');
UserId := json_ext.get_string(json(l_list.get(i)),'UserId');
UserName := json_ext.get_string(json(l_list.get(i)),'UserName');
OutletCode := json_ext.get_string(json(l_list.get(i)),'OutletCode');
OutletName := json_ext.get_string(json(l_list.get(i)),'OutletName');
MobileNumber := json_ext.get_string(json(l_list.get(i)),'MobileNumber');
PhoneNumber := json_ext.get_string(json(l_list.get(i)),'PhoneNumber');
Address := json_ext.get_string(json(l_list.get(i)),'Address');
City := json_ext.get_string(json(l_list.get(i)),'City');
State := json_ext.get_string(json(l_list.get(i)),'State');
Postcode := json_ext.get_string(json(l_list.get(i)),'Postcode');
Email := json_ext.get_string(json(l_list.get(i)),'Email');
UpdateCount := json_ext.get_string(json(l_list.get(i)),'UpdateCount');
loginCount := json_ext.get_string(json(l_list.get(i)),'loginCount');
ReferencePhoto := json_ext.get_string(json(l_list.get(i)),'ReferencePhoto');
Updates := json_ext.get_string(json(l_list.get(i)),'Updates');
AccountLocked := json_ext.get_string(json(l_list.get(i)),'AccountLocked');
Oracle_Flag := json_ext.get_string(json(l_list.get(i)),'Oracle_Flag');
acl := json_ext.get_string(json(l_list.get(i)),'acl');
insert .....
Notice that json_ext.get_stringretuns only VARCHAR2 limited to 32767 max. In order to use the same package with larger json_list and json_values (>32KB) check here.
请注意，json_ext.get_string仅返回限制为 32767 最大值的 VARCHAR2。为了使用具有更大 json_list 和 json_values (>32KB) 的相同包，请检查此处。
If you have APEX 5.0 and above, better option and much better performance via APEX_JSONpackage. See @Olafur Tryggvason's answer for details
如果您有 APEX 5.0 及更高版本，则可以通过APEX_JSON包获得更好的选择和更好的性能。详情请参阅@Olafur Tryggvason的回答
回答by Olafur Tryggvason
Since this question scores high in results, I want to post this preferred alternative:
由于这个问题的结果得分很高，我想发布这个首选的替代方案：
Oracle has released APEX 5.0(April 15. 2015). With it you get access to a great API to work with JSON
Oracle 已发布APEX 5.0（2015 年 4 月 15 日）。有了它，你就可以访问一个很棒的 API 来处理 JSON
I'm using it on 11.2 and have been able to crunch every single json, from simple to very complex objects with multiple arrays and 4/5 levels. APEX_JSON
我在 11.2 上使用它，并且能够处理每个单独的 json，从简单到非常复杂的具有多个数组和 4/5 级别的对象。 APEX_JSON
If you do not want to use APEX. Simply install the runtime environment to get access to the API.
如果您不想使用 APEX。只需安装运行时环境即可访问 API。
Sample usage, data from json.org's example:
示例用法，来自json.org 示例的数据：
declare
sample_json varchar2 (32767)
:= '{
"glossary": {
"title": "example glossary",
"GlossDiv": {
"title": "S",
"GlossList": {
"GlossEntry": {
"ID": "SGML",
"SortAs": "SGML",
"GlossTerm": "Standard Generalized Markup Language",
"Acronym": "SGML",
"Abbrev": "ISO 8879:1986",
"GlossDef": {
"para": "A meta-markup language, used to create markup languages such as DocBook.",
"GlossSeeAlso": ["GML", "XML"]
},
"GlossSee": "markup"
}
}
}
}
}';
begin
apex_json.parse (sample_json);
dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.title'));
dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.GlossList.GlossEntry.GlossTerm'));
dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.GlossList.GlossEntry.GlossDef.GlossSeeAlso[%d]', 2));
end;
Result: PL/SQL block executed
结果：PL/SQL 块被执行
S
Standard Generalized Markup Language
XML
回答by Srikrishnan
Orace provides PL/SQL DOM APIs for JSON handling. Strongly recommend using it, as it provides tons of useful APIs.
Orace 为 JSON 处理提供了 PL/SQL DOM API。强烈推荐使用它，因为它提供了大量有用的 API。
回答by Z. Anton
in Oracle 12: https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973
在 Oracle 12 中：https: //docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973
SELECT jt.*
FROM j_purchaseorder,
JSON_TABLE(po_document, '$.ShippingInstructions.Phone[*]'
COLUMNS (row_number FOR ORDINALITY,
phone_type VARCHAR2(10) PATH '$.type',
phone_num VARCHAR2(20) PATH '$.number'))
AS jt;
ROW_NUMBER PHONE_TYPE PHONE_NUM
---------- ---------- --------------------
1 Office 909-555-7307
2 Mobile 415-555-1234
