又来问sql server 存储过程转oracle出现的问题了
原sql server存储过程：
CREATE procedure spp_freecond_cfommcm (@POPNo varchar(11),@intCount int output)
as
declare @FundSource int
declare @ReturnValue float
select @FundSource=FundSource from TTQ_Basic where POPNo=@POPNo
if rtrim(ltrim(@FundSource))=3
Begin
select @ReturnValue=
case c.Channel
When '1 ' Then a.MMCMper-b.Mld_MMCM-0.02
When '2 ' Then a.MMCMper-b.Mli_MMCM-0.02
end
from TTQ_CMCost a,vproductsellgate b,TTQ_Basic c
where a.POPNo=@POPNo and a.POPNo=c.POPNo and b.ProductID=c.ProductID
if @ReturnValue> 0
select @intCount=0
else
select @intCount=1
End
else
Begin
select @ReturnValue=
case c.Channel
When '1 ' Then a.MMCMper-b.Mld_MMCM
When '2 ' Then a.MMCMper-b.Mli_MMCM
end
from TTQ_CMCost a,vproductsellgate b,TTQ_Basic c
where a.POPNo=@POPNo and a.POPNo=c.POPNo and b.ProductID=c.ProductID
if @ReturnValue> 0
select @intCount=0
else
select @intCount=1
End
return @intcount
我新改的oracle,但是出错了，55555
create or replace procedure spp_freecond_cfommcm
(p_POPNo in varchar2, intCount out int)
is
FundSource number;
ReturnValue number;
begin
select FundSource into FundSource from TTQ_Basic where POPNo=p_POPNo;
if rtrim(ltrim(FundSource))=3 then
Begin
select
case c.Channel
When '1 ' Then a.MMCMper-b.Mld_MMCM-0.02;
When '2 ' Then a.MMCMper-b.Mli_MMCM-0.02;
end
into ReturnValue
from TTQ_CMCost a,vproductsellgate b,TTQ_Basic c
where a.POPNo=p_POPNo and a.POPNo=c.POPNo and b.ProductID=c.ProductID;
if ReturnValue> 0 then
intCount:=0;
else
intCount:=1;
end if;
End
else
Begin
select
case c.Channel
When '1 ' Then a.MMCMper-b.Mld_MMCM;
When '2 ' Then a.MMCMper-b.Mli_MMCM;
end
into ReturnValue
from TTQ_CMCost a,vproductsellgate b,TTQ_Basic c
where a.POPNo=p_POPNo and a.POPNo=c.POPNo and b.ProductID=c.ProductID;
if ReturnValue> 0 then
intCount:=0;
else
intCount:=1;
end if;
End
end if;
end spp_freecond_cfommcm;
[解决办法]
--如果还有错误，把错误贴出来
create or replace procedure spp_freecond_cfommcm
(p_POPNo in varchar2, intCount out int)
is
FundSource number;
ReturnValue number;
begin
select FundSource into FundSource from TTQ_Basic where POPNo=p_POPNo;
if rtrim(ltrim(FundSource))=3 then
--begin --不需要写
select
case c.Channel
When '1 ' Then a.MMCMper-b.Mld_MMCM-0.02
When '2 ' Then a.MMCMper-b.Mli_MMCM-0.02
end
into ReturnValue
from TTQ_CMCost a,vproductsellgate b,TTQ_Basic c
where a.POPNo=p_POPNo and a.POPNo=c.POPNo and b.ProductID=c.ProductID;
if ReturnValue> 0 then
intCount:=0;
else
intCount:=1;
end if;
-- End --不需要，如果想加，后面要加;
else
-- Begin
select --select中不用加;
case c.Channel
When '1 ' Then a.MMCMper-b.Mld_MMCM
When '2 ' Then a.MMCMper-b.Mli_MMCM
end
into ReturnValue
from TTQ_CMCost a,vproductsellgate b,TTQ_Basic c
where a.POPNo=p_POPNo and a.POPNo=c.POPNo and b.ProductID=c.ProductID;
if ReturnValue> 0 then
intCount:=0;
else
intCount:=1;
end if;
end if;
end spp_freecond_cfommcm;
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html
