CREATE OR REPLACE PACKAGE BODY cux_json_util IS
PROCEDURE create_clob(clob_loc IN OUT NOCOPY CLOB) IS
BEGIN
dbms_lob.createtemporary(lob_loc => clob_loc,
cache => TRUE,
dur => dbms_lob.session);
dbms_lob.open(lob_loc => clob_loc,
open_mode => dbms_lob.lob_readwrite);
END;
PROCEDURE close_clob(clob_loc IN OUT NOCOPY CLOB) IS
BEGIN
IF clob_loc IS NOT NULL
AND (dbms_lob.isopen(clob_loc) = 1) THEN
dbms_lob.close(clob_loc);
dbms_lob.freetemporary(clob_loc);
END IF;
END;
--json特殊字符处理
FUNCTION escape_json(p_value IN VARCHAR2) RETURN VARCHAR2 IS
v_value VARCHAR2(32767);
BEGIN
IF p_value IS NOT NULL THEN
v_value := REPLACE(p_value,
'\',
'\\');
v_value := REPLACE(v_value,
'"',
'\"');
v_value := REPLACE(v_value,
chr(10),
'\n'); --换行
v_value := REPLACE(v_value,
chr(13),
'\r'); --回车
v_value := REPLACE(v_value,
chr(9),
'\t'); --tab符
END IF;
RETURN v_value;
END;
PROCEDURE put_key_value(clob_loc IN OUT NOCOPY CLOB,
p_name IN VARCHAR2, --描述
p_value IN VARCHAR2, --值
p_number_round IN NUMBER DEFAULT 2, --四舍五入位数
p_amt_format IN VARCHAR2 DEFAULT 'N') --金额格式化: FM999,999,999,999,999.00)
IS
l_res VARCHAR2(32676);
BEGIN
IF p_amt_format = 'Y' THEN
BEGIN
l_res := to_char(round(to_number(p_value),
p_number_round),
'FM999,999,999,999,990.00');
EXCEPTION
WHEN OTHERS THEN
l_res := p_value;
END;
ELSE
l_res := p_value;
END IF;
l_res := escape_json(l_res);
IF p_value IS NULL
OR p_value = chr(0)
OR p_value = '-99' THEN
l_res := '"' || p_name || '":null';
ELSIF p_value = '-99' THEN
l_res := '"' || p_name || '":';
ELSE
l_res := '"' || p_name || '":"' || l_res || '"';
END IF;
l_res := l_res || ',';
writetojsonclob(clob_loc,
l_res);
END;
PROCEDURE put_key_value_c(clob_loc IN OUT NOCOPY CLOB,
p_name IN VARCHAR2, --描述
p_value IN VARCHAR2, --值
p_number_round IN NUMBER DEFAULT 2, --四舍五入位数
p_amt_format IN VARCHAR2 DEFAULT 'N') --金额格式化: FM999,999,999,999,999.00)
IS
l_res VARCHAR2(32676);
BEGIN
IF p_amt_format = 'Y' THEN
BEGIN
l_res := to_char(round(to_number(p_value),
p_number_round),
'FM999,999,999,999,990.00');
EXCEPTION
WHEN OTHERS THEN
l_res := p_value;
END;
ELSE
l_res := p_value;
END IF;
l_res := escape_json(l_res);
IF p_value IS NULL
OR p_value = chr(0) THEN
l_res := '"' || p_name || '":null';
ELSE
l_res := '"' || p_name || '":"' || l_res || '"';
END IF;
l_res := l_res || ',';
writetojsonclob(clob_loc,
l_res);
END;
PROCEDURE put_key_value_n(clob_loc IN OUT NOCOPY CLOB,
p_name IN VARCHAR2, --描述
p_value IN NUMBER, --值
p_number_round IN NUMBER DEFAULT 2, --四舍五入位数
p_amt_format IN VARCHAR2 DEFAULT 'N') --金额格式化: FM999,999,999,999,999.00)
IS
l_res VARCHAR2(32676);
BEGIN
IF p_amt_format = 'Y' THEN
BEGIN
l_res := to_char(round(p_value,
p_number_round),
'FM999,999,999,999,990.00');
EXCEPTION
WHEN OTHERS THEN
l_res := p_value;
END;
ELSE
l_res := p_value;
END IF;
l_res := escape_json(l_res);
IF p_value IS NULL
OR p_value = 9.99e125 THEN
l_res := '"' || p_name || '":null';
ELSIF p_amt_format = 'Y' THEN
l_res := '"' || p_name || '":"' || l_res || '"';
ELSE
l_res := '"' || p_name || '":' || l_res || '';
END IF;
l_res := l_res || ',';
writetojsonclob(clob_loc,
l_res);
END;
PROCEDURE writetojsonclob(clob_loc IN OUT NOCOPY CLOB,
msg_string VARCHAR2) IS
pos INTEGER;
amt NUMBER;
BEGIN
--返回大对象等
pos := dbms_lob.getlength(clob_loc) + 1;
amt := length(msg_string);
dbms_lob.write(clob_loc,
amt,
pos,
msg_string);
END writetojsonclob;
PROCEDURE json_start(clob_loc IN OUT NOCOPY CLOB) IS
BEGIN
writetojsonclob(clob_loc,
'{');
END;
PROCEDURE json_end(clob_loc IN OUT NOCOPY CLOB) IS
BEGIN
clob_loc := rtrim(clob_loc,
',');
writetojsonclob(clob_loc,
'}');
END;
PROCEDURE obj_start(clob_loc IN OUT NOCOPY CLOB,
p_obj_name VARCHAR2) IS
BEGIN
writetojsonclob(clob_loc,
'"' || p_obj_name || '":{');
END;
PROCEDURE obj_end(clob_loc IN OUT NOCOPY CLOB) IS
BEGIN
clob_loc := rtrim(clob_loc,
',');
writetojsonclob(clob_loc,
'},');
END;
PROCEDURE list_start(clob_loc IN OUT NOCOPY CLOB,
p_list_name VARCHAR2) IS
BEGIN
writetojsonclob(clob_loc,
'"' || p_list_name || '":[');
END;
PROCEDURE list_end(clob_loc IN OUT NOCOPY CLOB) IS
BEGIN
clob_loc := rtrim(clob_loc,
',');
writetojsonclob(clob_loc,
'],');
END;
PROCEDURE list_r_start(clob_loc IN OUT NOCOPY CLOB) IS
BEGIN
writetojsonclob(clob_loc,
'{');
END;
PROCEDURE list_r_end(clob_loc IN OUT NOCOPY CLOB) IS
BEGIN
clob_loc := rtrim(clob_loc,
',');
writetojsonclob(clob_loc,
'},');
END;
FUNCTION ref_cursor_to_json(p_ref_cursor IN SYS_REFCURSOR) RETURN CLOB IS
l_json_data CLOB;
CURSOR lcr IS
SELECT t2.column_value.getrootelement() NAME,
extractvalue(t2.column_value,
'node()') VALUE,
t1.rn
FROM (SELECT rownum rn,
x.*
FROM TABLE(xmlsequence(p_ref_cursor)) x) t1,
TABLE(xmlsequence(extract(t1.column_value,
'/ROW/node()'))) t2;
l_before_rn NUMBER;
BEGIN
dbms_lob.createtemporary(lob_loc => l_json_data,
cache => TRUE,
dur => dbms_lob.session);
dbms_lob.open(lob_loc => l_json_data,
open_mode => dbms_lob.lob_readwrite);
cux_json_util.writetojsonclob(l_json_data,
'[');
l_before_rn := 0;
FOR lr IN lcr LOOP
IF l_before_rn <> lr.rn THEN
IF l_before_rn <> 0 THEN
cux_json_util.list_r_end(l_json_data);
END IF;
cux_json_util.list_r_start(l_json_data);
END IF;
IF lr.name LIKE 'N\_x0023\_%' ESCAPE '\' THEN
cux_json_util.put_key_value_n(l_json_data,
substr(lr.name,
9),
lr.value);
ELSE
cux_json_util.put_key_value(l_json_data,
lr.name,
lr.value);
END IF;
l_before_rn := lr.rn;
END LOOP;
l_json_data := rtrim(l_json_data,
',');
IF l_before_rn > 0 THEN
cux_json_util.writetojsonclob(l_json_data,
'}');
END IF;
cux_json_util.writetojsonclob(l_json_data,
']');
RETURN l_json_data;
END;
FUNCTION ref_cursor_to_json2(p_ref_cursor IN SYS_REFCURSOR) RETURN CLOB IS
l_json_data CLOB;
l_context dbms_xmlgen.ctxhandle;
l_xml_result CLOB;
parser xmlparser.parser;
parsedoc xmldom.domdocument;
row_list xmldom.domnodelist;
row_node xmldom.domnode;
attrname VARCHAR2(100) := NULL;
attrval VARCHAR2(4000) := NULL;
l_row_len NUMBER;
l_child_len NUMBER;
chilnodes xmldom.domnodelist;
BEGIN
--将游标数据转成xml
l_context := dbms_xmlgen.newcontext(p_ref_cursor);
--列空需要生成标记
dbms_xmlgen.setnullhandling(l_context,
dbms_xmlgen.empty_tag);
l_xml_result := dbms_xmlgen.getxml(l_context,
dbms_xmlgen.none);
dbms_xmlgen.closecontext(l_context);
--关闭游标
CLOSE p_ref_cursor;
create_clob(l_json_data);
cux_json_util.writetojsonclob(l_json_data,
'[');
--游标数据不为空处理数据
IF l_xml_result IS NOT NULL THEN
--解析xml生成json
parser := xmlparser.newparser;
xmlparser.parseclob(parser,
l_xml_result);
parsedoc := xmlparser.getdocument(parser);
-- 释放解析器实例
xmlparser.freeparser(parser);
row_list := xmldom.getelementsbytagname(parsedoc,
'ROW');
l_row_len := xmldom.getlength(row_list);
--遍历所有ROW元素
FOR i IN 0 .. l_row_len - 1 LOOP
cux_json_util.list_r_start(l_json_data);
row_node := xmldom.item(row_list,
i);
chilnodes := xmldom.getchildnodes(row_node);
l_child_len := xmldom.getlength(chilnodes);
--子元素
FOR j IN 0 .. l_child_len - 1 LOOP
attrval := xmldom.getnodevalue(xmldom.getfirstchild(xmldom.item(chilnodes,
j)));
attrname := xmldom.getnodename(xmldom.item(chilnodes,
j));
-- N#开头 表示数字类型
IF attrname LIKE 'N\_x0023\_%' ESCAPE '\' THEN
cux_json_util.put_key_value_n(l_json_data,
substr(attrname,
9),
attrval);
ELSE
cux_json_util.put_key_value(l_json_data,
attrname,
attrval);
END IF;
END LOOP;
cux_json_util.list_r_end(l_json_data);
END LOOP;
--释放
xmldom.freedocument(parsedoc);
END IF;
l_json_data := rtrim(l_json_data,
',');
cux_json_util.writetojsonclob(l_json_data,
']');
RETURN l_json_data;
END;
FUNCTION ref_cursor_to_json3(p_ref_cursor IN SYS_REFCURSOR,
p_page_size NUMBER,
p_page_index NUMBER) RETURN CLOB IS
l_json_data CLOB;
l_context dbms_xmlgen.ctxhandle;
l_xml_result CLOB;
parser xmlparser.parser;
parsedoc xmldom.domdocument;
row_list xmldom.domnodelist;
row_node xmldom.domnode;
attrname VARCHAR2(100) := NULL;
attrval VARCHAR2(4000) := NULL;
l_row_len NUMBER;
l_child_len NUMBER;
chilnodes xmldom.domnodelist;
BEGIN
--将游标数据转成xml
l_context := dbms_xmlgen.newcontext(p_ref_cursor);
--列空需要生成标记
dbms_xmlgen.setnullhandling(l_context,
dbms_xmlgen.empty_tag);
dbms_xmlgen.setmaxrows(l_context,
maxrows => p_page_size);
IF p_page_index IS NOT NULL THEN
dbms_xmlgen.setskiprows(ctx => l_context,
skiprows => ((p_page_index - 1) * p_page_size));
END IF;
l_xml_result := dbms_xmlgen.getxml(l_context,
dbms_xmlgen.none);
dbms_xmlgen.closecontext(l_context);
--关闭游标
CLOSE p_ref_cursor;
create_clob(l_json_data);
cux_json_util.writetojsonclob(l_json_data,
'[');
--游标数据不为空处理数据
IF l_xml_result IS NOT NULL THEN
--解析xml生成json
parser := xmlparser.newparser;
xmlparser.parseclob(parser,
l_xml_result);
parsedoc := xmlparser.getdocument(parser);
-- 释放解析器实例
xmlparser.freeparser(parser);
row_list := xmldom.getelementsbytagname(parsedoc,
'ROW');
l_row_len := xmldom.getlength(row_list);
--遍历所有ROW元素
FOR i IN 0 .. l_row_len - 1 LOOP
cux_json_util.list_r_start(l_json_data);
row_node := xmldom.item(row_list,
i);
chilnodes := xmldom.getchildnodes(row_node);
l_child_len := xmldom.getlength(chilnodes);
--子元素
FOR j IN 0 .. l_child_len - 1 LOOP
attrval := xmldom.getnodevalue(xmldom.getfirstchild(xmldom.item(chilnodes,
j)));
attrname := xmldom.getnodename(xmldom.item(chilnodes,
j));
-- N#开头 表示数字类型
IF attrname LIKE 'N\_x0023\_%' ESCAPE '\' THEN
cux_json_util.put_key_value_n(l_json_data,
substr(attrname,
9),
attrval);
ELSE
cux_json_util.put_key_value(l_json_data,
attrname,
attrval);
END IF;
END LOOP;
cux_json_util.list_r_end(l_json_data);
END LOOP;
--释放
xmldom.freedocument(parsedoc);
END IF;
l_json_data := rtrim(l_json_data,
',');
cux_json_util.writetojsonclob(l_json_data,
']');
RETURN l_json_data;
END;
END cux_json_util;
DECLARE
l_sql CLOB;
l_curid NUMBER;
l_tmp_str VARCHAR2(2000);
l_cur SYS_REFCURSOR;
l_json_clob CLOB;
BEGIN
dbms_lob.createtemporary(l_sql,
TRUE);
l_tmp_str := 'select * from fnd_user where rownum<=3 ';
dbms_lob.append(dest_lob => l_sql,
src_lob => l_tmp_str);
OPEN l_cur FOR l_sql;
l_json_clob := cux_json_util.ref_cursor_to_json2(p_ref_cursor => l_cur);
dbms_output.put_line(dbms_lob.substr(l_json_clob,
30000,
1));
END;