CREATE OR REPLACE FUNCTION validate_zone_contain (zone_value VARCHAR2,zone1 VARCHAR2,delimiter varchar2)
RETURN BOOLEAN IS
v_flag BOOLEAN;
v_zone VARCHAR2(500);
v_zone1 VARCHAR2(500);
i NUMBER := 1;
c_i NUMBER := 1;
j NUMBER := 1;
k NUMBER := 1;
c_k NUMBER := 1;
h NUMBER := 1;
v_zone_value NUMBER;
v_zone_start NUMBER;
v_zone_end NUMBER;
BEGIN
-- 区域
v_zone := RTrim(LTrim(zone_value, delimiter), delimiter);
v_zone1 := RTrim(LTrim(zone1, delimiter), delimiter);
v_flag := TRUE;
LOOP
i := InStr(v_zone, delimiter, j);
c_i := InStr(v_zone, delimiter, j);
IF v_flag AND i> 0 THEN
v_zone_value := to_number(Trim(SubStr(v_zone, j, i-j)));
j := i+1;
LOOP
k := InStr(v_zone1, delimiter, h);
-- 多个值时 K > 0 (112,113)
IF k>0 THEN
v_zone_start := to_number(Trim(SubStr(v_zone1, h, k-h)));
v_zone_end := to_number(Trim(SubStr(v_zone1, h, k-h)))+100;
IF v_zone_value < v_zone_start OR v_zone_value > v_zone_end THEN
v_flag := FALSE;
END IF;
k := h+1;
ELSIF c_k >0 AND k = 0 THEN -- c_k 避免k遍历最后一个报错
v_zone_start := to_number(TRIM(v_zone1));
v_zone_end := v_zone_start+100;
IF v_zone_value < v_zone_start OR v_zone_value > v_zone_end THEN
v_flag := FALSE;
END IF;
END IF;
EXIT WHEN k=0;
END LOOP;
ELSIF v_flag AND c_i > 0 AND i= 0 THEN
v_zone_value := to_number(v_zone);
LOOP
k := InStr(v_zone1, delimiter, h);
IF k>0 THEN
v_zone_start := to_number(Trim(SubStr(v_zone1, h, k-h)));
v_zone_end := to_number(Trim(SubStr(v_zone1, h, k-h)))+100;
IF v_zone_value < v_zone_start OR v_zone_value > v_zone_end THEN
v_flag := FALSE;
END IF;
k := h+1;
ELSIF c_k >0 AND k = 0 THEN
v_zone_start := to_number(TRIM(v_zone1));
v_zone_end := v_zone_start+100;
IF v_zone_value < v_zone_start OR v_zone_value > v_zone_end THEN
v_flag := FALSE;
END IF;
END IF;
EXIT WHEN k=0;
END LOOP;
END IF;
EXIT WHEN i=0 OR v_flag = FALSE;
END LOOP;
RETURN v_flag;
END;
RETURN BOOLEAN IS
v_flag BOOLEAN;
v_zone VARCHAR2(500);
v_zone1 VARCHAR2(500);
i NUMBER := 1;
c_i NUMBER := 1;
j NUMBER := 1;
k NUMBER := 1;
c_k NUMBER := 1;
h NUMBER := 1;
v_zone_value NUMBER;
v_zone_start NUMBER;
v_zone_end NUMBER;
BEGIN
-- 区域
v_zone := RTrim(LTrim(zone_value, delimiter), delimiter);
v_zone1 := RTrim(LTrim(zone1, delimiter), delimiter);
v_flag := TRUE;
LOOP
i := InStr(v_zone, delimiter, j);
c_i := InStr(v_zone, delimiter, j);
IF v_flag AND i> 0 THEN
v_zone_value := to_number(Trim(SubStr(v_zone, j, i-j)));
j := i+1;
LOOP
k := InStr(v_zone1, delimiter, h);
-- 多个值时 K > 0 (112,113)
IF k>0 THEN
v_zone_start := to_number(Trim(SubStr(v_zone1, h, k-h)));
v_zone_end := to_number(Trim(SubStr(v_zone1, h, k-h)))+100;
IF v_zone_value < v_zone_start OR v_zone_value > v_zone_end THEN
v_flag := FALSE;
END IF;
k := h+1;
ELSIF c_k >0 AND k = 0 THEN -- c_k 避免k遍历最后一个报错
v_zone_start := to_number(TRIM(v_zone1));
v_zone_end := v_zone_start+100;
IF v_zone_value < v_zone_start OR v_zone_value > v_zone_end THEN
v_flag := FALSE;
END IF;
END IF;
EXIT WHEN k=0;
END LOOP;
ELSIF v_flag AND c_i > 0 AND i= 0 THEN
v_zone_value := to_number(v_zone);
LOOP
k := InStr(v_zone1, delimiter, h);
IF k>0 THEN
v_zone_start := to_number(Trim(SubStr(v_zone1, h, k-h)));
v_zone_end := to_number(Trim(SubStr(v_zone1, h, k-h)))+100;
IF v_zone_value < v_zone_start OR v_zone_value > v_zone_end THEN
v_flag := FALSE;
END IF;
k := h+1;
ELSIF c_k >0 AND k = 0 THEN
v_zone_start := to_number(TRIM(v_zone1));
v_zone_end := v_zone_start+100;
IF v_zone_value < v_zone_start OR v_zone_value > v_zone_end THEN
v_flag := FALSE;
END IF;
END IF;
EXIT WHEN k=0;
END LOOP;
END IF;
EXIT WHEN i=0 OR v_flag = FALSE;
END LOOP;
RETURN v_flag;
END;