oracle判断数据出现交叉,Oracle-调用packages判断结合不允许重复、交叉、包含

最新推荐文章于 2023-04-07 15:07:13 发布

ArthuRScarlet

最新推荐文章于 2023-04-07 15:07:13 发布

阅读量223

点赞数

文章标签： oracle判断数据出现交叉

JAVA 数据校验集合交集存储过程维度比较

关键词由CSDN通过智能技术生成

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 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 LOOP;

END;

END PKG_VALID_SET_INTERSECT;

ArthuRScarlet

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle判断数据出现交叉,Oracle-调用packages判断结合不允许重复、交叉、包含

JAVA代码：@SuppressWarnings("unchecked")public String validateSets(final String setIds) {Object object = getHibernateTemplate().execute(new HibernateCallback(){@SuppressWarnings("deprecation")public Obje...
复制链接

扫一扫