oracle的split函数

资料一：

PL/SQL 中没有split函数，需要自己写。

　　代码：

 

create or replace type type_split as table of varchar2(50);  --创建一个　　type　　，如果为了使split函数具有通用性，请将其size 设大些。

--创建function

create or replace function split

(

   p_list varchar2,

   p_sep varchar2 := ','

)  return type_split pipelined

 is

   l_idx  pls_integer;

   v_list  varchar2(50) := p_list;

begin

   loop

      l_idx := instr(v_list,p_sep);

      if l_idx > 0 then

          pipe row(substr(v_list,1,l_idx-1));

          v_list := substr(v_list,l_idx+length(p_sep));

      else

          pipe row(v_list);

          exit;

      end if;

   end loop;

   return;

end split;

测试：

 

SQL> select * from table(split('northsnow,塞北的雪',','));

COLUMN_VALUE

--------------------------------------------------

northsnow

塞北的雪

SQL> 


补充：
-----PIPELINED关键字是什么意思？
---- pipe row是什么意思？

pipelined声名此function是pipe的,如果这么声名了,就必须使用pipe row的方式把数据返回,常规函数最后的"return 变量",就变成了"return".

pipelined的function主要是为了提高效率,不用等所有的数据都处理完成了才返回客户端,它是边处理边返回.适用于大数据量的交互.

资料二：

方法一：
自己写的一个简单的string的split函数
因为有这个需求，需要切割字符串，在oracle中没有找到类似java的split函数，自己写了一个
    plsql code:
    /**************************************************************************************************************************************************************
     1.判断有多少个','号，确定要查找的次数len= length(trim(translate(str, replace(str,','),' ')))，那么分割的集合元素个数是len+1
     2.循环查找','号出现的位置,用instr函数，如第1次出现位置是pos1=instr(str,',',1,1),第2次出现的位置pos2=instr(str,',',1,2),一致到第len次出现的位置instr(str,',',1,len)
     3.查找子串:substr函数.第1个子串substr(str,1,pos1-1),第2个子串substr(str,pos1+1,pos2-pos1-1),2到n-1依次类堆，到第len+1个是substr(str,pos_len+1)
     function name:str_plit
     功能：str_splite(str,pattern),第一个参数是源，第二个参数是分割的模式，返回一个集合nested table
     author:dj
     date:2008-09-17
     ****************************************************************************************************************************************************************
     **/
    create or replace type t_varray is table of varchar2(20);
    create or replace function str_split(str in varchar2,pattern in varchar2) return t_varray
     as
     len number;--分割的数组元素个数
     i number;--position位置
     res varchar2(100);
     up_len number;--上一个位置
     down_len number;--下一个位置
     cstr t_varray:=t_varray();--声明集合
     begin
       
         len:=length(trim(translate(str,replace(str,pattern),' ')));
         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);
           cstr(j):=res;
           up_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);
              cstr(j):=res;
             up_len:=i;
              else
             res:=substr(str,down_len+1);
              cstr.extend(1);
              cstr(j):=res;
           end if;   
       end loop;
        return cstr;
     end;
     
    --test
    /*
    declare
    str varchar2(100):='aa|bb|cc,dd,ee';
     pattern varchar2(10):='|';
     res varchar2(10);
    cstr t_varray:=t_varray();
     begin
        cstr:=str_split(str,pattern);
    for i in cstr.first .. cstr.last loop
           dbms_output.put_line(cstr(i));
     end loop;
     end;
    */

方法二：
在网上看了几个，下面发上来
    /**
    用pipe函数实现
    **/
    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', ';;'));
    /**结果：
    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_listPIPELINED
As
Begin
…
end;
        /
        然后在PL/SQL中调用将会失败：
        tab_oe_list hrp_oe_list;
        tab_oe_list := oe_list(pi_hr4u_id);
        会发生PLS-00653错误。

plsql简单的string的split函数和PLS-00653错误