2024-04-25 01:21:28
--通过游标遍历调用方法拆取存表:
--方法
create or replace function f_test_substr(
prm1 varchar2, --地
prm2 varchar2 , --名称
prm3 varchar2, --IP
prm4 varchar2 --结果
)
return varchar2
as
v_n int:=0; --第几次取
v_l int; --:截取次数
v_s varchar2(2000); --处理拆分字符串
v_s1 varchar2(200); --本次拆分
v_s2 varchar2(200); --本次剩余
v_type varchar2(100); --设备类型
v_code varchar2(100); --设备编号
v_desc varchar2(100); --设备描述
v_errmsg varchar2(200);
begin
v_l:=length(prm4)-length(replace(prm4,';',''));
v_s:=prm4||';';
v_s1:=substr(v_s,1,instr(v_s,';',1,1));
v_s2:=replace(v_s,v_s1,'');
while v_n<=v_l loop
v_s2:=replace(v_s2,v_s1,'');
v_s1:=substr(v_s1,1,length(v_s1)-1);
v_type:=substr(v_s1,1,instr(v_s1,',',1,1)-1);
v_code:=substr(v_s1,instr(v_s1,',',1,1)+1,instr(v_s1,',',1,2)-instr(v_s1,',',1,1)-1);
v_desc:=substr(v_s1,instr(v_s1,',',1,2)+1,instr(v_s1||',',',',1,3)-instr(v_s1,',',1,2)-1);
insert into t_test_str(a,b,c,d,e,f) values(prm1,prm2,prm3,substr(v_type,instr(v_type,']',1)+1),v_code,v_desc);
v_n:=v_n+1;
v_s1:=substr(v_s2,1,instr(v_s2,';',1,1));
end loop;
commit;
return '成功';
exception
when others then
v_errmsg:=substr(sqlerrm,1,200);
return v_errmsg ;
end;
--存过
create or replace procedure p_test_substr1(o_result out varchar2)
as
v_a varchar2(20);
v_b varchar2(20);
v_c varchar2(20);
v_d varchar2(2000);
v_num int;
cursor c_str is
SELECT * from t_test_substr
;
begin
select count(1) into v_num from t_test_substr;
while v_num>0 loop
open c_str;
loop
FETCH c_str INTO
v_a,v_b,v_c,v_d;
EXIT WHEN c_str%NOTFOUND;
o_result:=f_test_substr(v_a,v_b,v_c,v_d);
v_num:=v_num-1;
end loop;
CLOSE c_str;
end loop;
end;
效果见图:
2024-04-25 01:07:25
2024-04-25 00:43:05