oracle 字符串分割

字符串分割

• 示例

select regexp_substr('aa,bb,cc,dd','[^,]+',1,level) as item from dual
connect by level <= length('aa,bb,cc')-length(replace('aa,bb,cc',','))+1;

• 结果

---

把数据集放入数组中处理

• 示例

declare 
type list_type is  table of varchar2(30);  --声明表类型（实际和数组一样）
list list_type;  --声明数组变量
begin
select item bulk collect into list from (  --把查询结果集放入数组中
select regexp_substr('aa,bb,cc,dd','[^,]+',1,level) as item from dual
connect by level <= length('aa,bb,cc')-length(replace('aa,bb,cc',','))+1
) t
where item is not null;
for i in 1..list.count loop  --遍历数组
dbms_output.put_line(list(i));
end loop;
end;

• 结果

---