--把下面存储过程转成oracle的,等级低,只能开100分的帖…
Create procedure pExecproduct @parent varchar(40)
as
declare @pe3102 varchar(40)
declare cur_pro cursor for select distinct pe3102 from #pe31 order by pe3102 desc
open cur_pro
fetch next from cur_pro into @pe3102
while @@fetch_status=0
begin
update #pe31 set PE3108=isum
from (select sum(isnull(PE3108,0)*isnull(PE3104,1)) isum
from #pe31 where pe3102=@pe3102 and pe3101<>pe3102) a
where pe3101=@pe3102 and isnull(isum,0)<>0
fetch next from cur_pro into @pe3102
end
close cur_pro
deallocate cur_pro
Create procedure pExecproduct @parent varchar(40)
as
declare @pe3102 varchar(40)
declare cur_pro cursor for select distinct pe3102 from #pe31 order by pe3102 desc
open cur_pro
fetch next from cur_pro into @pe3102
while @@fetch_status=0
begin
update #pe31 set PE3108=isum
from (select sum(isnull(PE3108,0)*isnull(PE3104,1)) isum
from #pe31 where pe3102=@pe3102 and pe3101<>pe3102) a
where pe3101=@pe3102 and isnull(isum,0)<>0
fetch next from cur_pro into @pe3102
end
close cur_pro
deallocate cur_pro
create or replace procedure pExecproduct(parent in varchar2(40))
as
pe3102 varchar2(40);
cursor cur_pro for select distinct pe3102 from #pe31 order by pe3102 desc;
begin
open cur_pro;
loop
fetch cur_pro into pe3102;
exit when cur_pro%notfound; --update语法还要改下
update #pe31 set PE3108=isum
from (select sum(isnull(PE3108,0)*isnull(PE3104,1)) isum
from #pe31 where pe3102=@pe3102 and pe3101<>pe3102) a
where pe3101=@pe3102 and isnull(isum,0)<>0; end loop
close cur_pro;
end;
--你的输入参数好像没有用
--另外oracle里面表名不能以 # 开头,sqlserver 中# 开通属于临时表。
--oracle里面有临时表的概念但是与sqlerver略有不同,具体情况你百度下吧。CREATE OR PROCEDURE PEXECPRODUCT(I_PARENT VARCHAR(40)) AS
V_ISUM NUMBER;
BEGIN
FOR REC IN (SELECT DISTINCT PE3102 FROM PE31 ORDER BY PE3102 DESC) LOOP
SELECT SUM(NVL(PE3108, 0) * NVL(PE3104, 1))
INTO V_ISUM
FROM PE31
WHERE PE3102 = REC.PE3102
AND PE3101 <> PE3102;
UPDATE PE31
SET PE3108 = V_ISUM
WHERE PE3101 = REC.PE3102
AND NVL(ISUM, 0) <> 0;
END LOOP;
END PEXECPRODUCT;
create or replace procedure pExecproduct is
type cs is ref cursor;
v_pe3102 varchar(40);
cur_pro cs;
begin
open cur_pro for
select distinct pe3102 from pe31 order by pe3102 desc; loop
fetch cur_pro
into v_pe3102;
exit when cur_pro%notfound;
update pe31
set PE3108 =
(select sum(nvl(PE3108, 0) * nvl(PE3104, 1)) isum
from pe31
where pe3102 = v_pe3102
and pe3101 <> pe3102
and nvl(isum, 0) <> 0);
end loop;
close cur_pro;
commit;
exception
when others then
rollback;
close cur_pro;
end pExecproduct;
有什么问题?我在MSSQL2000上执行通过……