区间值的验证

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;