Oracle 根据重复计数将字符串分解为重复的行

嗨，康纳，克里斯，
您能否看一下下面的表格结构。

drop table acct_test;

create table acct_test
(
account_no varchar2(50 char),
country varchar2(50 char),
no_of_txn number(9),
sys_n_status varchar2(1000 char)
);

insert into acct_test values ('ACCT00001','SG',2,'ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}');

insert into acct_test values ('ACCT00002','KL',3,'ABC-NEW{3};ABC-PROCESS{2}');
commit;

对于每个帐户 _ 否，我需要
1.将字符串sys_n_status分成行，例如ABC-NEW{2};ABC-PROCESS{3};XYZ: 虚拟 {1}
ABC NEW
ABC NEW
ABC流程
ABC流程
ABC流程
XYZ假人
2.没有乘以 #1行与no_of_txn和生成IDENTIFIER_STRING每次迭代如下:

ACCT_NO   COUNTRY  SOURCE  STATUS    IDENTIFIER_STRING
--------- -------  ------  -------   -----------------
ACCT00001 SG       ABC      NEW       TXN0000000001
ACCT00001 SG       ABC      NEW       TXN0000000001
ACCT00001 SG       ABC      PROCESS   TXN0000000001
ACCT00001 SG       ABC      PROCESS   TXN0000000001
ACCT00001 SG       ABC      PROCESS   TXN0000000001
ACCT00001 SG       XYZ      DUMMY     TXN0000000001

ACCT00001 SG       ABC      NEW       TXN0000000002
ACCT00001 SG       ABC      NEW       TXN0000000002
ACCT00001 SG       ABC      PROCESS   TXN0000000002
ACCT00001 SG       ABC      PROCESS   TXN0000000002
ACCT00001 SG       ABC      PROCESS   TXN0000000002
ACCT00001 SG       XYZ      DUMMY     TXN0000000002

ACCT00002 KL       ABC      NEW       TXN0000000003
ACCT00002 KL       ABC      NEW       TXN0000000003
ACCT00002 KL       ABC      NEW       TXN0000000003
ACCT00002 KL       ABC      PROCESS   TXN0000000003
ACCT00002 KL       ABC      PROCESS   TXN0000000003

ACCT00002 KL       ABC      NEW       TXN0000000004
ACCT00002 KL       ABC      NEW       TXN0000000004
ACCT00002 KL       ABC      NEW       TXN0000000004
ACCT00002 KL       ABC      PROCESS   TXN0000000004
ACCT00002 KL       ABC      PROCESS   TXN0000000004

ACCT00002 KL       ABC      NEW       TXN0000000005
ACCT00002 KL       ABC      NEW       TXN0000000005
ACCT00002 KL       ABC      NEW       TXN0000000005
ACCT00002 KL       ABC      PROCESS   TXN0000000005
ACCT00002 KL       ABC      PROCESS   TXN0000000005

您能否为准备SQL提供帮助/建议。

您可以使用 “按级别连接” 技巧来生成行。将其放在横向联接中，可以根据该表的值重复源表中的每一行。

它有这样的形式:

  from   tab, lateral (
    select level l from dual
    connect by level <= num_rows
  )

您可以重复多次:

-将字符串拆分为行
-重复每个子刺 {N} 次
-重复每个事务NO_OF_TXN次

给出类似的东西:

with vals as (
  select a.*, 
         regexp_substr ( sys_n_status, '[^;]+', 1, l ) str,
         l n
  from   acct_test a, lateral (
    select level l from dual
    connect by level <= ( length ( sys_n_status ) - length ( replace ( sys_n_status, ';' ) ) + 1 )
  )
), rws as (
  select r.*
  from   vals r, lateral (
    select * from dual
    connect by level <= regexp_substr ( str, '[0-9]+' )
  )
)
  select * 
  from   rws, lateral ( 
     select level l from dual
     connect by level <= no_of_txn
  )
  order  by account_no, l, n;
  
ACCOUNT_NO   COUNTRY   NO_OF_TXN SYS_N_STATUS                             STR              N   L   
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-NEW{2}       1   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-NEW{2}       1   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   XYZ:DUMMY{1}     3   1 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-NEW{2}       1   2 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-NEW{2}       1   2 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   2 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   2 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   ABC-PROCESS{3}   2   2 
ACCT00001    SG                2 ABC-NEW{2};ABC-PROCESS{3};XYZ:DUMMY{1}   XYZ:DUMMY{1}     3   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   1 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   1 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   1 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   1 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   1 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   2 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   3 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   3 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-NEW{3}       1   3 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   3 
ACCT00002    KL                3 ABC-NEW{3};ABC-PROCESS{2}                ABC-PROCESS{2}   2   3 

从那里开始，这只是从STR中拆分值并使用dense_rank分配事务编号的问题。