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;
区间值的验证
最新推荐文章于 2023-02-17 15:39:23 发布