SQLSERVER存储过程转换oracle

最新推荐文章于 2024-01-25 11:08:06 发布

最新推荐文章于 2024-01-25 11:08:06 发布 · 839 阅读

文章标签：

#数据库

本文详细介绍了如何将SQL Server存储过程成功转换为Oracle存储过程，包括解决编译错误和调整语法细节。

今天帮人把一个SQL SERVER的简单存储过程改成了ORACEL的存储过程

--以前这个存储过程是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 ;

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

create or replace procedure p_GetPrenDataOfAtt(yymmdd1 date,empid1 number)
as
RHour11 float;
RHour21 float;
PreWTID varchar2(20);
PreLastOut number;

begin
select 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
RHour1,RHour2,WTID into PreLastOUT,RHour11,RHour21,PreWTID
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 a.WTID into PreWTID from
(select row_number()over(order by YYMMDD DESC) as "row",wtid from AttDayData where YYMMDD < yymmdd1 -1 and EmpID =EmpID1) a
where rownum=1;
end if;

end ;