CREATE OR REPLACE PACKAGE BODY FYANG.PKG_JSON_PLSQL
is
c_crlf varchar2(2) := chr(13) || chr(10);
c_mdl varchar2(30) := 'PKG_JSON_PLSQL' ;
c_dbg_mode boolean := false;
procedure append (p_src in out varchar2, p_opnd in varchar2)
is
begin
p_src := p_src || p_opnd;
dbms_output.put(p_opnd);
end;
procedure appendln (p_src in out varchar2, p_opnd in varchar2)
is
begin
p_src := p_src || p_opnd || c_crlf;
dbms_output.put_line(p_opnd);
end;
function quote (p_src in varchar2) return varchar2
is
begin
return '"' || p_src || '"';
end;
function js_naming (p_pls_name in varchar2) return varchar2
is
v_rst varchar2(2000) := p_pls_name;
begin
v_rst := lower(v_rst);
v_rst := replace(v_rst, '_a', 'A');
v_rst := replace(v_rst, '_b', 'B');
v_rst := replace(v_rst, '_c', 'C');
v_rst := replace(v_rst, '_d', 'D');
v_rst := replace(v_rst, '_e', 'E');
v_rst := replace(v_rst, '_f', 'F');
v_rst := replace(v_rst, '_g', 'G');
v_rst := replace(v_rst, '_h', 'H');
v_rst := replace(v_rst, '_i', 'I');
v_rst := replace(v_rst, '_j', 'J');
v_rst := replace(v_rst, '_k', 'K');
v_rst := replace(v_rst, '_l', 'L');
v_rst := replace(v_rst, '_m', 'M');
v_rst := replace(v_rst, '_n', 'N');
v_rst := replace(v_rst, '_o', 'O');
v_rst := replace(v_rst, '_p', 'P');
v_rst := replace(v_rst, '_q', 'Q');
v_rst := replace(v_rst, '_r', 'R');
v_rst := replace(v_rst, '_s', 'S');
v_rst := replace(v_rst, '_t', 'T');
v_rst := replace(v_rst, '_u', 'U');
v_rst := replace(v_rst, '_v', 'V');
v_rst := replace(v_rst, '_w', 'W');
v_rst := replace(v_rst, '_x', 'X');
v_rst := replace(v_rst, '_y', 'Y');
v_rst := replace(v_rst, '_z', 'Z');
return v_rst;
end;
procedure dbg (p_msg in varchar2)
is
begin
if c_dbg_mode then
dbms_output.put_line ('[DBG] '
|| to_char(systimestamp, 'YYYY/MM/DD HH24:MI:SSXFF')
|| ' ' || c_mdl
|| ' ' || p_msg);
end if;
end;
procedure tbl2json (p_json out varchar2, p_owner in varchar2, p_name in varchar2, p_table in varchar2, p_cond in varchar2)
is
-- prepare parameters
v_json varchar2(2000) := '';
v_owner all_tables.owner%type := nvl(upper(p_owner), user);
v_name all_tables.table_name%type := p_name;
v_table all_tables.table_name%type := upper(p_table);
v_cond varchar2(2000) := p_cond;
v_first_row boolean := true;
v_first_column boolean := true;
begin
if v_cond is not null then
v_cond := ' where ' || v_cond;
end if;
-- begin log
dbg ('begin v_json = ' || v_json
|| ', v_owner = ' || v_owner
|| ', v_name = ' || v_name
|| ', v_table = ' || v_table
|| ' v_cond = ' || v_cond);
appendln (v_json, 'var ' || v_name || ' = {');
appendln (v_json, '"name" : "' || v_name ||'", ');
appendln (v_json, '"rows" : [');
v_first_row := true;
for y in (
select person_no, person_name, modify_ts
from m01_person) loop
dbg ('fetch row: person_no = ' || y.person_no || ', person_name = ' || y.person_name);
if v_first_row = true then
v_first_row := false;
else
appendln (v_json, ', ');
end if;
append (v_json, '{');
v_first_column := true;
for x in (
select column_name
from all_tab_columns
where table_name = v_table
and owner = v_owner
order by column_id) loop
dbg (js_naming(x.column_name));
if v_first_column = true then
v_first_column := false;
else
append (v_json, ', ');
end if;
if x.column_name = upper('person_no') then
append (v_json, '"' || js_naming(x.column_name) || '" : "' || y.person_no || '"');
elsif x.column_name = upper('person_name') then
append (v_json, '"' || js_naming(x.column_name) || '" : "' || y.person_name || '"');
elsif x.column_name = upper('modify_ts') then
append (v_json, '"' || js_naming(x.column_name) || '" : ""');
end if;
end loop;
append (v_json, '}');
end loop;
appendln (v_json, '');
appendln (v_json, ']');
appendln (v_json, '}');
dbg ('end');
p_json := v_json;
end;
procedure cursor2json (p_json out varchar2, p_name in varchar2, p_sql in varchar2)
is
-- prepare parameters
v_json varchar2(2000) := '';
v_name all_tables.table_name%type := p_name;
v_sql varchar2(2000) := p_sql;
v_first_row boolean := true;
v_first_column boolean := true;
c number;
d number;
col_cnt pls_integer;
f boolean;
rec_tab dbms_sql.desc_tab;
col_num number;
type row_typ is table of varchar2(2000);
v_row row_typ := row_typ();
i number;
j number;
begin
-- begin log
dbg ('begin v_json = ' || v_json
|| ', v_name = ' || v_name
|| ', v_sql = ' || v_sql);
c := dbms_sql.open_cursor;
dbms_sql.parse(c, p_sql, dbms_sql.native);
d := dbms_sql.execute(c);
dbms_sql.describe_columns(c, col_cnt, rec_tab);
for i in 1..col_cnt loop
if not v_row.exists(i) then
v_row.extend;
end if;
dbms_sql.define_column(c, i, v_row(i), 2000);
end loop;
appendln (v_json, 'var ' || v_name || ' = {');
appendln (v_json, '"name" : "' || v_name ||'", ');
appendln (v_json, '"rows" : [');
v_first_row := true;
j := 0;
loop
j := j + 1;
if dbms_sql.fetch_rows(c) > 0 then
dbg ('processing row ' || j);
if v_first_row = true then
v_first_row := false;
else
appendln (v_json, ', ');
end if;
append (v_json, '{');
v_first_column := true;
for i in 1..col_cnt loop
dbg (js_naming(rec_tab(i).col_name));
if v_first_column = true then
v_first_column := false;
else
append (v_json, ', ');
end if;
dbms_sql.column_value(c, i, v_row(i));
append (v_json, '"' || js_naming(rec_tab(i).col_name) || '" : "' || v_row(i) || '"');
end loop;
append (v_json, '}');
else
exit;
end if;
end loop;
dbms_sql.close_cursor(c);
appendln (v_json, '');
appendln (v_json, ']');
appendln (v_json, '}');
dbg ('end');
p_json := v_json;
end;
procedure json2tbl (p_json in varchar2, p_owner in varchar2, p_name out varchar2, p_table out varchar2, p_cond out varchar2)
is
begin
null;
end;
-- exec pkg_json_plsql.test_main('cursor')
procedure test_main (p_pattern varchar2 default null)
is
v_json varchar2(2000);
begin
dbms_output.enable(1000000);
if p_pattern is null then
tbl2json (v_json, null, 'person', 'm01_person', null);
dbms_output.put_line('result');
dbms_output.put_line('-------------------------------------');
--dbms_output.put_line(v_json);
elsif p_pattern = 'cursor' then
cursor2json (v_json, 'person', 'select * from m01_person');
dbms_output.put_line('result');
dbms_output.put_line('-------------------------------------');
--dbms_output.put_line(v_json);
end if;
end;
end;
/
No comments:
Post a Comment