我想解析一个存储在Tests_1表的CLOB列中的JSON字符串，并将其插入另一个表（Test_2）。
如何在PL/SQL中完成此操作而不使用任何JSON库？
create table Tests_1
(
value CLOB
)
create table Test_2 (a date,b date,c number,d number, e number)
INSERT INTO Tests_1
(value)
VALUES
('{
"a":"01/01/2015",
"b":"31/12/2015",
"c":"11111111111",
"d":"1111111111",
"e":"1234567890"
}');
11.0.4版本（当然没有11.0.4版本）你至少有两个选择（除了自己编写解析器）：
根据您所使用的RDBMS版本，这里有几个选项：
第一个：对于Oracle 11.1.0.7及以上版本，请安装Apex 5并使用apex_json包：
-- here I have 12.1.0.1 version with version 5 of apex installed
column ora_version format a21;
column apex_version format a21;
select (select version from v$instance) as ora_version
, (select version_no from apex_release) as apex_version
from dual;
--drop table test_2;
/* our test table */
create table test_2(
c_a date,
c_b date,
c_c number,
c_d number,
c_e number
);
select * from test_2;
declare
l_json_doc clob;
begin
dbms_output.put_line('Parsing json...');
l_json_doc := '{"a":"01/01/2015","b":"31/12/2015",
"c":"11111111111","d":"1111111111",
"e":"1234567890"}';
apex_json.parse(l_json_doc);
insert into test_2(c_a, c_b, c_c, c_d, c_e)
values(apex_json.get_date(p_path=>'a', p_format=>'dd/mm/yyyy'),
apex_json.get_date(p_path=>'b', p_format=>'dd/mm/yyyy'),
to_number(apex_json.get_varchar2(p_path=>'c')),
to_number(apex_json.get_varchar2(p_path=>'d')),
to_number(apex_json.get_varchar2(p_path=>'e')));
commit;
dbms_output.put_line('Done!');
end;
/
column c_c format 99999999999;
select to_char(c_a, 'dd/mm/yyyy') as c_a
, to_char(c_b, 'dd/mm/yyyy') as c_b
, c_c
, c_d
, c_e
from test_2;
结果：
ORA_VERSION APEX_VERSION
--------------------- ---------------------
12.1.0.1.0 5.0.2.00.07
1 row selected.
Table created.
no rows selected.
Parsing json...
Done!
PL/SQL procedure successfully completed.
C_A C_B C_C C_D C_E
---------- ---------- ------------ ---------- ----------
01/01/2015 31/12/2015 11111111111 1111111111 1234567890
1 row selected.
第二个: 使用开源的PL/JSON。我以前从未使用过，所以我利用这个机会试用一下。它与apex_json非常相似。
declare
l_json json; --json object
l_json_doc clob;
begin
dbms_output.put_line('Parsing json...');
-- parsing is done upon object instantiation
l_json_doc := '{"a":"01/01/2015","b":"31/12/2015",
"c":"11111111111","d":"1111111111",
"e":"1234567890"}';
l_json := json(l_json_doc);
insert into test_2(c_a, c_b, c_c, c_d, c_e)
values(to_date(l_json.get('a').get_string, 'dd-mm-yyyy'),
to_date(l_json.get('b').get_string, 'dd-mm-yyyy'),
to_number(l_json.get('c').get_string),
to_number(l_json.get('d').get_string),
to_number(l_json.get('e').get_string));
commit;
dbms_output.put_line('Done!');
end;
column c_c format 99999999999;
select to_char(c_a, 'dd/mm/yyyy') as c_a
, to_char(c_b, 'dd/mm/yyyy') as c_b
, c_c
, c_d
, c_e
from test_2;
结果：
C_A C_B C_C C_D C_E
---------- ---------- ------------ ---------- ----------
01/01/2015 31/12/2015 11111111111 1111111111 1234567890
01/01/2015 31/12/2015 11111111111 1111111111 1234567890
2 rows selected.
在 12.1.0.2 版本中，json_table() 的介绍使得 JSON 解析变得更简单一些（仅为演示而说）：
insert into test_2
select to_date(c_a, 'dd-mm-yyyy')
, to_date(c_b, 'dd-mm-yyyy')
, c_c
, c_d
, c_e
from json_table('{"a":"01/01/2015",
"b":"31/12/2015",
"c":"11111111111",
"d":"1111111111",
"e":"1234567890"}'
, '$'
columns (
c_a varchar2(21) path '$.a',
c_b varchar2(21) path '$.b',
c_c varchar2(21) path '$.c',
c_d varchar2(21) path '$.d',
c_e varchar2(21) path '$.e'
)) ;
结果：
select *
from test_2;
C_A C_B C_C C_D C_E
----------- ----------- ---------- ---------- ----------
1/1/2015 12/31/2015 1111111111 1111111111 1234567890
Oracle 12c 支持 JSON 格式。
如果已经存在一张表，只需执行以下操作：
ALTER TABLE table1 ADD CONSTRAINT constraint_name CHECK (your_column IS json);
SELECT t.your_column.id FROM table1 t;
t昵称。CREATE TABLE json_documents (
id RAW(16) NOT NULL,
data CLOB,
CONSTRAINT json_documents_pk PRIMARY KEY (id),
CONSTRAINT json_documents_json_chk CHECK (data IS JSON)
);
INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(),
'{
"FirstName" : "John",
"LastName" : "Doe",
"Job" : "Clerk",
"Address" : {
"Street" : "99 My Street",
"City" : "My City",
"Country" : "UK",
"Postcode" : "A12 34B"
},
"ContactDetails" : {
"Email" : "john.doe@example.com",
"Phone" : "44 123 123456",
"Twitter" : "@johndoe"
},
"DateOfBirth" : "01-JAN-1980",
"Active" : true
}');
SELECT a.data.FirstName,
a.data.LastName,
a.data.Address.Postcode AS Postcode,
a.data.ContactDetails.Email AS Email
FROM json_documents a;
FIRSTNAME LASTNAME POSTCODE EMAIL
--------------- --------------- ---------- -------------------------
Jayne Doe A12 34B jayne.doe@example.com
John Doe A12 34B john.doe@example.com
2 rows selected.
更多信息
TREAT AS JSON，这将使它变得更加简单 :) - Lukasz SzozdaSELECT JSON_VALUE(tab.col_json_data, '$.fieldName') AS fieldName FROM json_data_table tab; - Ahmad Nadeem由于您指定不想使用任何JSON库，如果格式固定，您可以将其强制转换为可解析的XML格式，从去掉花括号开始，将冒号替换为等号，并从每个名称/值对的第一部分中删除双引号：
select regexp_replace(regexp_replace(value, '(^{|}$)'),
'^"(.*)":(".*")($|,)', '\1=\2', 1, 0, 'm')
from tests_1;
REGEXP_REPLACE(REGEXP_REPLACE(VALUE,'(^{|}$)'),'^"(.*)":(".*")($|,)','\1=\2',1,0
--------------------------------------------------------------------------------
a="01/01/2015"
b="31/12/2015"
c="11111111111"
d="1111111111"
e="1234567890"
您可以将其用作虚拟XML节点的属性；将其转换为XMLType，然后可以使用XMLTable提取属性：
select x.a, x.b, x.c, x.d, x.e
from tests_1 t
cross join xmltable('/tmp'
passing xmltype('<tmp ' ||regexp_replace(regexp_replace(value, '(^{|}$)'),
'^"(.*)":(".*")($|,)', '\1=\2', 1, 0, 'm') || ' />')
columns a varchar2(10) path '@a',
b varchar2(10) path '@b',
c number path '@c',
d number path '@d',
e number path '@e'
) x;
A B C D E
---------- ---------- ------------- ------------- -------------
01/01/2015 31/12/2015 11111111111 1111111111 1234567890
在插入时，您可以将字符串转换为日期：
insert into test_2 (a, b, c, d, e)
select to_date(x.a, 'DD/MM/YYYY'), to_date(x.b, 'DD/MM/YYYY'), x.c, x.d, x.e
from tests_1 t
cross join xmltable('/tmp'
passing xmltype('<tmp ' || regexp_replace(regexp_replace(value, '(^{|}$)'),
'^"(.*)":(".*")($|,)', '\1=\2', 1, 0, 'm') || ' />')
columns a varchar2(10) path '@a',
b varchar2(10) path '@b',
c number path '@c',
d number path '@d',
e number path '@e'
) x;
select * from test_2;
A B C D E
---------- ---------- ------------- ------------- -------------
2015-01-01 2015-12-31 11111111111 1111111111 1234567890
select to_date(regexp_substr(value, '[^"]+', 1, 4), 'DD/MM/YYYY') as a,
to_date(regexp_substr(value, '[^"]+', 1, 8), 'DD/MM/YYYY') as b,
to_number(regexp_substr(value, '[^"]+', 1, 12)) as c,
to_number(regexp_substr(value, '[^"]+', 1, 16)) as d,
to_number(regexp_substr(value, '[^"]+', 1, 20)) as e
from tests_1;
A B C D E
---------- ---------- ------------- ------------- -------------
2015-01-01 2015-12-31 11111111111 1111111111 1234567890
从Oracle 18c开始，您可以使用TREAT AS JSON operator操作符：
JSON的SQL增强功能
您可以使用TREAT (... AS JSON)指定给定的SQL表达式返回JSON数据。
TREAT (... AS JSON)允许您指定来自给定SQL表达式的返回值将被视为JSON数据。这样的表达式可以包括PL/SQL函数调用和由SQL WITH子句指定的列。新的数据指南视图使得访问JSON字段的路径和类型信息变得容易，这些信息记录在支持索引的数据指南中。在LOB实例中返回生成和查询的JSON数据扩大了关系数据的使用范围。
此运算符提供了一种方法，告诉数据库一个VARCHAR2、BLOB、CLOB的内容应该被视为包含JSON。 这使得许多有用的功能成为可能，包括在没有"IS JSON"约束的数据库对象上使用"Simplified Syntax"。
在您的示例中：
create table Test_1(val CLOB);
create table Test_2(a date,b date,c number,d number, e number);
INSERT INTO Test_1(val)
VALUES('{
"a":"01/01/2015",
"b":"31/12/2015",
"c":"11111111111",
"d":"1111111111",
"e":"1234567890"
}');
INSERT INTO Test_2(a,b,c,d,e)
SELECT sub.val_as_json.a,
sub.val_as_json.b,
sub.val_as_json.c,
sub.val_as_json.d,
sub.val_as_json.e
FROM (SELECT TREAT(val as JSON) val_as_json
FROM Test_1) sub;
COMMIT;