Oracle字符串拼接 循环拼接

``
`
with temp as (select replace(replace( replace(aa.WAFER_LIST,’[’,’’),’]’,’’),’"’,’’) waferlist,CUSTOMER_LOT_ID
from (SELECT OBJECT_RRN,
COPRODUCT_RRN,
COPRODUCT_ID,
MAIN_PRODUCT_RRN,
MAIN_PRODUCT_ID,
WORKORDER_RRN,
WORKORDER_ID,
CROSS_DIE,
COOD_DIE,
WAFER_QTY,
WAFER_LIST,
CUSTOMER_LOT_ID
FROM COPRODUCT_INFO
WHERE 1 = 1
AND COPRODUCT_RRN = 5245667
AND WORKORDER_RRN = 5245668
) aa)

**SELECT --t1.rrn,
CUSTOMER_LOT_ID || ‘#’ ||substr(t1.waferlist,
instr(t1.waferlist, ‘,’, 1, t2.lvl) + 1,
instr(t1.waferlist, ‘,’, 1, t2.lvl + 1) -
(instr(t1.waferlist, ‘,’, 1, t2.lvl) + 1))

FROM (SELECT --t.rrn,
CUSTOMER_LOT_ID,
‘,’ || t.waferlist || ‘,’ waferlist,
length(t.waferlist) - nvl(length(REPLACE(t.waferlist, ‘,’)), 0) + 1 AS cnt
FROM temp t
WHERE 1=1
–and rrn = 22222
) t1,
(SELECT rownum AS lvl
FROM (SELECT MAX(length(t.waferlist || ‘,’) -
nvl(length(REPLACE(t.waferlist, ‘,’)), 0)) max_len
FROM temp t
WHERE 1=1
–and rrn = 22222
)
CONNECT BY LEVEL <= max_len) t2
WHERE 1=1

AND t2.lvl <= t1.cnt**