Oracle--调用packages判断组合不允许重复、交叉、包含
JAVA代码：
CREATE OR REPLACE PACKAGE BODY PKG_VALID_SET_INTERSECT IS --字符串分割函数 FUNCTION SPLIT_STR(P_STR IN VARCHAR2, P_DELIMITER IN VARCHAR2 DEFAULT (',')) RETURN tbl_split_type IS J INT := 0; I INT := 1; LEN INT := 0; LEN1 INT := 0; STR VARCHAR2(4000); MY_SPLIT tbl_split_type := tbl_split_type(); BEGIN LEN := LENGTH(P_STR); LEN1 := LENGTH(P_DELIMITER); WHILE J < LEN LOOP J := INSTR(P_STR, P_DELIMITER, I); IF J = 0 THEN J := LEN; STR := SUBSTR(P_STR, I); MY_SPLIT.EXTEND; MY_SPLIT(MY_SPLIT.COUNT) := STR; IF I >= LEN THEN EXIT; END IF; ELSE STR := SUBSTR(P_STR, I, J - I); I := J + LEN1; MY_SPLIT.EXTEND; MY_SPLIT(MY_SPLIT.COUNT) := STR; END IF; END LOOP; RETURN MY_SPLIT; END; -- 检测节点是否包含函数 FUNCTION COUNT_DIST_CODE(IN_DIST_CODE IN VARCHAR2, IN_TYPE_CODE IN VARCHAR2, COMPARE_DIST_CODE IN VARCHAR2) RETURN NUMBER IS V_COUNT NUMBER; BEGIN -- 如果原寄地的级别为国家,下钻到省,市 IF IN_TYPE_CODE = 1 THEN SELECT COUNT(0) INTO V_COUNT FROM (WITH CODES AS (SELECT T.DIST_CODE FROM TM_DISTRICT T WHERE T.TYPE_CODE = 2 AND T.COUNTRY_CODE = IN_DIST_CODE) SELECT DIST_CODE FROM CODES UNION SELECT T.DIST_CODE FROM TM_DISTRICT T, CODES C WHERE T.PROVINCE_CODE = C.DIST_CODE AND T.TYPE_CODE = 3 UNION SELECT IN_DIST_CODE FROM DUAL) R WHERE R.DIST_CODE = COMPARE_DIST_CODE; -- 如果原寄地的级别为省,下钻到市，上溯到国家 ELSIF IN_TYPE_CODE = 2 THEN SELECT COUNT(0) INTO V_COUNT FROM (SELECT T.COUNTY_CODE AS DIST_CODE FROM TM_DISTRICT T WHERE T.DIST_CODE = IN_DIST_CODE UNION SELECT T.DIST_CODE FROM TM_DISTRICT T WHERE T.TYPE_CODE = 3 AND T.PROVINCE_CODE = IN_DIST_CODE UNION SELECT IN_DIST_CODE FROM DUAL) R WHERE R.DIST_CODE = COMPARE_DIST_CODE; -- 如果原寄地的级别为市,上溯到市,国家 ELSIF IN_TYPE_CODE = 3 THEN SELECT COUNT(0) INTO V_COUNT FROM (WITH CODES AS (SELECT PROVINCE_CODE FROM TM_DISTRICT T WHERE T.DIST_CODE = IN_DIST_CODE) SELECT T.DIST_CODE FROM TM_DISTRICT T, CODES C WHERE T.DIST_CODE = C.PROVINCE_CODE AND T.TYPE_CODE = 1 UNION SELECT C.PROVINCE_CODE FROM CODES C UNION SELECT IN_DIST_CODE FROM DUAL) R WHERE R.DIST_CODE = COMPARE_DIST_CODE; END IF; RETURN V_COUNT; END; -- 检查维度是否存在交集 FUNCTION COUNT_DMNSN(IN_DMNSN_A IN NUMBER, IN_DMNSN_B IN NUMBER) RETURN NUMBER AS V_COUNT NUMBER; BEGIN -- 任意一方为-1(全选) 都表示存在交集 将V_COUNT手工置值为1 IF (IN_DMNSN_A = -1 OR IN_DMNSN_B = -1) THEN V_COUNT := 1; -- 两者都不为-1的情况需要铺开判断是否存在交集 ELSE SELECT COUNT(0) INTO V_COUNT FROM (SELECT D.CODE FROM CDH_NEWRBT_SET_DMNSN D WHERE D.DMNSN_ID = IN_DMNSN_A INTERSECT SELECT D.CODE FROM CDH_NEWRBT_SET_DMNSN D WHERE D.DMNSN_ID = IN_DMNSN_B); END IF; -- 返回结果 RETURN V_COUNT; END; -- 根据ID获取别名 FUNCTION GET_SET_ALIAS_BY_DIRID(DIRID IN VARCHAR2) RETURN VARCHAR2 AS V_SET_ALIAS VARCHAR2(60); BEGIN SELECT T.ALIAS INTO V_SET_ALIAS FROM CDH_NEWRBT_SET T WHERE T.DIR_ID = DIRID; RETURN V_SET_ALIAS; END; -- 根据CODE获取流向名称 FUNCTION GET_DIST_NAME_BY_DISTCODE(DISTCODE IN VARCHAR2) RETURN VARCHAR2 AS V_DIST_NAME VARCHAR2(100); BEGIN SELECT T.DIST_NAME INTO V_DIST_NAME FROM TM_DISTRICT T WHERE T.DIST_CODE = DISTCODE; RETURN V_DIST_NAME; END; -- 验证组合的其他维度是否存在交集 必须所有维度都存在交集才可断定两个组合维度存在交集 FUNCTION VALIDATE_DMNSN(SETID_A IN NUMBER, SETID_B IN NUMBER) RETURN NUMBER AS V_COUNT NUMBER := 0; V_UNION_COUNT NUMBER := 0; V_DMNSN_A NUMBER; V_DMNSN_B NUMBER; BEGIN -- 区域流向 为非必填项 所以必须分别判断两个组合区域流向之间的各情况 SELECT S.REGION_DMNSN_ID INTO V_DMNSN_A FROM CDH_NEWRBT_SET S WHERE S.SET_ID = SETID_A; SELECT S.REGION_DMNSN_ID INTO V_DMNSN_B FROM CDH_NEWRBT_SET S WHERE S.SET_ID = SETID_B; -- 1.如果两者都不为空的情况 IF (V_DMNSN_A IS NOT NULL AND V_DMNSN_B IS NOT NULL) THEN V_COUNT := COUNT_DMNSN(V_DMNSN_A, V_DMNSN_B); --2.都为空表示该维度不参与比较，将V_COUNT手工置值为1，继续比较后续其他维度 ELSIF (V_DMNSN_A IS NULL AND V_DMNSN_B IS NULL) THEN V_COUNT := 1; -- 3.任意一方为空表示无交集,中断退出 ELSE V_COUNT := 0; END IF; -- 其他维度为必填项，所以对比时分为两种情形: --1.任意一方为-1(全选)就可以断定存在交集(将V_COUNT手工置值为1，继续比较后续其他维度) --2.都不为-1(全选)铺开对比检查是否存在交集 -- 区域流向是否存在交集 IF V_COUNT > 0 THEN DBMS_OUTPUT.PUT_LINE('区域流向存在交集'); -- 业务类型 SELECT S.BUSINESS_DMNSN_ID INTO V_DMNSN_A FROM CDH_NEWRBT_SET S WHERE S.SET_ID = SETID_A; SELECT S.BUSINESS_DMNSN_ID INTO V_DMNSN_B FROM CDH_NEWRBT_SET S WHERE S.SET_ID = SETID_B; V_COUNT := COUNT_DMNSN(V_DMNSN_A, V_DMNSN_B); END IF; -- 业务类型是否存在交集 IF V_COUNT > 0 THEN DBMS_OUTPUT.PUT_LINE('业务类型存在交集'); -- 快件内容 SELECT S.CARGO_DMNSN_ID INTO V_DMNSN_A FROM CDH_NEWRBT_SET S WHERE S.SET_ID = SETID_A; SELECT S.CARGO_DMNSN_ID INTO V_DMNSN_B FROM CDH_NEWRBT_SET S WHERE S.SET_ID = SETID_B; V_COUNT := COUNT_DMNSN(V_DMNSN_A, V_DMNSN_B); END IF; -- 快件内容是否存在交集 IF V_COUNT > 0 THEN DBMS_OUTPUT.PUT_LINE('快件内容存在交集'); -- 时效 SELECT S.TIME_DMNSN_ID INTO V_DMNSN_A FROM CDH_NEWRBT_SET S WHERE S.SET_ID = SETID_A; SELECT S.TIME_DMNSN_ID INTO V_DMNSN_B FROM CDH_NEWRBT_SET S WHERE S.SET_ID = SETID_B; V_COUNT := COUNT_DMNSN(V_DMNSN_A, V_DMNSN_B); END IF; -- 时效是否存在交集 IF V_COUNT > 0 THEN DBMS_OUTPUT.PUT_LINE('时效存在交集'); -- 时效 SELECT S.APPEND_DMNSN_ID INTO V_DMNSN_A FROM CDH_NEWRBT_SET S WHERE S.SET_ID = SETID_A; SELECT S.APPEND_DMNSN_ID INTO V_DMNSN_B FROM CDH_NEWRBT_SET S WHERE S.SET_ID = SETID_B; V_COUNT := COUNT_DMNSN(V_DMNSN_A, V_DMNSN_B); END IF; -- 结果返回 RETURN V_COUNT; END; -- 验证多个组合 PROCEDURE VALIDATE_SETIDS(SETIDS IN VARCHAR2,MSG IN OUT VARCHAR2) AS V_COUNT NUMBER; V_SET_ALIAS_A VARCHAR2(60); V_SET_ALIAS_B VARCHAR2(60); V_ORG_DIST_NAME_A VARCHAR2(100); V_DESC_DIST_NAME_A VARCHAR2(100); V_ORG_DIST_NAME_B VARCHAR2(100); V_DESC_DIST_NAME_B VARCHAR2(100); V_HAS_AREADY_COMPARE_SETID VARCHAR2(4000); BEGIN -- 查询组合 FOR RBT_SET_A IN (SELECT T.SET_ID, T.DIR_ID FROM CDH_NEWRBT_SET T WHERE T.SET_ID IN (SELECT * FROM TABLE(SPLIT_STR(SETIDS)))) LOOP -- 添加已经比较过的组合ID V_HAS_AREADY_COMPARE_SETID := V_HAS_AREADY_COMPARE_SETID || RBT_SET_A.SET_ID || ','; -- 排除上一次已经获取过的组合 FOR RBT_SET_B IN (SELECT T.SET_ID, T.DIR_ID FROM CDH_NEWRBT_SET T WHERE T.SET_ID IN (SELECT A.COLUMN_VALUE FROM (SELECT * FROM TABLE(SPLIT_STR(SETIDS))) A, (SELECT * FROM TABLE(SPLIT_STR(V_HAS_AREADY_COMPARE_SETID))) B WHERE A.COLUMN_VALUE = B.COLUMN_VALUE(+) AND B.COLUMN_VALUE IS NULL)) LOOP -- 查询每个组合流向明细 FOR RBT_SET_DIR_A IN (SELECT T.ORG_DIST_CODE, T.ORG_TYPE_CODE, T.DESC_DIST_CODE, T.DESC_TYPE_CODE FROM CDH_NEWRBT_SET_DIR T WHERE T.DIR_ID = RBT_SET_A.DIR_ID) LOOP /** *判断每个明细流向的原寄地是否和输入参数ARR_OBJ中流向的原寄地存在包含或被包含， *判断依据：以明细流向原寄地的点为中心上溯到顶级节点和下钻到最小叶子节点作为一颗参照树 *然后判断入参中的每个流向的原寄地是否存在于参照树中： *原寄地和目的地都存在重叠才能断定两个流向是存在交集的 */ FOR RBT_SET_DIR_B IN (SELECT T.ORG_DIST_CODE, T.ORG_TYPE_CODE, T.DESC_DIST_CODE, T.DESC_TYPE_CODE FROM CDH_NEWRBT_SET_DIR T WHERE T.DIR_ID = RBT_SET_B.DIR_ID) LOOP -- 先判断原寄地是否存在重叠,根据结果在判断目的地是否存在重叠 V_COUNT := COUNT_DIST_CODE(RBT_SET_DIR_A.ORG_DIST_CODE, RBT_SET_DIR_A.ORG_TYPE_CODE, RBT_SET_DIR_B.ORG_DIST_CODE); -- 判断原寄地是否重叠 IF V_COUNT > 0 THEN V_COUNT := COUNT_DIST_CODE(RBT_SET_DIR_A.DESC_DIST_CODE, RBT_SET_DIR_A.DESC_TYPE_CODE, RBT_SET_DIR_B.DESC_DIST_CODE); -- 判断目的地是否重叠 IF V_COUNT > 0 THEN DBMS_OUTPUT.PUT_LINE('流向存在交集'); -- 验证其他维度是否存在交集 V_COUNT := VALIDATE_DMNSN(RBT_SET_A.SET_ID, RBT_SET_B.SET_ID); if V_COUNT > 0 then V_SET_ALIAS_A := GET_SET_ALIAS_BY_DIRID(RBT_SET_A.DIR_ID); V_SET_ALIAS_B := GET_SET_ALIAS_BY_DIRID(RBT_SET_B.DIR_ID); /**V_ORG_DIST_NAME_A := GET_DIST_NAME_BY_DISTCODE(RBT_SET_DIR_A.ORG_DIST_CODE); V_DESC_DIST_NAME_A := GET_DIST_NAME_BY_DISTCODE(RBT_SET_DIR_A.DESC_DIST_CODE); V_ORG_DIST_NAME_B := GET_DIST_NAME_BY_DISTCODE(RBT_SET_DIR_B.ORG_DIST_CODE); V_DESC_DIST_NAME_B := GET_DIST_NAME_BY_DISTCODE(RBT_SET_DIR_B.DESC_DIST_CODE); MSG := '组合[' || V_SET_ALIAS_A || ']中的流向：' || V_ORG_DIST_NAME_A || '->' || V_DESC_DIST_NAME_A || '与组合[' || V_SET_ALIAS_B || ']中的流向：' || V_ORG_DIST_NAME_B || '->' || V_DESC_DIST_NAME_B || '存在重叠!';*/ MSG := '组合[' || V_SET_ALIAS_A || ']与组合[' || V_SET_ALIAS_B || ']中的维度存在重叠'; -- 检测到重叠立即中断返回 RETURN; END IF; END IF; END IF; END LOOP; END LOOP; END LOOP; END LOOP; END; -- 验证多个流向 PROCEDURE VALIDATE_DIST_CODES(ARR_OBJ IN ARR_OBJ_DIST_CODE,MSG IN OUT VARCHAR2) AS V_COUNT NUMBER; V_ORG_DIST_NAME_A VARCHAR2(100); V_DESC_DIST_NAME_A VARCHAR2(100); V_ORG_DIST_NAME_B VARCHAR2(100); V_DESC_DIST_NAME_B VARCHAR2(100); BEGIN FOR I IN 1 .. ARR_OBJ.COUNT() LOOP FOR J IN (I+1) .. ARR_OBJ.COUNT() LOOP -- 先判断原寄地是否存在重叠,根据结果在判断目的地是否存在重叠 V_COUNT := COUNT_DIST_CODE(ARR_OBJ(I).ORG_DIST_CODE, ARR_OBJ(I).ORG_TYPE_CODE, ARR_OBJ(J).ORG_DIST_CODE); -- 判断原寄地是否重叠 IF V_COUNT > 0 THEN V_COUNT := COUNT_DIST_CODE(ARR_OBJ(I).DESC_DIST_CODE, ARR_OBJ(I).DESC_TYPE_CODE, ARR_OBJ(J).DESC_DIST_CODE); -- 判断目的地是否重叠 IF V_COUNT > 0 THEN V_ORG_DIST_NAME_A := GET_DIST_NAME_BY_DISTCODE(ARR_OBJ(I).ORG_DIST_CODE); V_DESC_DIST_NAME_A := GET_DIST_NAME_BY_DISTCODE(ARR_OBJ(I).DESC_DIST_CODE); V_ORG_DIST_NAME_B := GET_DIST_NAME_BY_DISTCODE(ARR_OBJ(J).ORG_DIST_CODE); V_DESC_DIST_NAME_B := GET_DIST_NAME_BY_DISTCODE(ARR_OBJ(J).DESC_DIST_CODE); MSG := '流向：' || V_ORG_DIST_NAME_A || '->' || V_DESC_DIST_NAME_A || '与流向：' || V_ORG_DIST_NAME_B || '->' || V_DESC_DIST_NAME_B || '存在重叠!'; END IF; END IF; END LOOP; END LOOP; END;END PKG_VALID_SET_INTERSECT;?