Oracle--调用packages判断组合不允许重复、交叉、包含

keepwork

浏览: 334266 次
性别:
来自: 深圳

最近访客更多访客>>

gaoming1990

zjy_369

不穿秋裤的超人

xubukang

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

开发者博客：www.developsearch.com

JAVA代码：

@SuppressWarnings("unchecked")
	public String validateSets(final String setIds) {
		Object object = getHibernateTemplate().execute(new HibernateCallback(){
			@SuppressWarnings("deprecation")
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				CallableStatement statement = session.connection().prepareCall("{call PKG_VALID_SET_INTERSECT.VALIDATE_SETIDS(?,?)}");
				statement.setString(1, setIds);
				statement.registerOutParameter(2, Types.VARCHAR);
				statement.execute();
				return statement.getString(2);
			}
		});
		return object == null ? null :object.toString();
	}

packages：

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;

开发者博客：www.developsearch.com

分享到：