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..unt 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..unt 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,B          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,B          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字段拆成多条记录，⼀条记录以,（逗号）分割，'@@'符号分割的两个值分别是拆分后的⼀条记录的两个字段。此过程即使从横表解析为纵表的过程。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。