oracle拆分行

WITH Z_BRANCH AS(
  SELECT 'BR1001' BRID, 'BR1001.BR1002.BR1003' BR_LEVEL FROM DUAL
  UNION ALL
  SELECT 'BR1002' BRID, 'BR1002.BR1003' BR_LEVEL FROM DUAL
  UNION ALL
  SELECT 'BR1003' BRID, 'BR1001.BR1003' BR_LEVEL FROM DUAL
)
SELECT BRID,
       LEVEL, 
       REGEXP_COUNT(BR_LEVEL, '\.') + 1 AS ROW_CNT,
       REGEXP_SUBSTR(BR_LEVEL, '[^.]+', 1, LEVEL) AS S_BRID
  FROM Z_BRANCH T
CONNECT BY LEVEL <= REGEXP_COUNT(BR_LEVEL, '\.') + 1
   AND T.BRID = PRIOR T.BRID
   AND PRIOR DBMS_RANDOM.VALUE > 0;