拆分多行CLOB列-Oracle PL/SQL

Question

我有一个表，其中包括一个CLOB字段，该字段的值由逗号分隔的行组成。在输出中，我希望每行都有一行，这些行以一个特定的值开始。我还想从性能上提取一些逗号分隔的值。

输入表（3行）：

id         my_clob
001        500,aaa,bbb
           500,ccc,ddd
           480,1,2,bad
           500,eee,fff
002        777,0,0,bad
003        500,yyy,zzz

id         my_clob          line_num    line           second_val
001        500,aaa,bbb      1           500,aaa,bbb    aaa
           500,ccc,ddd
           480,1,2,bad
           500,eee,fff
001        500,aaa,bbb      2           500,ccc,ddd    ccc
           500,ccc,ddd
           480,1,2,bad
           500,eee,fff
001        500,aaa,bbb      3           500,eee,fff    eee
           500,ccc,ddd
           480,1,2,bad
           500,eee,fff
003        500,yyy,zzz      1           500,yyy,zzz    yyy

SELECT 
   id
   ,my_clob
   ,level as line_num
   ,regexp_substr(my_clob,'^500,\S+', 1, level, 'm') as line 
   ,regexp_substr(
        regexp_substr(
            my_clob,'^500,\S+', 1, level, 'm'
        )
        ,'[^,]+', 1, 2
   ) as second_val
FROM tbl
CONNECT BY level <= regexp_count(my_clob, '^500,\S+', 1, 'm')
  and prior id = id
  and prior sys_guid() is not null

'm'将源字符串视为多行。Oracle将插入符号(^)和美元符号($)分别解释为源字符串中任意行的开始和结束

这些regexp_*(my_clob,'^500,\s+',1,m')有什么问题吗？或者更好的是，没有Regex还有更好的性能吗？

Answer 1

您可以使用regexp，如下所示：

SQL> -- sample data
SQL> with your_data(id,myclob) as
  2  (select 1, '500,aaa,bbb
  3             500,ccc,ddd
  4             480,1,2,bad
  5             500,eee,fff' from dual)
  6  -- Your query starts from here
  7  select id, myclob, line_num, lines as line,
  8  regexp_substr(lines,'[^,]+',1,2) as second_val
  9  from
 10    (select id, myclob, column_value as line_num,
 11  trim(regexp_substr(d.myclob,'.+',1,column_value,'m')) as lines
 12     from your_data d
 13     cross join table(cast(multiset(select level from dual
 14                                 connect by  level <= regexp_count(d.myclob,'$',1,'m'))
 15     as sys.OdciNumberList)) levels)
 16  where regexp_like(lines,'^[500]');

 ID MYCLOB                    LINE_NUM LINE            SECOND_VAL
--- ------------------------- -------- --------------- ----------
  1 500,aaa,bbb                      1 500,aaa,bbb     aaa
    500,ccc,ddd
    480,1,2,bad
    500,eee,fff

  1 500,aaa,bbb                      2 500,ccc,ddd     ccc
    500,ccc,ddd
    480,1,2,bad
    500,eee,fff

  1 500,aaa,bbb                      4 500,eee,fff     eee
    500,ccc,ddd
    480,1,2,bad
    500,eee,fff


SQL>