我想根据条件插入一条记录.如果列值包含方括号，则应插入一条记录，否则将根据情况插入

Question

CREATE TABLE new_staging(
e_id     NUMBER(10),
e_owner  VARCHAR2(255)
CONSTRAINT pk_new_staging PRIMARY KEY
( E_ID ) );
insert into new_staging values(1,'AUZA, PAUL OSA');
insert into new_staging values(2,'PAUL, REXX OSA');
insert into new_staging values(3,'PAUL, REXX OSA (PR56789)');
insert into new_staging values(4,'PAUL, REXX, OSA');
CREATE SEQUENCE new_target_sq;
CREATE TABLE new_target(
t_id     NUMBER(10) DEFAULT new_target_sq.NEXTVAL NOT NULL,
e_id     NUMBER(10),
t_owner  VARCHAR2(30),
sort_order number(30),
CONSTRAINT pk_new_target PRIMARY KEY
( T_ID ) , CONSTRAINT FK_NEW_TARGET_E_ID FOREIGN KEY ( E_ID ) REFERENCES NEW_STAGING ( E_ID ));

我想根据select查询中的以下条件将值插入到new_target表中。目前，我正在验证e_owner是否包含逗号。如果它包含，那么它就选择了那么多行。假设e_id包含AUZA，PAUL OSA，那么它应该将两条记录插入到new_target表中，同样也插入三个逗号。但是，如果列在e_owner列中有括号((，那么它应该只插入一条记录，例如e_id 3

下面是选择查询，它只做逗号分隔的工作，但也需要包括处理"("的逻辑。

select e_id
,trim(regexp_substr(e_owner, '[^,]+', 1, level)) as owner,
level as sort_order
from   new_staging
connect by e_id = prior e_id
and level <= regexp_count(e_owner, ',') + 1
and prior sys_guid() is not null

此外，在插入记录时，它抛出了一个错误。

insert into new_target(t_id,e_id,t_owner,sort_order) 
(t_id.nextval,select e_id
,trim(regexp_substr(e_owner, '[^,]+', 1, level)) as owner
,level as sort_order
from   new_staging
connect by e_id = prior e_id
and level <= regexp_count(e_owner, ',') + 1
and prior sys_guid() is not null);

有一次，我构建了处理括号的逻辑，然后将我想要插入的全部内容插入到new_target表中。

Answer 1

insert into new_target(t_id, e_id, t_owner, sort_order)
select NEW_TARGET_SQ.nextval,e_id
,case when e_owner like '%(%' then e_owner else trim(regexp_substr(e_owner, '[^,]+', 1, level)) end,
level
from   new_staging
connect by e_id = prior e_id
and level <= regexp_count(case when e_owner like '%(%' then 'x' else e_owner end, ',') + 1
and prior sys_guid() is not null