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 ||