在oracle 12c中以JSON形式返回SQL查询的结果 [英] Return results of a sql query as JSON in oracle 12c

问题描述

背景

我需要从Oracle提取几千行并将其转换为JSON，以便在SlickGrid中使用. 目前，我正在获取PHP中的行，使用iconv将其从ISO转换为UTF-8，并使用json_encode导出到json.整个操作在数据库端大约需要1秒，而生成JSON则需要5秒.这是很长的路.

I need to fetch a few thousands rows from Oracle and convert them to JSON for use in SlickGrid. Currently I am fetching the rows in PHP, converting it from ISO to UTF-8 with iconv and exporting to json with json_encode. The whole operation takes about 1 second on DB side and 5 seconds to generate JSON. It is way to long.

问题

我已经了解到Oracle 12c支持JSON，但是我找不到我真正需要的东西.

I have read that Oracle 12c supports JSON, but I cannot find exactly what I need.

有没有办法以json格式返回标准sql查询的结果?

Is there a way to return the result of a standard sql query in a json format?

据说我想发出与此类似的查询:

supposedly I would like to issue a query similar to this:

SELECT * from table AS JSON

并收到类似于以下内容的有效json:

and receive a valid json similar to this:

[{"col1": "value1", "col2": 2}, {"col1": "valueOfRow2", "col2": 3}]

重要的是，我需要为我转义unicode序列，因为我在客户端使用ISO-8859-2字符集，而JSON必须位于UTF-8或转义序列中. /p>

An important thing is that I need to have the unicode sequences escaped for me, as I use ISO-8859-2 charset on the client side, and JSON have to be in either UTF-8 or have the sequences escaped.

推荐答案

Oracle 12c版本12.1.0.2(截至2014年11月11日的最新版本)添加了JSON支持: https://docs.oracle.com/database/121/NEWFT/chapter12102.htm#BGBGADCC

Oracle 12c version 12.1.0.2 (the latest version as of 11.11.2014) adds JSON support: https://docs.oracle.com/database/121/NEWFT/chapter12102.htm#BGBGADCC

自10月17日起开始提供. https://blogs.oracle.com/db/entry/oracle_database_12c_release_1

It's been available since October 17th. https://blogs.oracle.com/db/entry/oracle_database_12c_release_1

如果您无法使用该版本进行修补/使用，则有一个由Lewis Cunningham和Jonas Krogsboell编写的出色软件包:PL/JSON * http://pljson.sourceforge.net/

If you are unable to patch/work with that version there is an excellent package written by Lewis Cunningham and Jonas Krogsboell: PL/JSON * http://pljson.sourceforge.net/

这是一个非常棒的软件包(我已经在许多数据库安装中使用过它).

It's an excellent package (I have used it in numerous database installations).

其中包含的示例很好，涵盖了大多数情况.

The examples included are good and cover most scenarios.

declare 
  ret json;
begin
  ret := json_dyn.executeObject('select * from tab');
  ret.print;
end;
/

这篇关于在oracle 12c中以JSON形式返回SQL查询的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！