在 oracle 中使用 json
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7985841/
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
work with json in oracle
提问by Lloyd
Is there an easy way to work with JSON within oracle? I have a standard procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but what is the best way to work with json within a stored procedure? For instance take the CLOB response from the URI, convert that to a JSON object and get a value from that?
是否有一种在 oracle 中使用 JSON 的简单方法？我有一个经常用来调用 Web 服务的标准过程，JSON 是我在 Web 开发环境中熟悉的一种格式，但是在存储过程中使用 json 的最佳方法是什么？例如，从 URI 获取 CLOB 响应，将其转换为 JSON 对象并从中获取值？
For reference sake, here is the procedure I used to fetch URLs
作为参考，这里是我用来获取 URL 的过程
create or replace procedure macp_URL_GET(url_resp in out clob, v_url in varchar2) is
req Utl_Http.req;
resp Utl_Http.resp;
NAME VARCHAR2 (255);
VALUE VARCHAR2 (1023);
v_msg VARCHAR2 (80);
v_ans clob;
-- v_url VARCHAR2 (32767) := 'http://www.macalester.edu/';
BEGIN
/* request that exceptions are raised for error Status Codes */
Utl_Http.set_response_error_check (ENABLE => TRUE );
/* allow testing for exceptions like Utl_Http.Http_Server_Error */
Utl_Http.set_detailed_excp_support (ENABLE => TRUE );
/*
Utl_Http.set_proxy (
proxy => 'www-proxy.us.oracle.com',
no_proxy_domains => 'us.oracle.com'
);
*/
req := Utl_Http.begin_request (url => v_url, method => 'GET');
/*
Alternatively use method => 'POST' and Utl_Http.Write_Text to
build an arbitrarily long message
*/
/*
Utl_Http.set_authentication (
r => req,
username => 'SomeUser',
PASSWORD => 'SomePassword',
scheme => 'Basic',
for_proxy => FALSE --this info is for the target Web server
);
*/
Utl_Http.set_header (r => req, NAME => 'User-Agent', VALUE => 'Mozilla/4.0');
resp := Utl_Http.get_response (r => req);
/*
DBMS_OUTPUT.put_line ('Status code: ' || resp.status_code);
DBMS_OUTPUT.put_line ('Reason phrase: ' || resp.reason_phrase);
FOR i IN 1 .. Utl_Http.get_header_count (r => resp)
LOOP
Utl_Http.get_header (r => resp, n => i, NAME => NAME, VALUE => VALUE);
DBMS_OUTPUT.put_line (NAME || ': ' || VALUE);
END LOOP;
*/
--test
BEGIN
LOOP
Utl_Http.read_text (r => resp, DATA => v_msg);
--DBMS_OUTPUT.put_line (v_msg);
v_ans := v_ans || v_msg;
url_resp := url_resp || v_msg;
END LOOP;
EXCEPTION
WHEN Utl_Http.end_of_body
THEN
NULL;
END;
--test
Utl_Http.end_response (r => resp);
--url_resp := v_ans;
EXCEPTION
/*
The exception handling illustrates the use of "pragma-ed" exceptions
like Utl_Http.Http_Client_Error. In a realistic example, the program
would use these when it coded explicit recovery actions.
Request_Failed is raised for all exceptions after calling
Utl_Http.Set_Detailed_Excp_Support ( ENABLE=>FALSE )
And it is NEVER raised after calling with ENABLE=>TRUE
*/
WHEN Utl_Http.request_failed
THEN
DBMS_OUTPUT.put_line (
'Request_Failed: ' || Utl_Http.get_detailed_sqlerrm
);
url_resp :='Request_Failed: ' || Utl_Http.get_detailed_sqlerrm;
/* raised by URL http://xxx.oracle.com/ */
WHEN Utl_Http.http_server_error
THEN
DBMS_OUTPUT.put_line (
'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm
);
url_resp := 'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm;
/* raised by URL http://otn.oracle.com/xxx */
WHEN Utl_Http.http_client_error
THEN
DBMS_OUTPUT.put_line (
'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm
);
url_resp := 'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm;
/* code for all the other defined exceptions you can recover from */
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
url_resp := SQLERRM;
END;
Then to test it
然后测试一下
begin
macp_url_get(url_resp => :url_resp,
'http://maps.googleapis.com/maps/api/geocode/json?address=55105&sensor=false');
end;
(I know that the googleapi will allow xml response, but there are other web APIs that I use regularly that default to JSON)
（我知道 googleapi 将允许 xml 响应，但我经常使用其他默认为 JSON 的 Web API）
采纳答案by jmc
I have started using this library, and it seems promising: https://github.com/pljson/pljson
我已经开始使用这个库，看起来很有希望：https: //github.com/pljson/pljson
Easy to install, and the examples are good.
易于安装，示例很好。
To use the library in your example, add these variables to your procedure..
要在您的示例中使用该库，请将这些变量添加到您的过程中。
mapData json;
results json_list;
status json_value;
firstResult json;
geometry json;
....
....
Then you can manipulate the response as a json object.
然后您可以将响应作为 json 对象进行操作。
-- convert the result from the get to a json object, and show some results.
mapData := json(v_ans);
-- Show the status of the request
status := mapData.get('status');
dbms_output.put_line('Status = ' || status.get_string());
IF (status.get_string() = 'OK') THEN
results := json_list(mapData.get('results'));
-- Grab the first item in the list
resultObject := json(results.head);
-- Show the human readable address
dbms_output.put_line('Address = ' || resultObject.get('formatted_address').to_char() );
-- Show the json location data
dbms_output.put_line('Location = ' || resultObject.get('geometry').to_char() );
END IF;
Running this code will output this to the dbms output:
运行此代码会将其输出到 dbms 输出：
Status = OK
Address = "St Paul, MN 55105, USA"
Location = {
"bounds" : {
"northeast" : {
"lat" : 44.9483849,
"lng" : -93.1261959
},
"southwest" : {
"lat" : 44.9223829,
"lng" : -93.200307
}
},
"location" : {
"lat" : 44.9330076,
"lng" : -93.16290629999999
},
"location_type" : "APPROXIMATE",
"viewport" : {
"northeast" : {
"lat" : 44.9483849,
"lng" : -93.1261959
},
"southwest" : {
"lat" : 44.9223829,
"lng" : -93.200307
}
}
}
回答by Stuart Brock
It should be noted that as of Oracle 12c there is some native support of JSON. However i don't think in the current form it's as useful as the like of PLJSON included in another answer.
应该注意的是，从 Oracle 12c 开始，有一些对 JSON 的原生支持。但是，我认为在当前形式中，它不像另一个答案中包含的 PLJSON 那样有用。
To use the feature you create a table with a BLOB, CLOB or Varchar2 field and add a constraint against it "column IS JSON". This enforces JSON syntax checking on that column.
要使用该功能，您需要创建一个带有 BLOB、CLOB 或 Varchar2 字段的表，并针对它添加一个约束“列是 JSON”。这会对该列强制执行 JSON 语法检查。
As long as the "IS JSON" constraint is in place you can access the JSON values within using dot notation from SQL. To me, it doesn't seem to provide as powerful manipulation as PLJSON. You can also create an XMLType and then convert to JSON.
只要“IS JSON”约束到位，您就可以使用 SQL 中的点表示法访问 JSON 值。对我来说，它似乎没有像 PLJSON 那样提供强大的操作。您还可以创建一个 XMLType，然后转换为 JSON。
Useful links:
Oracle docs
Good tutorial and examples
Tutorial including XML to JSON
回答by Pierre-Gilles Levallois
I wrote this library : http://reseau.erasme.org/pl-sql-library-for-JSON?lang=en, and this works great to get some json response into a plsql table.
我写了这个库：http: //reseau.erasme.org/pl-sql-library-for-JSON?lang=en ，这非常适合将一些 json 响应放入 plsql 表中。
If you only want to extract Oracle data and transform it in Json, this library is a bit "Heavy to use"... I can propose you another code doing it better and faster :
如果你只想提取 Oracle 数据并在 Json 中转换它，这个库有点“重用”......我可以建议你另一个代码做得更好更快：
create or replace package jsonfly as
procedure open_object(k varchar2 default null);
procedure close_object;
procedure open_array (k varchar2 default null);
procedure close_array;
procedure separation;
procedure member(k varchar2, v varchar2);
procedure member(k varchar2, n number);
procedure send;
end;
/
create or replace package body jsonfly as
--------------------------------------------------------------------------------
-- package pour générer du JSON, envoyé à la volé
--------------------------------------------------------------------------------
type tCache is table of varchar2(2000) index by binary_integer;
g_openBrace constant varchar2(2) := '{ ';
g_closeBrace constant varchar2(2) := ' }';
g_openBracket constant varchar2(2) := '[ ';
g_closeBracket constant varchar2(2) := ' ]';
g_stringDelimiter constant varchar2(1) := '"';
g_Affectation constant varchar2(3) := ' : ';
g_separation constant varchar2(3) := ', ';
g_CR constant varchar2(1) := Chr(10); -- used to indent the JSON object correctly
g_spc constant varchar2(2) := ' '; -- used to indent the JSON object correctly
g_js_comment_open constant varchar2(20) := '/*-secure-\n'; -- used to prevent from javascript hiHymaning
g_js_comment_close constant varchar2(20) := '\n*/'; -- used to prevent from javascript hiHymaning
--isObjectOpened boolean := false;
--isArrayOpened boolean := false;
t tCache;
i number := 1;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure p(s varchar2) is
begin
t(i) := s;
i := i + 1;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
function encap (s varchar2) return varchar2 is
begin
return g_stringdelimiter || s || g_stringdelimiter;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
function encode_string(p_string varchar2) return varchar2 is
begin
return replace(replace(replace(replace(replace(replace(replace(replace(p_string,
'\', '\'),
'"', '\"'),
'/', '\/'),
chr(8), '\b'),
chr(9), '\t'),
chr(10), '\n'),
chr(12), '\f'),
chr(13), '\r');
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure open_object(k varchar2 default null) is
begin
if ( k is null ) then
p(g_openbrace);
else
p( encap(k) || g_affectation || g_openbrace);
end if;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure close_object is
begin
if (t(i-1) = g_separation) then
i := i - 1;
end if;
p(g_closebrace);
separation();
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure open_array (k varchar2 default null) is
begin
if ( k is null ) then
p(g_openbracket);
else
p( encap(k) || g_affectation || g_openbracket);
end if;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure close_array is
begin
if (t(i-1) = g_separation) then
i := i - 1;
end if;
p(g_closebracket);
separation();
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure separation is
begin
p(g_separation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure key(k varchar2) is
begin
p( encap(k) || g_affectation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure value(v varchar2) is
begin
p(v);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure member(k varchar2, v varchar2) is
begin
p( encap(k) || g_affectation || encap(encode_string(v)));
p(g_separation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure member(k varchar2, n number) is
begin
p( encap(k) || g_affectation || n );
p(g_separation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure send is
begin
if (t(i-1) = g_separation) then
t.delete(i-1);
end if;
i := t.first;
while (i is not null) loop
htp.p(t(i));
i := t.next(i);
end loop;
end;
end jsonfly;
/
回答by Nashev
Oracle 12c now have native JSON support:
Oracle 12c 现在具有原生 JSON 支持：
Oracle Database supports JavaScript Object Notation (JSON) data natively with relational database features, including transactions, indexing, declarative querying, and views
Oracle 数据库通过关系数据库特性（包括事务、索引、声明式查询和视图）原生支持 JavaScript 对象表示法 (JSON) 数据
JSON data and XML data can be used in Oracle Database in similar ways. Unlike relational data, both can be stored, indexed, and queried without any need for a schema that defines the data. Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views.
JSON 数据和 XML 数据可以以类似的方式在 Oracle 数据库中使用。与关系数据不同，两者都可以存储、索引和查询，而无需定义数据的模式。Oracle 数据库通过关系数据库特性原生支持 JSON，包括事务、索引、声明式查询和视图。
JSON data has often been stored in NoSQL databases such as Oracle NoSQL Database and Oracle Berkeley DB. These allow for storage and retrieval of data that is not based on any schema, but they do not offer the rigorous consistency models of relational databases.
JSON 数据通常存储在 NoSQL 数据库中，例如 Oracle NoSQL Database 和 Oracle Berkeley DB。这些允许存储和检索不基于任何模式的数据，但它们不提供关系数据库的严格一致性模型。
To compensate for this shortcoming, a relational database is sometimes used in parallel with a NoSQL database. Applications using JSON data stored in the NoSQL database must then ensure data integrity themselves.
为了弥补这一缺点，有时会与 NoSQL 数据库并行使用关系数据库。使用存储在 NoSQL 数据库中的 JSON 数据的应用程序必须自己确保数据完整性。
Native support for JSON by Oracle Database obviates such workarounds. It provides all of the benefits of relational database features for use with JSON, including transactions, indexing, declarative querying, and views.
Oracle 数据库对 JSON 的本机支持避免了此类变通方法。它提供了与 JSON 一起使用的关系数据库功能的所有好处，包括事务、索引、声明式查询和视图。
Oracle Database queries are declarative. You can join JSON data with relational data. And you can project JSON data relationally, making it available for relational processes and tools. You can also query, from within the database, JSON data that is stored outside the database in an external table.
Oracle 数据库查询是声明性的。您可以将 JSON 数据与关系数据连接起来。您可以以关系方式投影 JSON 数据，使其可用于关系流程和工具。您还可以从数据库内部查询存储在数据库外部的外部表中的 JSON 数据。
You can access JSON data stored in the database the same way you access other database data, including using OCI, .NET, and JDBC.
您可以像访问其他数据库数据一样访问存储在数据库中的 JSON 数据，包括使用 OCI、.NET 和 JDBC。
Unlike XML data, which is stored using SQL data type XMLType, JSON data is stored in Oracle Database using SQL data types VARCHAR2, CLOB, and BLOB. Oracle recommends that you always use an is_json check constraint to ensure that column values are valid JSON instances
与使用 SQL 数据类型 XMLType 存储的 XML 数据不同，JSON 数据使用 SQL 数据类型 VARCHAR2、CLOB 和 BLOB 存储在 Oracle 数据库中。Oracle 建议您始终使用 is_json 检查约束来确保列值是有效的 JSON 实例
回答by Shahbaz Ali
Life is happy try this:
生活很幸福试试这个：
CLOB or 20000000 characters JSON
CLOB 或 20000000 个字符的 JSON
with data as
( select
xmlelement(e,regexp_replace('{"name":"'||colname||'"}', '[[:cntrl:]]', ''),',') col1
from tblname
)
select
rtrim(replace(replace(replace(xmlagg(col1).getclobval(),'&'||'quot;','"'),'<E>',''),'</E>',''),',')
as very_long_json
from data;
回答by Parvez
Oracle APEX 5.0has support for JSON using APEX_JSONpackage. I haven't used it but it looks interesting and I have asked my team to explore it. Our use case is to be able to pass JSON data as input parameter to stored procedure from nodejs application.
Oracle APEX 5.0使用APEX_JSON包支持 JSON 。我没有使用过它，但它看起来很有趣，我已经要求我的团队探索它。我们的用例是能够将 JSON 数据作为输入参数从 nodejs 应用程序传递到存储过程。
