欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle 字符串分割的处理

程序员文章站 2022-05-11 23:29:57
...

1.首先要先创建数组 create or replace type t_ret_table is table of varchar2(20); 2.利用自定义函数实现一,利用函数返回数组

1.首先要先创建数组

create or replace type t_ret_table is table of varchar2(20);

2.利用自定义函数实现一,利用函数返回数组.
create or replace function f_split_string(var_str in string,var_split In String) return t_ret_table
is
var_out t_ret_table;
var_tmp varchar2(4000);
var_element varchar2(4000);
begin
var_tmp := var_str;
var_out := t_ret_table();
--如果存在匹配的分割符
while instr(var_tmp,var_split)>0 loop
var_element := substr(var_tmp,1,instr(var_tmp,var_split)-1);
var_tmp := substr(var_tmp,instr(var_tmp,var_split)+length(var_split),length(var_tmp));
var_out.extend(1);
var_out(var_out.count) := var_element;
end loop;
var_out.extend(1);
var_out(var_out.count) := var_tmp;
return var_out;
end f_split_string;


3.利用 pipelined Function 函数实现.
create or replace function f_split(var_str in string,var_split In String) return t_ret_table PIPELINED
as
var_tmp varchar2(4000);
var_element varchar2(4000);
n_length Number := length(var_split);
begin
var_tmp := var_str;
while instr(var_tmp,var_split)>0 loop
var_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 f_split;

Oracle 字符串分割的处理