本文共 4155 字，大约阅读时间需要 13 分钟。

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

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

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

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)    RETURN ty_str_splitIS    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;

CREATE OR REPLACE PROCEDURE jx_qudao_1IS    --一次分割的调用函数分割后的数组    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;

vv_find_val :=vv_aite(2);

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

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