此需要类似于在sql中,使用in的场景,只是in是一对多,此处需要切割后的多对多。
这里我使用了Oracle函数进行处理,取交集
CREATE OR REPLACE
FUNCTION "FUN_JUDGE_ISCONTAIN" (ids IN NVARCHAR2, out_ids IN NVARCHAR2)
return number
AS
connum NUMBER;
BEGIN
IF ids is null or out_ids is null THEN
return 0;
END IF;
select count(*) into connum from (
select regexp_substr(nme, '[^,]+', 1, rownum) nme
from (select ids nme from dual)
connect by rownum <= length(regexp_replace(nme, '[^,]+')) +1
intersect
select regexp_substr(nme, '[^,]+', 1, rownum) nme
from (select out_ids nme from dual)
connect by rownum <= length(regexp_replace(nme, '[^,]+')) +1);
return connum;
END;
ids和out_ids为两表对应的字段,通过取交集并判断是否返回0,获取两表的查询结果