Oracle 解析分隔符分隔的CLOB数据

askTom 2018-09-07

698

问题描述

CREATE TABLE T
(
DT_FIELD DATE,
SKEY NUMBER,
FIELD_VALUES CLOB
)
INSERT INTO T VALUES('06-SEP-18',10,'68|} 88026 |}ABC|}101010|}ADSD|}');
INSERT INTO T VALUES('07-SEP-18',11,'70|} 88027 |}DEF|}');
INSERT INTO T VALUES('07-SEP-18',12,'101|} 88028|}YYY|}101030|}');
INSERT INTO T VALUES('08-SEP-18',15,'102|} 88029 |}XXX|}101040|}');
INSERT INTO T VALUES('09-SEP-18',20,'501|} 88030 |}XYZ|}101050|}BBB|}111|}');
commit;
复制

T表中的数据和FIELD_VALUES字段由分隔符 “|}” 分隔，field_values列可以包含多个由分隔符分隔的值
这需要每天对整个数据进行自动化处理

DT_FIELD  SKEY   FIELD_VALUES
06-SEP-18  10  68|} 88026 |}ABC|}101010|}ADSD|}
07-SEP-18  11  70|} 88027 |}DEF|}
07-SEP-18  12  101|} 88028 |}YYY|}101030|}
08-SEP-18  15  102|} 88029 |}XXX|}101040|}
09-SEP-18  20  501|} 88030 |}XYZ|}101050|}BBB|}111|}
复制

PL/SQL的预期输出

DT_FIELD  SKEY  FIELD_1  FIELD_2  FIELD_3  FIELD_4  FIELD 5  FIELD6
06-SEP-18   10 68   88026   ABC   101010  ADSD
07-SEP-18  11 70    88027   DEF  
07-SEP-18  12 101    88028  YYY  101030
08-SEP-18  15 102    88029  XXX  101040
09-SEP-18  20 501    88030  XYZ  101050 BBB  111
复制

专家解答

感谢测试脚本。如果您对期望获得的字段有上限 (我在下面使用了6个)，那么枢轴应该可以解决问题

SQL> CREATE TABLE T
  2  (
  3  DT_FIELD DATE,
  4  SKEY NUMBER,
  5  FIELD_VALUES CLOB
  6  );

Table created.

SQL> INSERT INTO T VALUES('06-SEP-18',10,'68|} 88026 |}ABC|}101010|}ADSD|}');

1 row created.

SQL> INSERT INTO T VALUES('07-SEP-18',11,'70|} 88027 |}DEF|}');

1 row created.

SQL> INSERT INTO T VALUES('07-SEP-18',12,'101|} 88028|}YYY|}101030|}');

1 row created.

SQL> INSERT INTO T VALUES('08-SEP-18',15,'102|} 88029 |}XXX|}101040|}');

1 row created.

SQL> INSERT INTO T VALUES('09-SEP-18',20,'501|} 88030 |}XYZ|}101050|}BBB|}111|}');

1 row created.

SQL> commit;

Commit complete.

SQL> with
  2  fields_to_rows  as
  3  (
  4  select
  5        t.DT_FIELD,
  6        t.SKEY,
  7        c1.column_value as key,
  8        cast(regexp_substr(t.FIELD_VALUES, '[^\|\}]+', 1, c1.column_value) as varchar2(40)) as fname
  9      from
 10        t,
 11        table(cast(multiset(select level from dual connect by  level <= 0.5*length (regexp_replace(t.FIELD_VALUES, '[^\|\}]+'))) as sys.OdciNumberList)) c1
 12  )
 13  select * from fields_to_rows
 14  pivot ( max(fname) as colval for ( key ) in (1 as F1, 2 as F2, 3 as F3, 4 as F4, 5 as F5, 6 as F6 ));

DT_FIELD              SKEY F1_COLVAL    F2_COLVAL    F3_COLVAL    F4_COLVAL    F5_COLVAL    F6_COLVAL
--------------- ---------- ------------ ------------ ------------ ------------ ------------ ------------
08-SEP-18               15 102           88029       XXX          101040
06-SEP-18               10 68            88026       ABC          101010       ADSD
07-SEP-18               12 101           88028       YYY          101030
07-SEP-18               11 70            88027       DEF
09-SEP-18               20 501           88030       XYZ          101050       BBB          111

5 rows selected.

SQL>
SQL>
SQL>
复制