1.前提条件,安装json的包
2.创建JSON_UTIL_PKG
CREATE OR REPLACE PACKAGE JSON_UTIL_PKG AUTHID CURRENT_USER AS
/*
Purpose: JSON utilities for PL/SQL
see http://ora-00001.blogspot.com/
Remarks:
Who Date Description
------ ---------- -------------------------------------
MBR 30.01.2010 Created
JKR 01.05.2010 Edited to fit in PL/JSON
JKR 19.01.2011 Newest stylesheet + bugfix handling
*/
-- generate JSON from REF Cursor
FUNCTION REF_CURSOR_TO_JSON(P_REF_CURSOR IN SYS_REFCURSOR,
P_MAX_ROWS IN NUMBER := NULL,
P_SKIP_ROWS IN NUMBER := NULL) RETURN JSON_LIST;
-- generate JSON from SQL statement
FUNCTION SQL_TO_JSON(P_SQL IN VARCHAR2, P_MAX_ROWS IN NUMBER := NULL, P_SKIP_ROWS IN NUMBER := NULL) RETURN JSON_LIST;
END JSON_UTIL_PKG;
create or replace package body json_util_pkg
as
scanner_exception exception;
pragma exception_init(scanner_exception, -20100);
parser_exception exception;
pragma exception_init(parser_exception, -20101);
/*
Purpose: JSON utilities for PL/SQL
Remarks:
Who Date Description
------ ---------- -------------------------------------
MBR 30.01.2010 Created
*/
g_json_null_object constant varchar2(20) := '{ }';
function get_xml_to_json_stylesheet return varchar2
as
begin
/*
Purpose: return XSLT stylesheet for XML to JSON transformation
Remarks: see http://code.google.com/p/xml2json-xslt/
Who Date Description
------ ---------- -------------------------------------
MBR 30.01.2010 Created
MBR 30.01.2010 Added fix for nulls
*/
return q'^<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="no" omit-xml-declaration="yes" method="text" encoding="UTF-8" media-type="text/x-json"/>
<xsl:strip-space elements="*"/>
<!--contant-->
<xsl:variable name="d">0123456789</xsl:variable>
<!-- ignore document text -->
<xsl:template match="text()[preceding-sibling::node() or following-sibling::node()]"/>
<!-- string -->
<xsl:template match="text()">
<xsl:call-template name="escape-string">
<xsl:with-param name="s" select="."/>
</xsl:call-template>
</xsl:template>
<!-- Main template for escaping strings; used by above template and for object-properties
Responsibilities: placed quotes around string, and chain up to next filter, escape-bs-string -->
<xsl:template name="escape-string">
<xsl:param name="s"/>
<xsl:text>"</xsl:text>
<xsl:call-template name="escape-bs-string">
<xsl:with-param name="s" select="$s"/>
</xsl:call-template>
<xsl:text>"</xsl:text>
</xsl:template>
<!-- Escape the backslash (\) before everything else. -->
<xsl:template name="escape-bs-string">
<xsl:param name="s"/>
<xsl:choose>
<xsl:when test="contains($s,'\')">
<xsl:call-template name="escape-quot-string">
<xsl:with-param name="s" select="concat(substring-before($s,'\'),'\\')"/>
</xsl:call-template>
<xsl:call-template name="escape-bs-string">
<xsl:with-param name="s" select="substring-after($s,'\')"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:call-template name="escape-quot-string">
<xsl:with-param name="s" select="$s"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<!-- Escape the double quote ("). -->
<xsl:template name="escape-quot-string">
<xsl:param name="s"/>
<xsl:choose>
<xsl:when test="contains($s,'"')">
<xsl:call-template name="encode-string">
<xsl:with-param name="s" select="concat(substring-before($s,'"'),'\"')"/>
</xsl:call-template>
<xsl:call-template name="escape-quot-string">
<xsl:with-param name="s" select="substring-after($s,'"')"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:call-template name="encode-string">
<xsl:with-param name="s" select="$s"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template name="encode-string">
<xsl:param name="s"/>
<xsl:choose>
<!-- tab -->
<xsl:when test="contains($s,' ')">
<xsl:call-template name="encode-string">
<xsl:with-param name="s" select="concat(substring-before($s,' '),'\t',substring-after($s,' '))"/>
</xsl:call-template>
</xsl:when>
<!-- line feed -->
<xsl:when test="contains($s,'
')">
<xsl:call-template name="encode-string">
<xsl:with-param name="s" select="concat(substring-before($s,'
'),'\n',substring-after($s,'
'))"/>
</xsl:call-template>
</xsl:when>
<!-- carriage return -->
<xsl:when test="contains($s,'
')">
<xsl:call-template name="encode-string">
<xsl:with-param name="s" select="concat(substring-before($s,'
'),'\r',substring-after($s,'
'))"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise><xsl:value-of select="$s"/></xsl:otherwise>
</xsl:choose>
</xsl:template>
<!-- number (no support for javascript mantissa) -->
<xsl:template match="text()[not(string(number())='NaN' or
(starts-with(.,'0' ) and . != '0' and
not(starts-with(.,'0.' ))) or
(starts-with(.,'-0' ) and . != '-0' and
not(starts-with(.,'-0.' )))
)]">
<xsl:value-of select="."/>
</xsl:template>
<!-- boolean, case-insensitive -->
<xsl:template match="text()[translate(.,'TRUE','true')='true']">true</xsl:template>
<xsl:template match="text()[translate(.,'FALSE','false')='false']">false</xsl:template>
<!-- object -->
<xsl:template match="*" name="base">
<xsl:if test="not(preceding-sibling::*)">{</xsl:if>
<xsl:call-template name="escape-string">
<xsl:with-param name="s" select="name()"/>
</xsl:call-template>
<xsl:text>:</xsl:text>
<!-- check type of node -->
<xsl:choose>
<!-- null nodes -->
<xsl:when test="count(child::node())=0">null</xsl:when>
<!-- other nodes -->
<xsl:otherwise>
<xsl:apply-templates select="child::node()"/>
</xsl:otherwise>
</xsl:choose>
<!-- end of type check -->
<xsl:if test="following-sibling::*">,</xsl:if>
<xsl:if test="not(following-sibling::*)">}</xsl:if>
</xsl:template>
<!-- array -->
<xsl:template match="*[count(../*[name(../*)=name(.)])=count(../*) and count(../*)>1]">
<xsl:if test="not(preceding-sibling::*)">[</xsl:if>
<xsl:choose>
<xsl:when test="not(child::node())">
<xsl:text>null</xsl:text>
</xsl:when>
<xsl:otherwise>
<xsl:apply-templates select="child::node()"/>
</xsl:otherwise>
</xsl:choose>
<xsl:if test="following-sibling::*">,</xsl:if>
<xsl:if test="not(following-sibling::*)">]</xsl:if>
</xsl:template>
<!-- convert root element to an anonymous container -->
<xsl:template match="/">
<xsl:apply-templates select="node()"/>
</xsl:template>
</xsl:stylesheet>^';
end get_xml_to_json_stylesheet;
function ref_cursor_to_json (p_ref_cursor in sys_refcursor,
p_max_rows in number := null,
p_skip_rows in number := null) return json_list
as
l_ctx dbms_xmlgen.ctxhandle;
l_num_rows pls_integer;
l_xml xmltype;
l_json xmltype;
l_returnvalue clob;
begin
/*
Purpose: generate JSON from REF Cursor
Remarks:
Who Date Description
------ ---------- -------------------------------------
MBR 30.01.2010 Created
JKR 01.05.2010 Edited to fit in PL/JSON
*/
l_ctx := dbms_xmlgen.newcontext (p_ref_cursor);
dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag);
-- for pagination
if p_max_rows is not null then
dbms_xmlgen.setmaxrows (l_ctx, p_max_rows);
end if;
if p_skip_rows is not null then
dbms_xmlgen.setskiprows (l_ctx, p_skip_rows);
end if;
-- get the XML content
l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none);
l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx);
dbms_xmlgen.closecontext (l_ctx);
close p_ref_cursor;
if l_num_rows > 0 then
-- perform the XSL transformation
l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet));
l_returnvalue := l_json.getclobval();
else
l_returnvalue := g_json_null_object;
end if;
l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode);
if(l_num_rows = 0) then
return json_list();
else
if(l_num_rows = 1) then
declare ret json_list := json_list();
begin
ret.append(
json(
json(l_returnvalue).get('ROWSET')
).get('ROW')
);
return ret;
end;
else
return json_list(json(l_returnvalue).get('ROWSET'));
end if;
end if;
exception
when scanner_exception then
dbms_output.put('Scanner problem with the following input: ');
dbms_output.put_line(l_returnvalue);
raise;
when parser_exception then
dbms_output.put('Parser problem with the following input: ');
dbms_output.put_line(l_returnvalue);
raise;
when others then raise;
end ref_cursor_to_json;
function sql_to_json (p_sql in varchar2,
p_max_rows in number := null,
p_skip_rows in number := null) return json_list
as
v_cur sys_refcursor;
begin
open v_cur for p_sql;
return ref_cursor_to_json(v_cur, p_max_rows, p_skip_rows);
end sql_to_json;
end json_util_pkg;
3. 测试
DECLARE
V_REFER SYS_REFCURSOR;
V_JSON_LIST JSON_LIST;
BEGIN
OPEN V_REFER FOR
SELECT * FROM EMP;
V_JSON_LIST := JSON_UTIL_PKG.REF_CURSOR_TO_JSON(V_REFER);
V_JSON_LIST.PRINT;
END;
[{
"EMPNO" : 7369,
"ENAME" : "SMITH",
"JOB" : "CLERK",
"MGR" : 7902,
"HIREDATE" : "17-DEC-80",
"SAL" : 800,
"COMM" : null,
"DEPTNO" : 20
}, {
"EMPNO" : 7499,
"ENAME" : "ALLEN",
"JOB" : "SALESMAN",
"MGR" : 7698,
"HIREDATE" : "20-FEB-81",
"SAL" : 1600,
"COMM" : 300,
"DEPTNO" : 30
}, {
"EMPNO" : 7521,
"ENAME" : "WARD",
"JOB" : "SALESMAN",
"MGR" : 7698,
"HIREDATE" : "22-FEB-81",
"SAL" : 1250,
"COMM" : 500,
"DEPTNO" : 30
}, {
"EMPNO" : 7566,
"ENAME" : "JONES",
"JOB" : "MANAGER",
"MGR" : 7839,
"HIREDATE" : "02-APR-81",
"SAL" : 2975,
"COMM" : null,
"DEPTNO" : 20
}, {
"EMPNO" : 7654,
"ENAME" : "MARTIN",
"JOB" : "SALESMAN",
"MGR" : 7698,
"HIREDATE" : "28-SEP-81",
"SAL" : 1250,
"COMM" : 1400,
"DEPTNO" : 30
}, {
"EMPNO" : 7698,
"ENAME" : "BLAKE",
"JOB" : "MANAGER",
"MGR" : 7839,
"HIREDATE" : "01-MAY-81",
"SAL" : 2850,
"COMM" : null,
"DEPTNO" : 30
}, {
"EMPNO" : 7782,
"ENAME" : "CLARK",
"JOB" : "MANAGER",
"MGR" : 7839,
"HIREDATE" : "09-JUN-81",
"SAL" : 2450,
"COMM" : null,
"DEPTNO" : 10
}, {
"EMPNO" : 7788,
"ENAME" : "SCOTT",
"JOB" : "ANALYST",
"MGR" : 7566,
"HIREDATE" : "19-APR-87",
"SAL" : 3000,
"COMM" : null,
"DEPTNO" : 20
}, {
"EMPNO" : 7839,
"ENAME" : "KING",
"JOB" : "PRESIDENT",
"MGR" : null,
"HIREDATE" : "17-NOV-81",
"SAL" : 5000,
"COMM" : null,
"DEPTNO" : 10
}, {
"EMPNO" : 7844,
"ENAME" : "TURNER",
"JOB" : "SALESMAN",
"MGR" : 7698,
"HIREDATE" : "08-SEP-81",
"SAL" : 1500,
"COMM" : 0,
"DEPTNO" : 30
}, {
"EMPNO" : 7876,
"ENAME" : "ADAMS",
"JOB" : "CLERK",
"MGR" : 7788,
"HIREDATE" : "23-MAY-87",
"SAL" : 1100,
"COMM" : null,
"DEPTNO" : 20
}, {
"EMPNO" : 7900,
"ENAME" : "JAMES",
"JOB" : "CLERK",
"MGR" : 7698,
"HIREDATE" : "03-DEC-81",
"SAL" : 950,
"COMM" : null,
"DEPTNO" : 30
}, {
"EMPNO" : 7902,
"ENAME" : "FORD",
"JOB" : "ANALYST",
"MGR" : 7566,
"HIREDATE" : "03-DEC-81",
"SAL" : 3000,
"COMM" : null,
"DEPTNO" : 20
}, {
"EMPNO" : 7934,
"ENAME" : "MILLER",
"JOB" : "CLERK",
"MGR" : 7782,
"HIREDATE" : "23-JAN-82",
"SAL" : 1300,
"COMM" : null,
"DEPTNO" : 10
}]
