进行接口开发的时候，如果涉及很多字段或者复杂的嵌套结构，为了避免人工拼凑json数据，下面是常用的几种json数据产生方式。
插件 pljson 方式
避免中文进行Unicode转换
修改：pljson_printer.escapeChar 如下：
function escapeChar(ch char) return varchar2 deterministic is
result varchar2(20);
begin
--backspace b = U+0008
--formfeed f = U+000C
--newline n = U+000A
--carret r = U+000D
--tabulator t = U+0009
result := ch;
case ch
when chr( 8) then result := '\b';
when chr( 9) then result := '\t';
when chr(10) then result := '\n';
when chr(12) then result := '\f';
when chr(13) then result := '\r';
when chr(34) then result := '\"';
when chr(47) then if (escape_solidus) then result := '\/'; end if;
when chr(92) then result := '\\';
/* WARNING: ascii() returns PLS_INTEGER and large unicode code points can be negative */
else /*if (ascii(ch) >= 0 and ascii(ch) < 32) then
result := '\u' || replace(substr(to_char(ascii(ch), 'XXXX'), 2, 4), ' ', '0');
elsif (ascii_output) then
result := replace(asciistr(ch), '\', '\u');
end if;*/ --yangshixian@2022-5-23 避免将中文转码
null;
end case;
return result;
end;
直接打印
表字段类型虽然是varchar2类型，但是如果存放的值全是数字的话，会自动被转换成number格式，即没有双引号了，可以通过加单引号，然后把单引号给替换掉的方式来避免这种隐式转换。
declare
ret pljson_list;
dyn_ref sys_refcursor;
begin
open dyn_ref for
select h.BATCH_NO,
h.INVOICE_TYPE,
h.SEQ,
h.DOC_SEQUENCE_NUM,
h.DOC_NUM,
h.DOC_TYPE,
h.APPROVE_EMPLOYEE_NO,
h.MAKER_EMPLOYEE_NO,
h.REQUESTOR_NO,
h.CURRENCY_CODE,
h.CURRENCY_CODE_BEG,
h.EXCHANGE_RATE,
h.VENDOR_NUM,
h.PAY_FLAG,
to_char(h.GL_DATE,'YYYY-MM-DD') GL_DATE,
to_char(h.BUSINESS_DATE,'YYYY-MM-DD') BUSINESS_DATE,
h.PREPAYMENT_FLAG,
h.INVOICE_AMOUNT,
h.INVOICE_AMOUNT_BEG,
h.SIG_FLAG,
TO_CHAR(h.FINISHED_DATE,'YYYY-MM-DD') FINISHED_DATE,
h.PAYMENT_CURRENCY_CODE,
/*h.ORGANIZATION_NAME,*/
''''||hou.short_code || '''' short_code,
h.SOURCE_LINE_ID,
h.CREATION_DATE,
h.ATTRIBUTE1,
h.ATTRIBUTE2,
h.ATTRIBUTE3,
h.ATTRIBUTE4,
h.ATTRIBUTE5,
h.ATTRIBUTE6,
cursor (select NVL(l.LINE_NUM,0) LINE_NUM,
l.IFACE_TYPE,
l.DR_CR_DIRECTOR,
l.AMOUNT,
l.AMOUNT_BEG,
l.INVOICE_COMMENTS,
'''' || l.COA_COMPANY || '''' COA_COMPANY,
'''' || l.COA_DEPT || '''' COA_DEPT,
'''' || l.COA_ACCOUNt || '''' COA_ACCOUNT,
l.COA_SUB_ACCOUNT,
'''' ||l.COA_PRODUCT || '''' COA_PRODUCT,
'''' ||l.COA_PROJECT || '''' COA_PROJECT,
'''' ||l.COA_INTERCOMPANY || '''' COA_INTERCOMPANY,
to_char(l.PAYMENT_DATE,'YYYY-MM-DD') PAYMENT_DATE,
l.PAYMENT_AMOUNT,
l.PAYMENT_BANK_NUM,
l.VERIFICATION_AMOUNT,
l.VERIFICATION_AMOUNT_BEG,
l.VERIFICATION_DOC_NUM,
l.SOURCE_LINE_ID,
l.ATTRIBUTE1,
l.ATTRIBUTE2,
l.ATTRIBUTE3,
l.ATTRIBUTE4,
l.ATTRIBUTE5,
l.ATTRIBUTE6
from cux_ap_amt_to_ebs_iface_l l
where h.header_id = l.header_id
order by l.line_id
) item_lines
from cux_ap_amt_to_ebs_iface_h h,hr_operating_units hou
where h.process_status = 'S'
and h.organization_name = hou.name
and h.header_id = 10149;
ret := pljson_util_pkg.ref_cursor_to_json(dyn_ref);
/*ret.print;*/
dbms_output.put_line(replace(ret.to_char(true),'''',null));
end;
转为clob 大容量
上面那个直接打印的可能超出打印缓冲设置,下面这个可以转为clob方式
create or replace function ufx_plson_get_json_from_sql (i_sql in varchar2)
return clob
as
-- Local variables here
tstjson_list pljson_list;
l_Result_json_clob clob;
begin
-- Test statements here
dbms_lob.createtemporary(l_Result_json_clob, true);
tstjson_list := pljson_util_pkg.sql_to_json(i_sql);
tstjson_list.to_clob(l_Result_json_clob);
return l_Result_json_clob;
exception
when others then raise;
end;
/
/* example */
select ufx_plson_get_json_from_sql (i_sql =>
q'[ select sysdate, systimestamp, user from dual]') sql_result_as_json
from dual;
oracle apex 方式
cursor 方式
有点：可以使实现嵌套打印
缺陷：如果字段是null值，则字段不显示，无法控制是否打印空置。
DECLARE
c sys_refcursor;
BEGIN
open c for select deptno,
dname,
cursor(select empno,
ename
from emp e
where e.deptno=d.deptno) emps
from dept d;
apex_json.open_object;
apex_json. write('departments', c);
apex_json.close_object;
END;
{ "departments":[
{"DEPTNO":10,
"DNAME":"ACCOUNTING",
"EMPS":[{"EMPNO":7839,"ENAME":"KING"}]},
...
,{"DEPTNO":40,"DNAME":"OPERATIONS","EMPS":null}] }
WRITE_CONTEXT Procedure
优点:通过write_context参数p_write_null 可以控制是否打印空值
缺点：If the query contains object type, collection or cursor columns, an error is raised. If the column is VARCHAR2 and the uppercase value is ‘TRUE’ or ‘FALSE’, boolean values are generated.
DECLARE
l_context apex_exec.t_context;
begin
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db,
p_sql_query => q'#select * from dept#' );
apex_json.open_object;
apex_json.write_context( p_name => 'departments', p_context => l_context);
apex_json.close_object;
end;
{ "departments":[
{ "DEPTNO":10 ,"DNAME":"ACCOUNTING" ,"LOC":"NEW YORK" }
,{ "DEPTNO":20 ,"DNAME":"RESEARCH" ,"LOC":"DALLAS" }
,{ "DEPTNO":30 ,"DNAME":"SALES" ,"LOC":"CHICAGO" }
,{ "DEPTNO":40 ,"DNAME":"OPERATIONS" ,"LOC":"BOSTON" } ] }
