Oracle12以后内置了查询结果转JSON的函数可以对结果进行转换，可以省去在程序中吃内存的操作。
JSON_OBJECT
JSON_OBJECTAGG
JSON_ARRaY
JSON_ARRaYAGG
SELECT
JSON_OBJECT(
key 'id' value PT_SN || '-' || HIS_INFO_SN,
key 'ptName' value convert( PT_NAME , 'AL32UTF8' ),
key 'investors' value (
select JSON_ARRaYAGG( JSON_OBJECT(
key 'id' value convert( i.inv_sn , 'AL32UTF8' ),
key 'invName' value convert( i.inv_Name , 'AL32UTF8' )
absent on null
) ) FROM ENTERPRISE_INVESTOR i WHERE i.pt_sn = b.pt_sn
)
,
key 'organizes' value (
select JSON_ARRaYagg( JSON_OBJECT(
key 'id' value convert( o.INST_SN , 'AL32UTF8' ),
key 'name' value convert( o.name , 'AL32UTF8' )
absent on null
)) FROM ENTERPRISE_Organize o WHERE o.pt_sn = b.pt_sn
)
absent on null
)
FROM
ENTERPRISE_BASEINFO b where b.pt_sn = 290359
得到结果
{
"id":"290359-727969",
"busiScope":"汽车配件、摩托车配件",
"ptName":"四川省汽车成都供应公司",
"lastUpdate":"2012-07-07 10:45:37",
"investors":[
{
"id":"2260679",
"invName":"四川省汽车成都供应公司"
}
],
"organizes":[
{
"id":"3146159",
"name":"马莉"
},
{
"id":"2385929",
"name":"马莉"
}
]
}
查询结果乱码：
如果在navicat等工具中查出结果有乱码，尝试将工具的编码修改后再次进行验证。
