参考链接:https://www.cnblogs.com/RoyalBlue/p/11250092.html
感谢!
借鉴上面,我扩展了下功能,代码如下:
CREATE OR REPLACE FUNCTION INTERSECTION(SOURCE VARCHAR(2000),TARGET VARCHAR(2000),FLAG VARCHAR(100)) RETURN INT
AS
V_PRIC INT;
RE_FLAG VARCHAR(100);
BEGIN
RE_FLAG := concat('[^',FLAG,']+');
SELECT COUNT(*) INTO V_PRIC FROM (
SELECT REGEXP_SUBSTR (SOURCE, RE_FLAG, 1, ROWNUM) AS NME FROM DUAL CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE(SOURCE, RE_FLAG)) +1
INTERSECT
SELECT REGEXP_SUBSTR (TARGET, RE_FLAG, 1,ROWNUM) AS NME FROM DUAL CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE ( TARGET,RE_FLAG)) +1);
RETURN V_PRIC;
END;
测试:
SELECT INTERSECTION ('12;23;6;9','23;7;9;6',';') FROM dual;
SELECT INTERSECTION ('11,13,2,7,51','23,7,9,6',',') FROM dual;
SELECT INTERSECTION ('1,3,2,7,1','23',',') FROM dual;
结果如图:
可以用于判断用户与多部门数据的查询权限,如用户在某业务场景创建的数据,只有与该用户相同部门才可查阅使用,用户1任职于a,b,d部门,用户2任职于a,c,e。判断用户1是否可查询用户2创建的数据。
有需要的,也可以修改上面函数,把返回的结果改为布尔值,只要大于0返回true,否则返回false.