oracle用存储过程批量分割字符串

with cs as (select max(regexp_count(addr,'@')+1) lines from house)
select
substr(getaddr,1,instr(getaddr,'#') -1) "城市",
substr(getaddr,instr(getaddr,'#',1,1)+1,instr(getaddr,'#',1,2) -instr(getaddr,'#',1,1) -1) "小区",
substr(getaddr,instr(getaddr,'#',1,2)+1,instr(getaddr,'#',1,3) -instr(getaddr,'#',1,2) -1) "单元",
substr(getaddr,instr(getaddr,'#',1,3)+1,instr(getaddr,'#',1,4) -instr(getaddr,'#',1,3) -1) "楼房",
substr(getaddr,instr(getaddr,'#',1,4)+1) "房间"
from (
select
a.*,
REGEXP_SUBSTR(a.address ,'(.*?)(@|$)',1,le,null,1) AS getaddr
from house a,(SELECT LEVEL le FROM DUAL a,cs b CONNECT BY LEVEL<= b.lines) c
WHERE le <=regexp_count(a.addr,'@')+1
)