mssql存储过程转成ORACLE解决方案

mssql存储过程转成ORACLE
--把下面存储过程转成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

[解决办法]

SQL code

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;
[解决办法]
SQL code
--你的输入参数好像没有用--另外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;
[解决办法]
LZ自己写的存储过程就有问题。。。。哎

SQL code
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;
[解决办法]
探讨

引用:
LZ自己写的存储过程就有问题。。。。哎


SQL code

create or replace procedure pExecproduct is
type cs is ref cursor;
v_pe3102 varchar(40);
cur_pro cs;
begin
open cur_pro for
select di……

我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html