create or replace function isContained
/****************************************************
** created by lixiaobin
** 函数名称:isContained
** 作 用:拆分源字符串和目标字符串,并逐项比较
** 参 数:【名称】 【类型 】 【说明】
p_SrcStr varchar2 源字符串
p_DesStr varchar2 目标字符串
p_Mode integer 比较模式:0:或比较* 1:与比较*
p_SrcDel varchar2 源字符串的分隔符(1字符)
p_DesDel varchar2 目标字符串的分隔符(1字符)
** 返 回 值:v_Ret_0 varchar2 或比较时的返回值(0不匹配1匹配)
v_Ret_1 varchar2 与比较时的返回值(0不匹配1匹配)
解释:
或比较:目标字符串任何项目包含在源字符串中则匹配成功
与比较:目标字符串所有项目包含在源字符串中则匹配成功
调用 举例:
select isContained('78;77;76;79','77;78',0,';',';') from dual;
****************************************************/
(
p_SrcStr varchar2, p_DesStr varchar2, p_Mode integer := 0,
p_SrcDel varchar2 := ';', p_DesDel varchar2 := ';'
) return integer as
type t_SrcArr is table of varchar2(100) index by binary_integer;
v_SrcArr t_SrcArr;
v_SrcStr varchar2(32767);
v_DesStr varchar2(32767);
v_SrcItm varchar2(100);
v_DesItm varchar2(100);
i pls_integer;
v_Count pls_integer;
v_Ret_0 integer := 0;
v_Ret_1 integer := 1;
begin
if p_SrcStr is null or p_DesStr is null then
return 0;
end if;
v_SrcStr := p_SrcStr || p_SrcDel;
v_DesStr := p_DesStr || p_DesDel;
i := 1;
while instr(v_SrcStr, p_SrcDel) > 0
loop
v_SrcItm := substr(v_SrcStr, 1, instr(v_SrcStr, p_SrcDel) - 1);
v_SrcStr := substr(v_SrcStr, instr(v_SrcStr, p_SrcDel) + 1);
v_SrcArr(i) := v_SrcItm;
i := i + 1;
end loop;
while instr(v_DesStr, p_DesDel) > 0
loop
if p_Mode = 1 and v_Ret_1 = 0 then
return 0;
end if;
v_DesItm := substr(v_DesStr, 1, instr(v_DesStr, p_DesDel) - 1);
v_DesStr := substr(v_DesStr, instr(v_DesStr, p_DesDel) + 1);
v_Ret_1 := 0;
for i in 1 .. v_SrcArr.count
loop
if v_DesItm = v_SrcArr(i) then
if p_Mode = 0 then
return 1;
else
v_Ret_1 := 1;
exit;
end if;
end if;
end loop;
end loop;
if p_Mode = 0 then
return v_Ret_0;
else
return v_Ret_1;
end if;
end;
/****************************************************
** created by lixiaobin
** 函数名称:isContained
** 作 用:拆分源字符串和目标字符串,并逐项比较
** 参 数:【名称】 【类型 】 【说明】
p_SrcStr varchar2 源字符串
p_DesStr varchar2 目标字符串
p_Mode integer 比较模式:0:或比较* 1:与比较*
p_SrcDel varchar2 源字符串的分隔符(1字符)
p_DesDel varchar2 目标字符串的分隔符(1字符)
** 返 回 值:v_Ret_0 varchar2 或比较时的返回值(0不匹配1匹配)
v_Ret_1 varchar2 与比较时的返回值(0不匹配1匹配)
解释:
或比较:目标字符串任何项目包含在源字符串中则匹配成功
与比较:目标字符串所有项目包含在源字符串中则匹配成功
调用 举例:
select isContained('78;77;76;79','77;78',0,';',';') from dual;
****************************************************/
(
p_SrcStr varchar2, p_DesStr varchar2, p_Mode integer := 0,
p_SrcDel varchar2 := ';', p_DesDel varchar2 := ';'
) return integer as
type t_SrcArr is table of varchar2(100) index by binary_integer;
v_SrcArr t_SrcArr;
v_SrcStr varchar2(32767);
v_DesStr varchar2(32767);
v_SrcItm varchar2(100);
v_DesItm varchar2(100);
i pls_integer;
v_Count pls_integer;
v_Ret_0 integer := 0;
v_Ret_1 integer := 1;
begin
if p_SrcStr is null or p_DesStr is null then
return 0;
end if;
v_SrcStr := p_SrcStr || p_SrcDel;
v_DesStr := p_DesStr || p_DesDel;
i := 1;
while instr(v_SrcStr, p_SrcDel) > 0
loop
v_SrcItm := substr(v_SrcStr, 1, instr(v_SrcStr, p_SrcDel) - 1);
v_SrcStr := substr(v_SrcStr, instr(v_SrcStr, p_SrcDel) + 1);
v_SrcArr(i) := v_SrcItm;
i := i + 1;
end loop;
while instr(v_DesStr, p_DesDel) > 0
loop
if p_Mode = 1 and v_Ret_1 = 0 then
return 0;
end if;
v_DesItm := substr(v_DesStr, 1, instr(v_DesStr, p_DesDel) - 1);
v_DesStr := substr(v_DesStr, instr(v_DesStr, p_DesDel) + 1);
v_Ret_1 := 0;
for i in 1 .. v_SrcArr.count
loop
if v_DesItm = v_SrcArr(i) then
if p_Mode = 0 then
return 1;
else
v_Ret_1 := 1;
exit;
end if;
end if;
end loop;
end loop;
if p_Mode = 0 then
return v_Ret_0;
else
return v_Ret_1;
end if;
end;