Sql Server与Ｏracle存储过程转换

在sql 向oracle 迁移过程中，最头疼的也许就是存储过程的转换了，之前利用sql/developer 将sql 数据迁移到oracle 里面去了，但是对于存储过程来说，还需要进行大量的后续更改，下面就介绍一个sql 中的一个update 语句是如何转向 oracle的

例子:

Sql:

declare @locatidnext char(11),
 @sphwcount int, @goodsidnext char(11),
 @upshl decimal(14,3), @fpsl decimal(14,3)
 set @sphwcount = 0
 set @upshl = 0.00
 set @fpsl = 0.00
 set @goodsidnext = ''
 set @locatidnext = ''
 
 update #tmppicils set
 @upshl = case when goodsid <> @goodsidnext  then upshl else @upshl - @fpsl end,
 @sphwcount = case when goodsid <> @goodsidnext /*or hw <> @hwnext*/ then 1 else @sphwcount + 1 end,
 @fpsl = biandongsl = case when @sphwcount = sphwcount and num>@upshl then @upshl
  when @sphwcount = sphwcount and num<=@upshl then num
  when num > @upshl then @upshl
  else num
  end,
 @goodsidnext = goodsid,
 @locatidnext = locatid

对于上面的语句，如何修改成oracle的呢，首先我们要明白上面的逻辑，之前也询问过一些sql 的相关人员，有人认为这就是一个基本的case when，其实这条语句是一条循环update，下面就是转换成oracle的写法：

    open cv_2;
             fetch cv_2 into v_count2,goodsid,upshl,sphwcount,num,locatid;
             while cv_2%found loop
             v_upshl:=case when goodsid<>v_goodsidnext then upshl else v_upshl-v_fpsl end;
             v_sphwcount:=case when goodsid<>v_goodsidnext then 1 else v_sphwcount + 1 end;
             update tmppicils set biandongsl=case when v_sphwcount = sphwcount and num>v_upshl then v_upshl
            when v_sphwcount = sphwcount and num<=v_upshl then num
            when num > v_upshl then v_upshl
            else num end where id=v_count2 returning biandongsl into v_fpsl ;
            
            v_goodsidnext:= goodsid;
            v_locatidnext:= locatid;
            --v_count2:=v_count2+1;
            fetch cv_2 into v_count2,goodsid,upshl,sphwcount,num,locatid;
          end loop;
        close cv_2;