oracel中字符串分割成集合详解

-- use conventional plsql
create or replace function f_str2list_pls
(
  p_str       varchar2,
  p_separator varchar2 default ','
) return my_tk_str_tab_type is
  l_idx  pls_integer := 0;
  l_str  varchar2(32767) := trim(p_str);
  l_elmt varchar2(100) := null;
  l_list my_tk_str_tab_type := my_tk_str_tab_type();
begin
  while l_str is not null loop
    l_idx := instr(l_str, p_separator);
    if l_idx = 0 then
      l_elmt := l_str;
      l_str  := null;
    else
      l_elmt := substr(l_str, 1, l_idx - 1);
      l_str  := substr(l_str, l_idx + 1);
    end if;
 
    l_list.extend;
    l_list(l_list.last) := trim(l_elmt);
  end loop;

  return l_list;
end;
/

-- use single sql
create or replace function f_str2list_sql
(
  p_str       varchar2,
  p_separator varchar2 default ','
) return my_tk_str_tab_type is
  l_list my_tk_str_tab_type := my_tk_str_tab_type();
begin
  select substr(a.str,
                instr(p_separator || a.str, p_separator, 1, rn),
                instr(a.str || p_separator, p_separator, 1, rn) -
                instr(p_separator || a.str, p_separator, 1, rn)) q
    bulk collect into l_list
    from (select p_str as str from dual) a,
         (select rownum rn from dual connect by rownum <= length(p_str)) b
   where instr(p_separator || a.str, p_separator, 1, rn) > 0;

  return l_list;
end;
/

-- same result
declare
  l_list my_tk_str_tab_type := my_tk_str_tab_type();
  l_str varchar2(1000) := 'a,b,c';
begin
  l_list := f_str2list_pls(l_str,',');
  for i in 1..l_list.count loop
    dbms_output.put_line(l_list(i));
  end loop;
 
  dbms_output.put_line('');
  l_list := f_str2list_sql(l_str,',');
  for i in 1..l_list.count loop
    dbms_output.put_line(l_list(i));
  end loop;
end;
/

SQL> set serveroutput on
a
b
c
 
a
b
c

 

 

-- pls versions tring length limit
declare
  l_list my_tk_str_tab_type := my_tk_str_tab_type();
  l_str  varchar2(32767) := '';
  l_max  pls_integer := 16383;
begin
  -- construct string
  for i in 1 .. l_max loop
    l_str := l_str || ',' || 'a';
  end loop;
  l_str := substr(l_str, 2);

   l_list := f_str2list_pls(l_str, ',');
end;
/

PL/SQL procedure successfully completed

declare
  l_list my_tk_str_tab_type := my_tk_str_tab_type();
  l_str  varchar2(32767) := '';
  l_max  pls_integer := 16384;
begin
  -- construct string
  for i in 1 .. l_max loop
    l_str := l_str || ',' || 'a';
  end loop;

  l_list := f_str2list_pls(l_str, ',');
end;
/

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 9

-- 修改一下代码，实际调用这个函数。从结果可以看到，f_str2list_pls()内部报超长错误
declare
  l_list my_tk_str_tab_type := my_tk_str_tab_type();
  l_str  varchar2(32767) := '';
  l_max  pls_integer := 16383;
begin
  -- construct string
  for i in 1 .. l_max loop
    l_str := l_str || ',' || 'a';
  end loop;

  l_list := f_str2list_pls(l_str||',a', ',');
end;
 
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "T2.F_STR2LIST_PLS", line 7
ORA-06512: at line 12

-- 因为SQL中varchar2上限是4000，所以使用2000个元素。一切正常。
----------------------------------------------------
-- sql versions tring length limit
declare
  l_list my_tk_str_tab_type := my_tk_str_tab_type();
  l_str  varchar2(32767) := '';
  l_max  pls_integer := 2000;
begin
  -- construct string
  for i in 1 .. l_max loop
    l_str := l_str || ',' || 'a';
  end loop;
  l_str := substr(l_str, 2);

   l_list := f_str2list_sql(l_str, ',');
end;
/

PL/SQL procedure successfully completed

declare
  l_list my_tk_str_tab_type := my_tk_str_tab_type();
  l_str  varchar2(32767) := '';
  l_max  pls_integer := 2001;
begin
  -- construct string
  for i in 1 .. l_max loop
    l_str := l_str || ',' || 'a';
  end loop;
  l_str := substr(l_str, 2);

   l_list := f_str2list_sql(l_str, ',');
end;
 
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "T2.F_STR2LIST_SQL", line 8
ORA-06512: at line 13

----------------------------------------------------
-- performance test
declare
  l_list my_tk_str_tab_type := my_tk_str_tab_type();
  l_str  varchar2(32767) := '';
  l_max  pls_integer := 2000;
begin
  -- construct string
  for i in 1 .. l_max loop
    l_str := l_str || ',' || 'a';
  end loop;
  l_str := substr(l_str, 2);

  -- warm up before actually calculation
  l_list := f_str2list_sql(l_str, ',');
  l_list := f_str2list_pls(l_str, ',');
 
  -- begin calc and diff
  my_rs.rs_start;
  -- 1. pls version
  l_list := f_str2list_pls(l_str, ',');
  my_rs.rs_middle;
  -- 2. sql version
  l_list := f_str2list_sql(l_str, ',');
  my_rs.rs_stop();
end;
/