--以前这个存储过程是SQL Server的,我想把它用oracle语句来,但是编译不过,有错误,请大家指教一下,create or replace p_GetPrenDataOfAtt(yymmdd1 date,empid1 number)
as
RHour11 float;
RHour21 float;
PreWTID varchar2(20);
PreLastOut number;begin
select PreLastOUT:=case
when IntGO5>-1 and IntOUT5>-1 then IntOUT5;
when IntGO4>-1 and IntOUT4>-1 then IntOUT4;
when IntGO3>-1 and IntOUT3>-1 then IntOUT3;
when IntGO2>-1 and IntOUT2>-1 then IntOUT2;
when IntGO1>-1 and IntOUT1>-1 then IntOUT1;
end case,
RHour11: = RHour1, RHour21: = RHour2 , PreWTID:= WTID
from AttDayData where YYMMDD =YYMMDD1 - 1 and EmpID=EmpID1;
if PreLastOUT is null then
PreLastOUT: = -1;
end if;
if PreLastOUT >1440 then
PreLastOUT: = PreLastOUT - 1440;
else
PreLastOUT:= 0 ;
end if ;
if RHour11>0 or RHour21>0 or PreWTID is null then
select PreWTID:=a.WTID from
(select row_number()over(order by YYMMDD DESC) as "row",wtid from AttDayData where YYMMDD < yymmdd1 -1 and EmpID =EmpID1;)a
where a.row=1;
end if;
end ;
as
RHour11 float;
RHour21 float;
PreWTID varchar2(20);
PreLastOut number;begin
select PreLastOUT:=case
when IntGO5>-1 and IntOUT5>-1 then IntOUT5;
when IntGO4>-1 and IntOUT4>-1 then IntOUT4;
when IntGO3>-1 and IntOUT3>-1 then IntOUT3;
when IntGO2>-1 and IntOUT2>-1 then IntOUT2;
when IntGO1>-1 and IntOUT1>-1 then IntOUT1;
end case,
RHour11: = RHour1, RHour21: = RHour2 , PreWTID:= WTID
from AttDayData where YYMMDD =YYMMDD1 - 1 and EmpID=EmpID1;
if PreLastOUT is null then
PreLastOUT: = -1;
end if;
if PreLastOUT >1440 then
PreLastOUT: = PreLastOUT - 1440;
else
PreLastOUT:= 0 ;
end if ;
if RHour11>0 or RHour21>0 or PreWTID is null then
select PreWTID:=a.WTID from
(select row_number()over(order by YYMMDD DESC) as "row",wtid from AttDayData where YYMMDD < yymmdd1 -1 and EmpID =EmpID1;)a
where a.row=1;
end if;
end ;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货