Oracle 将clob数据转换为JSON格式

我的表键和VAL中有两个clob列。

关键列数据:
动作 | COCODE | SNCODE | SPCODE | PARAMETER_ID | PARAMETER_VALUE

VAL列数据:
添加 | CONTR0019209052 | 123 | 456 | 1 | abc | 111 | zzz | 222 | 999 | 333 | 888 | 666 | tttt

我想转换这个数据在JSON格式像这样:

“我的”: [
{"action": "Add"，
"SNCODE | SPCODE": "123 | 456"，
“参数”: [
{
"参数 _ id":"1"，
"参数 _ 值":"abc"
}，
{
"PARAMETER_ID":"111"，
"参数 _ 值":"zzz"
}
]
}，
{
"action": "Add"，
"SNCODE | SPCODE": "222 | 999"

}，
{
"action": "Add"，
"SNCODE | SPCODE": "333 | 888"，
“参数”: [
{
"PARAMETER_ID":"666"，
"参数 _ 值":"tttt"
}
]
}
]


我如何实现这个SNCODE & SPCODE，参数可以出现很多次。

好的，首先检查这里的帖子

https://asktom.oracle.com/pls/apex/asktom.search?tag=parsing-a-clob-field-which-contains-csv-data

这是如何解析包含CSV数据的clob的简单演示。

然后我拿了得到的GTT，用一些简单的字符串处理来解析它

SQL>
SQL> create table T ( id int, c1 clob, c2 clob );

Table created.

SQL>
SQL> insert into t
  2  values (1,
  3    'ACTION|COCODE|SNCODE|SPCODE|PARAMETER_ID|PARAMETER_VALUE|PARAMETER_ID|PARAMETER_VALUE|SNCODE|SPCODE|SNCODE|SPCODE|PARAMETER_ID|PARAMETER_VALUE',
  4    'Add|CONTR0019209052|123|456|1|abc|111|zzz|222|999|333|888|666|tttt');

1 row created.

SQL>
SQL> create global temporary table GTT (id int, seq int, col varchar2(100), val varchar2(100) )
  2  on commit preserve rows;

Table created.

SQL>
SQL> create sequence seq;

Sequence created.

SQL>
SQL>  set serverout on
SQL>  declare
  2     l_big_chunk varchar2(32767);
  3     l_leftover varchar2(200);
  4     l_offset pls_integer;
  5     l_len pls_integer;
  6
  7     type string_tab is table of varchar2(100) index by pls_integer;
  8     l_cols string_tab;
  9     l_vals  string_tab;
 10
 11     l_delim pls_integer;
 12   begin
 13   for i in ( select * from t  )
 14   loop
 15
 16     --
 17     -- c1 first
 18     --
 19     l_offset := 1;
 20     loop
 21       l_big_chunk := dbms_lob.substr(i.c1,32700,l_offset);
 22       l_len := length(l_big_chunk);
 23       l_offset := l_offset + l_len;
 24       l_big_chunk := l_leftover || l_big_chunk;
 25
 26       loop
 27         l_delim := instr(l_big_chunk,'|');
 28         exit when l_delim = 0 or l_big_chunk is null;
 29         l_cols(l_cols.count+1) := substr(l_big_chunk,1,l_delim-1);
 30         l_big_chunk := substr(l_big_chunk,l_delim+1);
 31       end loop;
 32       l_leftover := l_big_chunk;
 33
 34       exit when l_len < 32700;
 35     end loop;
 36     if l_big_chunk is not null then
 37       l_cols(l_cols.count+1) := l_big_chunk;
 38     end if;
 39     --
 40     -- then same for c2
 41     --
 42
 43     l_offset := 1;
 44     l_leftover := null;
 45     loop
 46       l_big_chunk := dbms_lob.substr(i.c2,32700,l_offset);
 47       l_len := length(l_big_chunk);
 48       l_offset := l_offset + l_len;
 49       l_big_chunk := l_leftover || l_big_chunk;
 50
 51       loop
 52         l_delim := instr(l_big_chunk,'|');
 53         exit when l_delim = 0 or l_big_chunk is null;
 54         l_vals(l_vals.count+1) := substr(l_big_chunk,1,l_delim-1);
 55         l_big_chunk := substr(l_big_chunk,l_delim+1);
 56       end loop;
 57       l_leftover := l_big_chunk;
 58
 59       exit when l_len < 32700;
 60     end loop;
 61     if l_big_chunk is not null then
 62          l_vals(l_vals.count+1) := l_big_chunk;
 63     end if;
 64
 65     forall g in 1 .. l_cols.count
 66        insert into gtt values (i.id,seq.nextval,l_cols(g), l_vals(g) ) ;
 67    l_cols.delete;
 68    l_vals.delete;
 69   end loop;
 70   end;
 71   /

