问题描述

sqlserver语句转oracle

求大神们解答。下面是sqlsever

declare @FaultID nvarchar(50)
declare @filePath nvarchar(500)
set @FaultID = '{0}'
set @filePath = ''
declare @ReportLogID nvarchar(50)

Declare CaseCur Cursor for
select ReportLogID from " + LogDataBase + @"T_FORM_ReportLogFault where FaultID = @FaultID order by ModifyDate desc
Open CaseCur
Fetch Next From CaseCur into @ReportLogID
While @@FETCH_STATUS = 0 Begin

    if exists(select * from " + LogDataBase + @"T_FORM_FormTableReportItemDataLog where genTableResultID= @FaultID and ID =@ReportLogID )

begin
select top 1 @filePath = FilePath from " + LogDataBase + @"T_FORM_FormTableReportItemDataFiles
where ReportItemDataLogID = @ReportLogID
order by CreateDate desc
--print 'aa'
end
else
begin
select top 1 @filePath = FilePath from " + LogDataBase + @"T_FORM_FormTableReportItemDataFiles
where ReportItemDataLogID = @ReportLogID
and fieldid in(select formTableReportitemid from
T_FORM_FormTableReportItemMatch m ,T_CUS_Fault_Case c
where m.FaultType = c.FaultType and c.ID=@FaultID)
order by CreateDate desc
--print 'bb'
end
if @filePath <> '' begin

break;
end
Fetch Next From CaseCur into @ReportLogID
--print @filePath

    End

Close CaseCur
Deallocate CaseCur

if exists(select * from " + LogDataBase + @"T_FORM_FormTableReportItemDataLog where genTableResultID= @FaultID and ID =@ReportLogID )
begin
select * from " + LogDataBase + @"T_FORM_FormTableReportItemDataFiles
where ReportItemDataLogID = @ReportLogID
order by CreateDate desc
--print 'aa'
end
else
begin
select * from " + LogDataBase + @"T_FORM_FormTableReportItemDataFiles
where ReportItemDataLogID = @ReportLogID
and fieldid in(select formTableReportitemid from
T_FORM_FormTableReportItemMatch m ,T_CUS_Fault_Case c
where m.FaultType = c.FaultType and c.ID=@FaultID)
order by CreateDate desc
--print 'bb'
end

----------------------------------这里是改了一半的oracle---------------------------------------------Declare
vFaultID varchar2(50);
vfilePath varchar2(500);
vReportLogID varchar2(50);
counts number :=0;
cursor CaseCur is select ReportLogID from T_FORM_ReportLogFault where FaultID = vFaultID order by ModifyDate desc;
begin
vFaultID:='{0}';
vfilePath:=' ';
select count(*) into counts from D_Log where genTableResultID= vFaultID and ID =vReportLogID;
open CaseCur;
loop
fetch CaseCur into vReportLogID;
exit when CaseCur%notfound;
if counts>0 then
select FilePath into vfilePath from (select FilePath from (select FilePath from D_Files
where ReportItemDataLogID = vReportLogID
order by CreateDate desc) where rownum=1);
else
select FilePath into vfilePath from (select FilePath from (select FilePath from D_Files
where ReportItemDataLogID = vReportLogID
and fieldid in(select formTableReportitemid from
F_LevelMatch m ,T_CUS_Fault_Case c
where m.FaultType = c.FaultType and c.ID=vFaultID)
order by CreateDate desc)where rownum=1);
end if;
if vfilePath<>' 'then
exit;
end if;
end loop;
close CaseCur;
if counts>0then
select * from D_Files
where ReportItemDataLogID = vReportLogID
order by CreateDate desc;
else
select * from D_Files
where ReportItemDataLogID = vReportLogID
and fieldid in(select formTableReportitemid from
F_LevelMatch m ,T_CUS_Fault_Case c
where m.FaultType = c.FaultType and c.ID=vFaultID)
order by CreateDate desc;
end if;
end;
--------------------------请大神们看一下oracle部分的语法是否和sqlsever的逻辑一致。然后oracle部分的最好两个select * from ..会报错，缺少into ，求大神告知怎么修改

解决方案

游标建议改为带参游标

declare
FaultID varchar2(50);
ReportLogID varchar2(50);
filePath varchar2(500);
CURSOR CaseCur(fid varchar2) IS select ReportLogID from T_FORM_ReportLogFault where FaultID = fid order by ModifyDate desc;
begin
FaultID = '{0}'
open CaseCur(FaultID) ;
loop
fetch CaseCur into ReportLogID ;
exit when CaseCur%notfound;
end loop;
end;