oracle实现字符串分割函数 split()函数

1.以下要写的函数实现oracle的字符创分割，例如字符串：'4@@,1@@150107014'使用逗号分割为'4@@'和'1@@150107014'。

2.新建一个数组，看代码：

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

3.创建分割函数：

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN ty_str_split
IS
    j INT := 0;
    i INT := 1;
    len INT := 0;
    len1 INT := 0;
    str VARCHAR2 (400);
    str_split ty_str_split := ty_str_split ();
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);
            str_split.EXTEND;
            str_split (str_split.COUNT) := str;

            IF i >= len
            THEN
                EXIT;
            END IF;
        ELSE
            str := SUBSTR (p_str, i, j - i);
            i := j + len1;
            str_split.EXTEND;
            str_split (str_split.COUNT) := str;
        END IF;
    END LOOP;

    RETURN str_split;
END fn_split;

4.使用：

CREATE OR REPLACE PROCEDURE jx_qudao_1
IS
    --一次分割的调用函数分割后的数组
    type t_agent_code is table of varchar2(400) index by binary_integer;
    vv_comm t_agent_code;
    --二次分割的数组
    vv_aite t_agent_code;
    --二次分割后保存find_id和find_val
    vv_find_id number(18);
    vv_find_val varchar2(50);
    vv_t_find number;
    type serv_array is table of V_CHANNEL_JS_TEMP@to_jsappt_xsqd%rowtype index by binary_integer;
    var_serv serv_array;
BEGIN
  SELECT /*+parallel(t,8)*/ * bulk COLLECT INTO var_serv FROM V_CHANNEL_JS_TEMP@to_jsappt_xsqd t;
  FOR j IN 1..var_serv.count LOOP 
     --写find_rel表
    SELECT column_value bulk collect  into vv_comm FROM TABLE (CAST (fn_split (var_serv(j).agt_tml, ',') AS ty_str_split));
     for i in 1..vv_comm.count loop
      SELECT column_value bulk collect  into vv_aite FROM TABLE (CAST (fn_split (vv_comm(i), '@@') AS ty_str_split));
        vv_find_id := vv_aite(1);
        vv_find_val :=vv_aite(2);
        if vv_aite(2) is not null then
        select /*+parallel(t,8)*/count(*) into vv_t_find from TP_OBJECT_FIND_REL_1227 t where t.find_id = vv_find_id and t.find_val = vv_find_val;
        if vv_t_find =0 then
          insert into TP_OBJECT_FIND_REL_1227(object_id,Object_Type,Find_Id,Find_Val,City_Id,Area_Id,State,State_Date,Create_Staff,Create_Date,End_Date,Begin_Date,Source_Type)
          values(var_serv(j).channel_id,'CHN',vv_find_id,vv_find_val,decode(var_serv(j).agent_city,'025',1,'0510',2,
                              '0511',3,'0512',4,'0513',5,'0514',6,'0515',7,'0516',8,'0517',9,
                              '0518',10,'0519',11,'0523',12,'0527',13,null,null),var_serv(j).area,
                              decode(var_serv(j).status,'10A','12','10D','22',11),sysdate,'lckj',var_serv(j).cre_date,
                              to_date('3000/1/1','yyyy-mm-dd'),to_date('2001/1/1','yyyy-mm-dd'),'IN');
                              COMMIT;
        end if;
        if vv_t_find <>0 then
          update TP_OBJECT_FIND_REL_1227 t set t.state='22',t.state_date=sysdate where t.find_id=vv_find_id
          and t.find_val=vv_find_val and t.state<>'22';
          COMMIT;
          insert into TP_OBJECT_FIND_REL_1227(object_id,Object_Type,Find_Id,Find_Val,City_Id,Area_Id,State,State_Date,Create_Staff,Create_Date,End_Date,Begin_Date,Source_Type)
          values(var_serv(j).channel_id,'CHN',vv_find_id,vv_find_val,decode(var_serv(j).agent_city,'025',1,'0510',2,
                              '0511',3,'0512',4,'0513',5,'0514',6,'0515',7,'0516',8,'0517',9,
                              '0518',10,'0519',11,'0523',12,'0527',13,null,null),var_serv(j).area,
                              decode(var_serv(j).status,'10A','12','10D','22',11),sysdate,'lckj',var_serv(j).cre_date,
                              to_date('3000/1/1','yyyy-mm-dd'),to_date('2001/1/1','yyyy-mm-dd'),'IN');
                              COMMIT;
        end if;
        end if;
    end loop;
   END LOOP;
END jx_qudao_1;

5.说明：分割函数支持对fn_split (’‘, ',')、fn_split (null, ',')的解析。同时需要注意的是fn_split ('4@@', '@@')解析后得到的数组还是有两个字符串，这也是上面过程中能直接写

vv_find_val :=vv_aite(2);

的原因。

注：上诉过程是个解析的过程，将数据库中的记录

将这每条记录按agt_tml字段拆成多条记录，一条记录以,（逗号）分割，'@@'符号分割的两个值分别是拆分后的一条记录的两个字段。此过程即使从横表解析为纵表的过程。