我必须插入表 2 字段(第一个主键(关于文章),第二个涉及它们的大小(这些文章)。
在源环境中,我有表、主键(TK 文章)和第二个字段的大小串联。但是,我必须将 TK 文章和文章的几种大小插入到目标表中。
例如,
来源:
ART SIZE**
1 | 28/30
2 | 30/32
3 | Size 10/Size 12/Size 14/Size 14
Target:
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,'[^/]+',1,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, 1, l_dlm_pos - 1));
l_src_str := substr(l_src_str, l_dlm_pos + 1);
end case;
end loop;
return;
end;
end;
-
使用正则表达式函数进行 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, '[^/]+', 1, o.ocr)) as res
from tb_splitStr s
cross join ocrs o
Result:
-- 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, '[^/]+', 1, 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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)