oracle如何检测处理异常事物,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 := '可用积分数量('||to_char(Curr_crdeit_eff)||')小于了当前所需积分('||to_char(p_qty * p_creditsingle)||')';

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 := '兑换数量超过了最大库存(' || to_char(Curr_Qty) || ')';

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 := '兑换数量超过了最大库存(' || to_char(Curr_Qty) || ',条码编号：' +

v_IMEI + ')';

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;