oracle 判断A字符串数组和B字符串数组有交集的字符串个数函数

--新增判断A字符串数组和B字符串数组有交集的字符串个数函数
create or replace function stringArrayIntersectionCount(A in varchar2, B in varchar) return number deterministic  --deterministic 是函数索引，意思是如果输入一样的话就不运行，取上次输入同样入参后运算出来的值

as
countNum number;
    --判断A字符串数组和B字符串数组有交集的字符串个数
        begin
SELECT COUNT(*) into countNum  FROM
  (select REGEXP_SUBSTR(A, '[^,]+', 1, LEVEL, 'i') AS ADDUSERCD 
  from DUAL
  CONNECT BY LEVEL <= LENGTH(A) - LENGTH(replace(A, ',', '')) + 1
  INTERSECT
  select REGEXP_SUBSTR(B, '[^,]+', 1, LEVEL, 'i') AS ADDUSERCD 
  from DUAL
  CONNECT BY LEVEL <= LENGTH(B) - LENGTH(replace(B, ',', '')) + 1
  );           
  return countNum;
   end stringArrayIntersectionCount;
/

目前这个函数用于select 查询，充当一个字段或者查询条件还是太慢了，表查出来多少行就得用多少次。查出来的基数据少的可以用cross join来处理，数据如果很多的话尽量找别的方法实现

晚点等我试试管道函数看看效率是否有提升

--------------------

select 里调用函数还是太慢了，后来用cross join 代替了。最后发现用inner join 还能更快一点。所以最后实现方式是：

select
 distinct id from
(select
  id,a数组
 from
表A
where  d_month = '2023-04'

）A
INNER JOIN (
select REGEXP_SUBSTR(b数组, '[^,]+', 1, LEVEL, 'i') AS ADDUSERCD 
  from DUAL
  CONNECT BY LEVEL <= LENGTH(b数组) - LENGTH(replace(b数组, ',', '')) + 1
)  on  instr(',' || a数组|| ',', ',' || ADDUSERCD || ',') > 0

）AA