oracle 存储过程使用实例（含事物和异常处理）

create or replace procedure ZMD_GIFCONVERT(p_productcode  varchar2, 
                                           p_storecode    varchar2, 
                                           p_qty          integer, 
                                           p_creditsingle integer, 
                                           p_userID       varchar2, 
                                           p_deptID       varchar2, 
                                           p_CardNO       varchar2, 
                                           p_IMEIs        varchar2, 
                                           p_IsIMEI       integer, 
                                           p_transcode    varchar2, 
                                           Retunr_Result  out varchar2 
                                           ) as
  --自定义异常
  APP_EXP exception;
  Curr_Qty           integer; --记录当前的库存值，用来判断是否够数
  after_Deduction    integer; --记录扣除后的数量
  Curr_crdeit_eff    integer; --记录当前可以会员积分
  v_Transid          integer; --获取积分兑换的ID号
  v_Imei_inventoryID integer; --获取IMEI条码的序号
  v_transitemid      integer; --获取条码表的序列号
begin
  Retunr_Result   := '';
  after_Deduction := p_qty; --初始化扣除后的数量
  Curr_crdeit_eff := 0;
  select ZMD_GIFTCONVERT_HEADER_SEQ.nextval into v_Transid from dual;


  --修改会员积分表
  --获取有效积分
  select nvl(CREDIT_EFF, 0)
    into Curr_crdeit_eff
    from zmd_hy_card
   where cardNO = p_CardNO;
  --判断积分是否够用
  if (Curr_crdeit_eff < p_qty * p_creditsingle) then
    Retunr_Result := '可用积分数量（<b style=''color:Red''>'||to_char(Curr_crdeit_eff)||'</b>）小于了当前所需积分（<b style=''color:Red''>'||to_char(p_qty * p_creditsingle)||'</b>）';
    raise APP_EXP; --抛出异常
  else
    update zmd_hy_card
       set CREDIT_EFF  = nvl(CREDIT_EFF, 0) - (p_qty * p_creditsingle),
           CREDIT_USED = nvl(CREDIT_USED, 0) + (p_qty * p_creditsingle),
           lastupdby   = p_userID,
           lastupddttm = sysdate
     where cardNO = p_CardNO;
  end if;


  --保存数据到积分兑换表中去
  insert into zmd_giftconvert_header
    (transid,
     transcode,
     dept,
     custom,
     productcode,
     storecode,
     qty,
     creditsingle,
     credits,
     lastupdby,
     lastupddttm)
  values
    (v_Transid,
     p_transcode,
     p_deptID,
     p_CardNO,
     p_productcode,
     p_storecode,
     p_qty,
     p_creditsingle,
     p_qty * p_creditsingle,
     p_userID,
     sysdate);


  --没有条码限制的情况下扣除库存的方法 
  if (p_IsIMEI = 0) then
    --如果库存数量满足的情况下，要减库存
    declare
      v_inventoryID zmd_inventory.inventoryid%type;
      v_qty         zmd_inventory.qty%type;
      cursor Deduction_Stock is
        select INVENTORYID, nvl(qty, 0) as qty
          from zmd_inventory
         where productcode = p_productcode
           and storecode = p_storecode
           and  nvl(IMEI,'A')='A'
         order by INVENTORYID desc;
    begin
      open Deduction_Stock;
      LOOP
        fetch Deduction_Stock

          into v_inventoryID, v_qty;

exit when Deduction_Stock%notfound;

        --判断库存数量 
        select sum(nvl(qty, 0))
          into Curr_Qty
          from zmd_inventory
         where productcode = p_productcode
           and storecode = p_storecode
           and  nvl(IMEI,'A')='A';
        if (Curr_Qty < after_Deduction) then
          Retunr_Result := '兑换数量超过了最大库存（<b style=''color:Red''>' || to_char(Curr_Qty) || '</b>）';
          raise APP_EXP; --抛出异常
        end if;
        -- 如果库存数量大于
        
        if (after_Deduction > 0) then
          if (after_Deduction >= NVL(v_qty,0)) then
            --如果数量大于库存数量，直接将库存的此条数据删除
            delete zmd_inventory where INVENTORYID = v_inventoryID;
            after_Deduction:=after_Deduction-nvl(v_qty,0);
          else
            --如果数量小于库存数量的直接修改数量列（qty）
           --dbms_output.put_line(v_inventoryID);
            update zmd_inventory
               set qty = NVL(v_qty,0) - after_Deduction
             where INVENTORYID = v_inventoryID;
             after_Deduction:=0;
          end if;
        

        end if;

      end loop;
      close Deduction_Stock;
    end;
  
  end if;


  --=======================如果有条码管控的时候的操作
  if (p_IsIMEI = 1) then
    --有条码的时候的操作
    --select sum(nvl(qty, 0)) into Curr_Qty from zmd_inventory;
    --首先要判断是否存在商品交易信息
    declare
      v_IMEI zmd_giftconvert_imei.sn%type;
      cursor Cur_IMEI is
        select *
          From table(split(p_IMEIs, ';'))
         where column_value is not null;
    begin
      open Cur_IMEI;
      Loop
        fetch Cur_IMEI

          into v_IMEI;

 exit when Cur_IMEI%notfound;

        --执行存储和扣除库存
        select nvl(qty, 0)
          into Curr_Qty
          from zmd_inventory
         where productcode = p_productcode
           and storecode = p_storecode
           and IMEI = v_IMEI;
        if (Curr_Qty = 0) then
          Retunr_Result := '兑换数量超过了最大库存（<b style=''color:Red''>' || to_char(Curr_Qty) || ',条码编号：' +
                           v_IMEI + '</b>）';
          raise APP_EXP; --抛出异常
        end if;
      
        select INVENTORYID
          into v_Imei_inventoryID
          from zmd_inventory
         where productcode = p_productcode
           and storecode = p_storecode
           and IMEI = v_IMEI;
        --扣除库存
        --如果条码管控的产品，直接将库存的此条数据删除
        delete zmd_inventory where INVENTORYID = v_Imei_inventoryID;
        select zmd_giftconvert_imei_SEQ.Nextval
          into v_transitemid
          from dual;
        --保存数据
        insert into zmd_giftconvert_imei
          (transitemid,
           transid,
           transcode,
           sn,
           productcode,
           storecode,
           lastupddttm)
        values
          (v_transitemid,
           v_Transid,
           p_transcode,
           v_IMEI,
           p_productcode,
           p_storecode,
           sysdate);
        --========================
        
       
      end loop;
      close Cur_IMEI;
    end;
  end if;
  COMMIT;


exception
  WHEN APP_EXP THEN
    rollback;
  WHEN OTHERS THEN
    rollback;
  
end;