开发者博客:www.developsearch.com
扩展已知的数组空间(extend)
DECLARE
TYPE CourseList IS TABLE OF VARCHAR2(10);
courses CourseList;
BEGIN
-- 初始化数组元素,大小为3
courses := CourseList( 'Biol 4412 ', 'Psyc 3112 ', 'Anth 3001 ');
-- 为数组增加一个元素,数组大小为4,末尾的元素为NULL
courses.EXTEND; -- append one null element
-- 为增加的元素赋值,如果没用EXTEND,这里会出错
courses(4) := 'Engl 2005 ';
end
模拟字符对象
-- 创建组合SETIDS表集合类型
CREATE OR REPLACE TYPE TBL_SPLIT_TYPE IS TABLE OF VARCHAR2 (4000);
--字符串分割函数
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;
模拟集合对象
-- 创建流向的数据结构 模拟当个集合的单个流向
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;
开发者博客:www.developsearch.com