Oracle SQL：使用REGEXP_SUBSTR表达式的插入查询非常长(拆分字符

在源环境中,我有表,主键(TK文章)和大小到第二个字段的串联.但是,我必须插入目标表,TK文章和Artcles的几个大小.
例如,

资源：

ART        SIZE**                                      
1        |  28/30   
2        |  30/32   
3        | Size 10/Size 12/Size 14/Size 14

目标：

ART         Size
1        |   28  
1        |   30                   
2        |   30            
2        |   32             
3        |  Size 10         
3        |  Size 12       
3        |  Size 14      
3        |  Size 16

困难在于知道该领域包含多少’/’？

我已经提出了一个问题

SELECT ART,REGEXP_SUBSTR(SIZE,'[^/]+',1,level)
FROM TABLLE
CONNECT BY REGEXP_SUBSTR(SIZE,level) IS NOT NULL;

选择事务工作并在46秒内显示结果.但是TABLE有100 000行,插入事务太长而且不起作用.

有人可以在这一点上帮助我吗？

谢谢&问候

-- table with 100000 rows
create table Tb_SplitStr(col1,col2) as
  select level,'Size 10/Size 12/Size 14/Size 14/Size 15/Size 16/Size 17'
   from dual
  connect by level <= 100000

> PL / SQL包：

create or replace package Split_Pkg as
  type T_StrList is table of varchar2(1000);
  function Str_Split(
     p_str in varchar2,p_dlm in varchar2
  ) return T_StrList pipelined;
end;

create or replace package body Split_Pkg as
  function Str_Split(
     p_str in varchar2,p_dlm in varchar2
  ) return T_StrList pipelined
  is
     l_src_str  varchar2(1000) default p_str;
     l_dlm_pos  number;
  begin
     while l_src_str is not null
     loop
        l_dlm_pos := instr(l_src_str,p_dlm);
        case
          when l_dlm_pos = 0
          then pipe row (l_src_str);
               l_src_str := '';
          else pipe row(substr(l_src_str,l_dlm_pos - 1));
               l_src_str := substr(l_src_str,l_dlm_pos + 1);
        end case;
     end loop;
     return;
  end; 
end;

>带有regexp函数的SQL查询：

with ocrs(ocr) as(
   select level
     from ( select max(regexp_count(col2,'[^/]+')) as mx
              from tb_splitStr) t
   connect by level <= t.mx
)
select count(regexp_substr(s.col2,o.ocr)) as res
  from tb_splitStr s
   cross join ocrs o

结果：

-- SQL with regexp
SQL> with ocrs(ocr) as(
  2    select level
  3     from ( select max(regexp_count(col2,'[^/]+')) as mx
  4              from tb_splitStr) t
  5    connect by level <= t.mx
  6  )
  7  select count(regexp_substr(s.col2,o.ocr)) as res
  8    from tb_splitStr s
  9     cross join ocrs o
 10  ;

Res
------------------------------
                        700000
Executed in 4.093 seconds

SQL> /

Res
------------------------------
                        700000
Executed in 3.812 seconds



--Query with pipelined table function  
SQL> select count(*)
  2    from Tb_SplitStr s
  3    cross join table(split_pkg.Str_Split(s.col2,'/'))
  4  ;

 COUNT(*)
----------
    700000
Executed in 2.469 seconds

SQL> /

COUNT(*)
----------
    700000
Executed in 2.406 seconds

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!