区间值的验证
create table TEST3( n1 NUMBER, n2 NUMBER);delete from test3;commit;insert into test3(n1,n2) values(5,50);insert into test3(n1,n2) values(60,80);insert into test3(n1,n2) values(90,100);insert into test3(n1,n2) values(200,300);commit;select * from test3;select * from (select t.n1 rec_firest, t.n2 rec_second, nvl(lag(n2, 1) over(order by t.n1), 0) FirstValue, t.n1 secondValue, t.n2 thirdValue, nvl(lead(n1, 1) over(order by t.n1), 99999999999999) LastValue from test3 t order by t.n1) x where (x.firstValue < 10 and x.secondValue > 20) or (x.thirdValue < 10 and x.lastValue > 20);/*验证区间范围(正向验证)例如 5 - 50 60 - 80 90 - 100 200 - 300 验证(1:验证通过 0:验证失败) 1 - 4 :return 1 10 - 20 :return 0 51 - 55 :return 1 35 - 99 :reutrn 0 59 - 88 :reutrn 0 301 - 400:return 1*/create or replace function checkIntervalPositive(in_min test3.n1%type, in_max test3.n2%type) return number is cursor check_Interval is Select * From (With Tmp_x As (Select t.n1 rec_firest, t.n2 rec_second, Nvl(Lag(t.N2, 1) Over(Order By t.N1), 0) FirstValue, t.N1 secondValue, t.N2 thirdValue, Nvl(Lead(t.N1, 1) Over(Order By t.N1), 999999999) LastValue From Test3 t Order By t.N1) Select Case When (Select Count(1) From Tmp_x) = 0 Then -1 Else Null End rec_firest, Case When (Select Count(1) From Tmp_x) = 0 Then -1 Else Null End rec_second, Case When (Select Count(1) From Tmp_x) = 0 Then 0 Else null End FirstValue, Case When (Select Count(1) From Tmp_x) = 0 Then -1 Else Null End secondValue, Case When (Select Count(1) From Tmp_x) = 0 Then -1 Else Null End thirdValue, Case When (Select Count(1) From Tmp_x) = 0 Then 999999999 Else null End LastValue From Dual union all Select * From Tmp_x ) x Where 1 = 1 And x.FirstValue is not null and ((x.firstValue < in_min and x.secondValue > in_max) or (x.thirdValue < in_min and x.lastValue > in_max)); type rec_row is RECORD( rec_firest test3.n1%type, rec_second test3.n2%type, FirstValue number, secondValue test3.n1%type, thirdValue test3.n2%type, LastValue number); temp_row rec_row;begin if in_min is null or in_max is null then return 0; end if; open check_Interval; fetch check_Interval into temp_row; IF (check_Interval%FOUND) THEN dbms_output.put_line('起始值 | 终止值 | 当前行的上一行终止值 | 当前起始值 | 当前终止值 | 当前行的下一行起始值'); while check_Interval%found loop dbms_output.put_line(' ' || temp_row.rec_firest || ' ' || temp_row.rec_second || ' ' || temp_row.FirstValue || ' ' || temp_row.rec_second || ' ' || temp_row.thirdValue || ' ' || temp_row.LastValue); fetch check_Interval into temp_row; end loop; CLOSE check_Interval; RETURN 1; END IF; close check_Interval; return 0;end;declare returnNumber_ number;begin dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalPositive(1, 4); dbms_output.put_line('1 - 4 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalPositive(10, 20); dbms_output.put_line('10 - 20 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalPositive(51, 55); dbms_output.put_line('51 - 55 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalPositive(35, 99); dbms_output.put_line('35 - 99 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalPositive(51, 55); dbms_output.put_line('59 - 88 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalPositive(301, 400); dbms_output.put_line('301 - 400 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------');end;/*验证区间范围(反向验证)例如 5 - 50 60 - 80 90 - 100 200 - 300 验证(0:验证通过 1:验证失败) 1 - 4 :return 0 10 - 20 :return 1 51 - 55 :return 0 35 - 99 :reutrn 1 59 - 88 :reutrn 1 301 - 400:return 0*/create or replace function checkIntervalReverse(in_min test3.n1%type, in_max test3.n2%type) return number is cursor check_Interval is Select * From TEST3 t Where ((t.N1 > in_min And t.N1 < in_max) Or (t.N2 > in_min And t.N2 < in_max)) Or (in_min > t.N1 And in_max < t.N2); temp_row check_Interval%Rowtype;begin if in_min is null or in_max is null then return 0; end if; open check_Interval; fetch check_Interval into temp_row; IF (check_Interval%FOUND) THEN CLOSE check_Interval; RETURN 1; END IF; close check_Interval; return 0;end;declare returnNumber_ number;begin dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalReverse(1, 4); dbms_output.put_line('1 - 4 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalReverse(10, 20); dbms_output.put_line('10 - 20 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalReverse(51, 55); dbms_output.put_line('51 - 55 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalReverse(35, 99); dbms_output.put_line('35 - 99 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalReverse(51, 55); dbms_output.put_line('59 - 88 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------'); returnNumber_ := checkIntervalReverse(301, 400); dbms_output.put_line('301 - 400 :=' || returnNumber_); dbms_output.put_line('----------------------------------------------------');end;