plsql简单的切割字符串函数

方法一：

自己写的一个简单的string的split函数

因为有这个需求，需要切割字符串，在oracle中没有找到类似java的split函数，自己写了一个

plsql code:

---

包data_type_for_cm中自定义类型:

type varcharArray is table of varchar2(25) index by binary_integer;


create or replace function str_split(
       str in varchar2,    --需要分割的字符串
       pattern in varchar2 --分隔符
      )
      return data_type_for_cm.varcharArray -- 自定义的字符串数组
      is
len number;--分割的数组元素个数
i number;--position位置
res varchar2(100);
up_len number;--上一个位置
down_len number;--下一个位置
cstr data_type_for_cm.varcharArray;--声明集合
begin
       --判断有多少个','号，那么分割的集合元素个数是len+1
       len:=length(trim(translate(str,replace(str,pattern),' ')));
       len := nvl(len, 0);--防止输入如"1",","
       if (len = 0) then
          cstr(1) := str;
          return cstr;
       end if;

       for j in 1..len+1 loop--j是集合元素下标
         if j=1 then
             i:=instr(str,pattern,1,j);
             res:=substr(str,1,i-1);
             --cstr.extend(1);
             res := nvl(res, '');--防止输入"1,,,2", ","
             if length( res) > 0 then
                cstr(cstr.count + 1):=trim(res);
             end if;
             up_len := i;
             down_len := i;
          elsif j<len+1 then
             i:=instr(str,pattern,1,j);
             down_len:=i;
             res:=substr(str,up_len+1,down_len-up_len-1);
             --cstr.extend(1);
             res := nvl(res, '');--防止输入"1,,,2", ","
             if length( res) > 0 then
                cstr(cstr.count + 1):=trim(res);
             end if;
             up_len:=i;
           else
             res:=substr(str,down_len+1);
             res := nvl(res, '');--防止输入"1,,,2", ","
             if length( res) > 0 then
                cstr(cstr.count + 1):=trim(res);
             end if;
           end if;  
      end loop;
      return cstr;
end str_split;

test


方法二：

在网上看了几个，下面发上来 

---

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);






CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)



           RETURN ty_str_split PIPELINED



IS



           j INT := 0;



    i INT := 1;



    len INT := 0;



    len1 INT := 0;



    str VARCHAR2 (4000);



BEGIN



           len := LENGTH (p_str);



    len1 := LENGTH (p_delimiter);







           WHILE j < len



           LOOP



               j := INSTR (p_str, p_delimiter, i);







               IF j = 0



               THEN



                   j := len;



            str := SUBSTR (p_str, i);



            PIPE ROW (str);







                   IF i >= len



                   THEN



                EXIT;



            END IF;



               ELSE



            str := SUBSTR (p_str, i, j - i);



            i := j + len1;



            PIPE ROW (str);



        END IF;



    END LOOP;







           RETURN;



END fn_split;



/











--测试：







SELECT * FROM TABLE (fn_split ('1;;12;;123;;1234;;12345', ';;'));

PLS-00653错误及解决

       声明为PIPELINED的函数不能在PL/SQL中使用，需要使用平面函数或者select * bulk collect into tab_oe_list from table(oe_list(pi_hr4u_id));代替。

       如执行以下过程将会发生错误：

       create or replace type hrp_oe_list as table of varchar2(10)；

       /

FUNCTION oe_list(pi_hr4u_id IN hrp_users.usr_hr4u_id%TYPE)
RETURN hrp_oe_list
PIPELINED

As

Begin

…

end;

       /

       然后在PL/SQL中调用将会失败：

       tab_oe_list hrp_oe_list;

       tab_oe_list := oe_list(pi_hr4u_id);

       会发生PLS-00653错误。

方法三：

将表格的某一列转换成逗号分隔的字符串的另类方法

A：dbms_utility.table_to_comma的使用
一：首先建立测试表
create table table1
(
dz varchar2(100)
)
insert into table1 values('v');
insert into table1 values('dywm');
commit;

SQL> select * from table1;

DZ
--------------------------------------------------------------------------------
v
dywm

二：建立存储过程
create or replace procedure test_column_row as
qq1 dbms_utility.uncl_array;
l_tablen number;
l_tab varchar2(1000);
begin
select dz bulk collect into qq1 from table1;
dbms_utility.table_to_comma(qq1, l_tablen, l_tab);
dbms_output.put_line(l_tab);
end;

三：运行结果如下：
SQL> set serveroutput on
SQL> execute test_column_row;

v,dywm

PL/SQL procedure successfully completed

附：字符串<=>table相互转换的例子
create or replace procedure schema_table as

type vcArray is table of varchar2(4000);
l_names vcArray := vcArray('sswe,oejic,wwer', 'a , b , c',
'e123o,a688j9,d557i5', 'd334a,q225d,w67s7');
l_tablen number;
l_tab dbms_utility.uncl_array;
begin
for i in 1 .. l_names.count
loop
dbms_output.put_line('i_names(' || i || ')=' || l_names(i));
begin
dbms_utility.comma_to_table(l_names(i), l_tablen, l_tab);
for j in 1 .. l_tablen
loop
dbms_output.put_line('l_tab(' || j || ')=' || l_tab(j));
end loop;
l_names(i) := null;
dbms_utility.table_to_comma(l_tab, l_tablen, l_names(i));
dbms_output.put_line(l_names(i));
end;
end loop;
end;