[oracle自定义函数]pipe row 将一个字符串拆分成多条记录

写法一：通过基础函数及简单算法，加上自带的pipe row函数来实现（参考链接：/goodleiwei/article/details/11484991）

1.创建需要返回的类型

create or replace type t_ret_tableas table of varchar2(1000);

2.编写函数（我在原来基础上添加了去除两端的分隔符处理）

create or replace function row_split(var_str varchar2, var_split in varchar2)return t_ret_table pipelinedasvar_tmp varchar2(1000);var_element varchar2(1000);n_length number:=length(var_split);--将字符串分割为多条记录begin/*对输入的字符串做预处理，去掉两端的分隔符（参考/article/3a2f7c2e72324e26afd6119a.html）*/var_tmp := trim(both var_split from var_str);--只要字符串中存在分隔符，则继续执行将分隔出来的字符取出的操作while instr(var_tmp, var_split) > 0 loopvar_element := substr(var_tmp, 1, instr(var_tmp, var_split)-1);-- 每取完字符串里的分隔的字符，该字符将从原始字符串中剔除var_tmp := substr(var_tmp,instr(var_tmp, var_split)+n_length,length(var_tmp));pipe row(var_element);end loop;pipe row(var_tmp);return;end row_split;

3.调用函数

select * from table(row_split('|111|222|', '|'));select * from table(row_split(' 111 222 ', ' '));select * from table(row_split(',111,222,', ','));

以上返回结果均为

写法二：（直接使用oracle中现成的regexp_substr函数）

当然也可以使用函数regexp_substr（Oracle sql技巧--字符串分离--情形二），也是返回同样的结果

select regexp_substr('|111|222|', '[^|]+', 1, level) as column_value from dual connect by regexp_substr('|111|222|', '[^|]+', 1, level) is not null;select regexp_substr(',111,222,', '[^,]+', 1, level) as column_value from dual connect by regexp_substr(',111,222,', '[^,]+', 1, level) is not null;select regexp_substr(' 111 222 ', '[^ ]+', 1, level) as column_value from dual connect by regexp_substr(' 111 222 ', '[^ ]+', 1, level) is not null;

如果对于上面每次都要写这么长一串，觉得很累赘的话，可以类似于row_split的写法，把regexp_substr写的语句再次封装成一个函数，每次只传入待分隔的字符串、分隔符，就得到想要的结果。

-05-29更新

开始我以为对regexp_substr的封装是类似的，用pipe row再写一遍就行了。当我后来尝试写的时候发现这两者其实并不一样，因为写法一是一行一行的返回，针对每行用pipe row；而写法二是直接用返回结果集（是一列数据），当然有人会说我再用for循环遍历每行，然后再用pipe row，也实现了同样效果，但这样多做了无用功，因为oracle直接给了我们结果集，如果有方法能直接把这个结果集给传递出去，问题就解决了

通过google搜索，找到

上面这个页面的链接：https://renenyffenegger.ch/notes/development/databases/Oracle/PL-SQL/collection-types/return-table-from-function/index

这样，其实就很好封装了，代码如下：

1. 创建需要返回的类型（这个和方法一写的完全一样）

create or replace type t_ret_tableas table of varchar2(1000);

2.编写函数

create or replace function row_split2(var_str varchar2, var_split in varchar2)return t_ret_tableasv_ret t_ret_table;var_split_str varchar2(200);--将字符串分割为多条记录beginvar_split_str:='[^'||var_split||']+';select regexp_substr(var_str, var_split_str, 1, level) bulk collect into v_retfrom dualconnect by regexp_substr(var_str, var_split_str, 1, level) is not null;return v_ret;end row_split2;

3.调用函数

select * from table(row_split2('|111|222|', '|'));select * from table(row_split2(' 111 222 ', ' '));select * from table(row_split2(',111,222,', ','));

以上返回结果均为