Oracle 比较范围内日期的函数

我有一个函数，它将从具有特定日期和时间的数据库中提取记录。传递的参数之一是日期。此函数每天运行两次，其中传递的参数是AM或PM)。我的功能在早上工作 (时间大约在午夜到中午)。但是对于PM，我得到了retrived记录 (时间从下午1点到下午10点)。

我可能会补充说，记录每天在上午8点之后和中午之后插入一次。我需要为这些时间调整我的功能。

下面是我目前的函数。

create or replace FUNCTION            "F_ASSESS_CCAPLOAD" (pidm_in in number, sdate in date, run in varchar2)
return varchar2 authid definer
is
  return_ASSESCORE varchar2(4);
mmath number(4);
mmeng number(4);
sordate date ;
test_score number(4) ;
test_pidm number(9) ;

  cursor get_ASSESMENT is
   SELECT                                                                                                                                                                                                                                  
   CASE WHEN EXISTS(  select distinct 'AM; ' 
     from szrccap a  where 
       szrccap_pidm = pidm_in 
    and to_date( szrccap_load_date) between to_date(sdate ) - (+13/24) and to_date(sdate ) + (12/24)) 
     THEN 'Y'                                                                                                                                                                                                                                
     ELSE 'N'                                                                                                                                                                                                                                
   END                                                                                                                                                                                                                                       
   FROM dual ;  

   cursor get_ASSESMENT2 is
    SELECT                                                                                                                                                                                                                                  
   case when exists(  select distinct 'PM; '  from  szrccap a  where 
       szrccap_pidm = pidm_in 
      and to_date( szrccap_load_date) between to_date(sdate ) + (12/24) and to_date(sdate ) + (21/24))
       THEN 'Y2'                                                                                                                                                                                                                                
     ELSE 'N2'                                                                                                                                                                                                                                
   END                                                                                                                                                                                                                                       
   FROM dual ;   


 begin                                                                                                                                                                                                                                        
 if run = 'AM' then                                                                                                                                                                                                                      
 goto cohort1;                                                                                                                                                                                                                               
  elsif                                                                                                                                                                                                                                      
   run = 'PM' then                                                                                                                                                                                                                      
   goto cohort2 ;              

 end if;                  



<>                                                                                                                                                                                                                                  
open get_ASSESMENT ;                                                                                                                                                                                                                           
 fetch get_ASSESMENT into  return_ASSESCORE;                                                                                                                                                                                                       
 close get_ASSESMENT;                                                                                                                                                                                                                          
 goto fini;                                                                                                                                                                                                                                  

 <>                                                                                                                                                                                                                                 
open get_ASSESMENT2 ;                                                                                                                                                                                                                           
 fetch get_ASSESMENT2 into  return_ASSESCORE;                                                                                                                                                                                                       
 close get_ASSESMENT2;                                                                                                                                                                                                                          
 goto fini;       


 <>                                                                                                                                                                                                                                    
  return  return_ASSESCORE ;                                                                                                                                                                                                                     



end;

所以你想在给定的日期找到早上或下午/晚上的值？

如果是这样，您可以使用AM格式掩码!

这将在中午之前返回。以及之后的PM。所以你正在寻找在这个日期，匹配这个掩码的行:

alter session set nls_date_format = 'DD MON HH24:MI';
var dt_var varchar2(12);
var am_pm  varchar2(2);

exec :dt_var := '01-JAN-2019';
exec :am_pm  := 'AM';

with rws as (
  select date'2019-01-01' + level/6 dt 
  from   dual
  connect by level <= 10
)
  select * from rws
  where  to_date(:dt_var, 'DD-MON-YYYY') <= dt
  and    dt < to_date(:dt_var, 'DD-MON-YYYY') + 1
  and    to_char ( dt, 'AM' ) = :am_pm;

DT             
01 JAN 04:00    
01 JAN 08:00 
  
exec :dt_var := '01-JAN-2019';
exec :am_pm  := 'PM';

with rws as (
  select date'2019-01-01' + level/6 dt 
  from   dual
  connect by level <= 10
)
  select * from rws
  where  to_date(:dt_var, 'DD-MON-YYYY') <= dt
  and    dt < to_date(:dt_var, 'DD-MON-YYYY') + 1
  and    to_char ( dt, 'AM' ) = :am_pm;
  
DT             
01 JAN 12:00    
01 JAN 16:00    
01 JAN 20:00   

PS-goto有点... 恶心...你可以用一个声明来做到这一切!

附录:
=
所有这些都可以简化为

select min(x)
into   return_ASSESCORE
from
( select 'AM' x
  from   szrccap a  
  where  szrccap_pidm = pidm_in 
  and    szrccap_load_date >= trunc(sdate)
  and    szrccap_load_date < trunc(sdate)+12/24
  and    rownum = 1
  union all
  select 'PM'
  from   szrccap a  
  where  szrccap_pidm = pidm_in 
  and    szrccap_load_date >= trunc(sdate)+12/24
  and    szrccap_load_date < trunc(sdate)+21/24
  and    rownum = 1
)  
where rownum = 1;

return_ASSESCORE将包含AM或PM或null作为结果。