PL/SQL procedure successfully completed.

SQL>
SQL> col col format a40
SQL> col val format a40
SQL> set lines 150
SQL> set long 5000
SQL> set longchunksize 1000
SQL> select * from gtt;

        ID        SEQ COL                                      VAL
---------- ---------- ---------------------------------------- ----------------------------------------
         1          1 ACTION                                   Add
         1          2 COCODE                                   CONTR0019209052
         1          3 SNCODE                                   123
         1          4 SPCODE                                   456
         1          5 PARAMETER_ID                             1
         1          6 PARAMETER_VALUE                          abc
         1          7 PARAMETER_ID                             111
         1          8 PARAMETER_VALUE                          zzz
         1          9 SNCODE                                   222
         1         10 SPCODE                                   999
         1         11 SNCODE                                   333
         1         12 SPCODE                                   888
         1         13 PARAMETER_ID                             666
         1         14 PARAMETER_VALUE                          tttt

14 rows selected.

SQL>
SQL> set serverout on
SQL> declare
  2    c clob;
  3    sncode varchar2(100);
  4    eol varchar2(1) := chr(10);
  5    param varchar2(100);
  6  begin
  7    for i in ( select g.*,
  8                 row_number() over ( partition by id order by seq) r,
  9                 count(*) over ( partition by id ) as cnt
 10               from gtt g
 11               order by 1,2 )
 12    loop
 13      if i.col = 'ACTION' then
 14        c := '"my": ['||eol||
 15             '{"action": "'||i.val||'",'||eol;
 16      elsif i.col = 'SNCODE' then
 17         sncode := i.val;
 18      elsif i.col = 'SPCODE' then
 19        c := c ||'"SNCODE|SPCODE": "'||sncode||'|'||i.val||'",'||eol||
 20             '"parameter": ['||eol;
 21      elsif i.col = 'PARAMETER_ID' then
 22          param := i.val;
 23      elsif i.col = 'PARAMETER_VALUE' then
 24         c := c || '{'||eol||
 25                    '"PARAMETER_ID":"'||param||'",'||eol||
 26                    '"PARAMETER_VALUE":"'||i.val||'"'||eol||
 27                    '}'||case when i.r < i.cnt then ',' else ']}]' end||eol;
 28      end if;
 29
 30      if i.r = i.cnt then
 31         dbms_output.put_line(c);
 32      end if;
 33    end loop;
 34  end;
 35  /
"my": [
{"action": "Add",
"SNCODE|SPCODE": "123|456",
"parameter":
[
{
"PARAMETER_ID":"1",
"PARAMETER_VALUE":"abc"
},
{
"PARAMETER_ID":"111",
"PARAMETER_VALUE":"zzz"
},
"SNCODE|SPCODE": "222|999",
"parameter":
[
"SNCODE|SPCODE": "333|888",
"parameter": [
{
"PARAMETER_ID":"666",
"PARAMETER_VALUE":"tttt"
}]}]


PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>
复制