本文共 1185 字，大约阅读时间需要 3 分钟。

实例01：

CREATE TABLE T3 ( LX VARCHAR2(10), DM VARCHAR2(20), QX VARCHAR2(3000) ); insert into T3 values('CK09','mp702','about|accept|cancel|controlg|controlp'); select LX, DM, regexp_substr(QX, '[^|]+',1,level) new_itemfrom t3 connect by level <= regexp_count(qx,'|') + 1 and prior rowid = rowid and prior dbms_random.value is not nulland regexp_substr(QX, '[^|]+',1,level) is not null

实例02：

create table a ( cid number, num number);insert into a (CID, NUM) values (101, 30);insert into a (CID, NUM) values (102, 34);insert into a (CID, NUM) values (185, 34);insert into a (CID, NUM) values (230, 224);insert into a (CID, NUM) values (345, 12);create table b ( id number, cid varchar2(100));insert into b (ID, CID) values (1, '101|102|103|104');insert into b (ID, CID) values (2, '201|223|432|235');insert into b (ID, CID) values (3, '102|345|999|234|230');with tmp as (select id, regexp_substr(cid, '[^|]+', 1, level) new_cid    from b  connect by level <= regexp_count(cid, '|') + 1         and prior rowid = rowid         and prior dbms_random.value is not null         and regexp_substr(cid, '[^|]+', 1, level) is not null)select * from a where a.cid not in (select new_cid from tmp);

转载地址：http://cgjxi.baihongyu.com/