sql server存储过程迁移到oracle上时出现的问题?

Question

sql server中有一存储过程,里面有事务处理:
BEGIN TRANSACTION
      DECLARE   @fAvalibleNum    NUMERIC(28,8),
                  @fRealNumMain NUMERIC(28,8),  @fRealNumAss   NUMERIC(28,8),  @fCost                NUMERIC(28,8),  @fBorrowNum     NUMERIC(28,8), 
@fLendNum        NUMERIC(28,8),  @fMakingNum    NUMERIC(28,8), 
@fUnSendNum   NUMERIC(28,8),                          @fOnWayNum    NUMERIC(28,8),  @fOrderNum       NUMERIC(28,8) 
     SELECT  @fAvalibleNum=ISNULL(fAvalibleNum,0),
             @fRealNumMain=ISNULL(fRealNumMain,0),
             @fCost =ISNULL(fCost,0),
             @fBorrowNum=ISNULL(fBorrowNum,0),
             @fLendNum=ISNULL(fLendNum,0),
             @fMakingNum =ISNULL(fMakingNum,0),
             @fUnSendNum=ISNULL(fUnSendNum,0),
             @fOnWayNum =ISNULL(fOnWayNum,0),
             @fOrderNum=ISNULL(fOrderNum,0)
     FROM TB_INV_GOODS
     WHERE vcAspID = @vcAspID AND vcStoreCode = @storeCode 
           AND vcGoodsID = @goodsID
     IF @@ROWCOUNT<1
         INSERT TB_INV_GOODS(....)values(....)
     ELSE
         UPDATE TB_INV_GOODS set.......
     IF @@error != 0 
        BEGIN 
             ROLLBACK TRANSACTION 
             BREAK
        END
     ELSE
          COMMIT TRAN 
象这样的事务处理,我怎样在oracle中写?

Answer 1

create or replace procedure procName(vcAspID numeric(12,0), @storeCode varchar2(10),@goodsID varchar2(20)
)
RETURN NUMBER IS
  fAvalibleNum           NUMBER;
  fRealNumMainY_STATUS   NUMBER;
  fRealNumAss            NUMBER;
  fCost                  NUMBER;
  fLendNum               NUMBER;
  fMakingNum             number;
  fUnSendNum             NUMBER;
  fOnWayNum              NUMBER;
  fOrderNum              NUMBER;
  fBorrowNum             NUMBER;
     SELECT  nvl(fAvalibleNum,0),
             nvl(fRealNumMain,0),
             nvl(fCost,0),
             nvl(fBorrowNum,0),
             nvl(fLendNum,0),
             nvl(fMakingNum,0),
             nvl(fUnSendNum,0),
             nvl(fOnWayNum,0),
             nvl(fOrderNum,0)
             into 
             
             fAvalibleNum,          
             fRealNumMainY_STATUS  ,
             fRealNumAss           ,
             fCost                 ,
             fLendNum              ,
             fMakingNum            ,
             fUnSendNum            ,
             fOnWayNum             ,
             fOrderNum             ,
             fBorrowNum            
     FROM TB_INV_GOODS
     WHERE vcAspID = vcAspID AND vcStoreCode = storeCode 
           AND vcGoodsID = goodsID
           
     if not sql%found then      
         INSERT TB_INV_GOODS(....)values(....)
     ELSE
         UPDATE TB_INV_GOODS set.......
     end if;    
     commit work;
     return 0;
end  procName;    
没有调试，大致就这样写，仅供参考