Oracle 函数的创建-----处理时间(通话时间)

Oracle 函数的创建-----处理时间(通话时间)

 

create or replace function get_hh24miss(pss in number) return varchar2 is
  Result Varchar2(20);
  hh24 Varchar2(4);
  mi   Varchar2(3);
  ss   Varchar2(2);
  i    Number(4);
  j    Number(4);
Begin
  hh24:=lpad(to_char(trunc(pss/3600)),2,'0');
  i:=Mod(pss,3600);
  mi:=lpad(to_char(trunc(i/60)),2,'0');
  j:=Mod(i,60);
  ss:=lpad(to_char(j),2,'0');
  Result:=hh24||':'||mi||':'||ss;
  If pss>360000 Then
    Result:='超出100小时';
  End If;
  return(Result);
end get_hh24miss;

------------------------------------

select CALLER_NO,DIAL_NO,CALLING_TIME,
       get_hh24miss((onhook_time-offhook_time)*86400)
from vox_calllog_10

-----------------------------------