楼上的答案思路没问题,但还是有点小问题。不能用instr来判断pa中有没有a,如下 pa="aaa,bbb,ccc" pb="a,b,c" 返回应该是0,用instr则返回1 判断应该自己来写,将pb中每一个值和pa中的所有值比较来得出结果。其实这个问题解决方法很简单,不过中间有个步骤比较麻烦,就是读取pa,pb中值。一种另类的想法:建两个临时表,pa(a varchar2) pb(b varchar2)分别将pa,pb的每一个值读取出来插入两个表 然后select count(*) from pa,pb where pa.a=pb.b来判断,哈哈!
CREATE OR REPLACE FUNCTION f_include (pa varchar2, pb varchar2) return number is i integer;begin i:=1; loop if instr(pa,substr(pb,i,1))>0 then return 1; end if; if i=length(pb) then exit; end if; i:=i+1; end loop; return 0; end;
function func_include(pa varchar2, pb in out varchar2) return number is strtmp varchar2(50); nposition number:=0; nresult number:=0; begin loop nposition:=instr(pb,','); if nposition=0 then strtmp:=pb; pb:=null; else strtmp:=substr(pb,1,nposition-1); pb:=substr(pb,nposition+1,length(pb)-nposition); end if; if instr(pa,strtmp)>0 then nresult:=1; end if; exit when nresult=1 OR pb is null;
end loop; return(nresult); exception when others then dbms_output.put_line('error'); end;
function func_include1(pa in out varchar2, pb in out varchar2) return number is strtmpA varchar2(50); strtmpB varchar2(50); strPa varchar2(100); npositionB number:=0; nresult number:=0; npositionA number:=0;begin strPa:=pa; loop pa:=strPa; npositionB:=instr(pb,','); if npositionB=0 then strtmpB:=pb; pb:=null; else strtmpB:=substr(pb,1,npositionB-1); pb:=substr(pb,npositionB+1,length(pb)-npositionB); end if; loop npositionA:=instr(pa,','); if npositionA=0 then strtmpA:=pa; pa:=null; else strtmpA:=substr(pa,1,npositionA-1); pa:=substr(pa,npositionA+1,length(pa)-npositionA); end if; -- dbms_output.put_line('strA:'||strtmpA); -- dbms_output.put_line('strb:'||strtmpB); if strtmpA=strtmpB then nresult:=1; end if; exit when nresult=1 OR pa is null; end loop; exit when nresult=1 OR pb is null;
end loop; return(nresult); exception when others then dbms_output.put_line('error'); end;
pa="aaa,bbb,ccc"
pb="a,b,c"
返回应该是0,用instr则返回1
判断应该自己来写,将pb中每一个值和pa中的所有值比较来得出结果。其实这个问题解决方法很简单,不过中间有个步骤比较麻烦,就是读取pa,pb中值。一种另类的想法:建两个临时表,pa(a varchar2) pb(b varchar2)分别将pa,pb的每一个值读取出来插入两个表 然后select count(*) from pa,pb where pa.a=pb.b来判断,哈哈!
(pa varchar2, pb varchar2) return number
is
i integer;begin
i:=1;
loop
if instr(pa,substr(pb,i,1))>0 then
return 1;
end if;
if i=length(pb) then
exit;
end if;
i:=i+1;
end loop;
return 0;
end;
strtmp varchar2(50);
nposition number:=0;
nresult number:=0;
begin loop
nposition:=instr(pb,','); if nposition=0 then
strtmp:=pb;
pb:=null;
else
strtmp:=substr(pb,1,nposition-1);
pb:=substr(pb,nposition+1,length(pb)-nposition);
end if; if instr(pa,strtmp)>0 then
nresult:=1;
end if; exit when nresult=1 OR pb is null;
end loop; return(nresult);
exception
when others then
dbms_output.put_line('error');
end;
strtmpA varchar2(50);
strtmpB varchar2(50);
strPa varchar2(100);
npositionB number:=0;
nresult number:=0;
npositionA number:=0;begin
strPa:=pa;
loop
pa:=strPa;
npositionB:=instr(pb,','); if npositionB=0 then
strtmpB:=pb;
pb:=null;
else
strtmpB:=substr(pb,1,npositionB-1);
pb:=substr(pb,npositionB+1,length(pb)-npositionB);
end if; loop
npositionA:=instr(pa,',');
if npositionA=0 then
strtmpA:=pa;
pa:=null;
else
strtmpA:=substr(pa,1,npositionA-1);
pa:=substr(pa,npositionA+1,length(pa)-npositionA);
end if;
-- dbms_output.put_line('strA:'||strtmpA);
-- dbms_output.put_line('strb:'||strtmpB);
if strtmpA=strtmpB then
nresult:=1;
end if;
exit when nresult=1 OR pa is null;
end loop; exit when nresult=1 OR pb is null;
end loop; return(nresult);
exception
when others then
dbms_output.put_line('error');
end;
我想不要输出自变量的该怎么写呢?谢谢!