Sql Server与Ｏracle存储过程转换

朴灿烈づ我的快乐病毒、 2022-06-13 12:26 156阅读 0赞

在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;