Oracle--对象类型
?
扩展已知的数组空间(extend)
-- 创建流向的数据结构 模拟当个集合的单个流向CREATE OR REPLACE TYPE OBJ_DIST_CODE AS OBJECT( ORG_DIST_CODE VARCHAR2(30), ORG_TYPE_CODE VARCHAR2(2), DESC_DIST_CODE VARCHAR(30), DESC_TYPE_CODE VARCHAR2(2));-- 创建流向的数据结构集合 模拟当个集合的所有流向CREATE OR REPLACE TYPE ARR_OBJ_DIST_CODE AS VARRAY(1000) OF OBJ_DIST_CODE;-- 验证单个组合中的多个流向是否存在重叠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 := '{0}' || V_ORG_DIST_NAME_A || '{1}' || V_DESC_DIST_NAME_A || '{2}' || V_ORG_DIST_NAME_B || '{3}' || V_DESC_DIST_NAME_B || '{4}'; END IF; END IF; END LOOP; END LOOP; END;?
?