if.. then.. else用法
create or replace trigger DELETE_CUSTBANK before delete on bd_custbank for each row declare -- local variables here pk_bas char(20); cursor basjob(id char) is select pk_custbank from ncv5.bd_custbank /*目标数据库表*/ where pk_custbank = id and nvl(dr, 0) = 0 and pk_custbank not in (select bd_custbank.pk_custbank from ncv5.arap_djfb, ncv5.bd_accbank, ncv5.bd_custbank where bd_accbank.pk_accbank = arap_djfb.skyhzh and bd_accbank.pk_accbank = bd_custbank.pk_accbank); begin /*判断数据是否在目标数据库存在*/ open basjob(:old.pk_custbank); loop fetch basjob into pk_bas; /*dbms_output.put_line('pa_bas:'||pk_bas);*/ exit when basjob%notfound; end loop; close basjob; /******************************/ if pk_bas is not null then begin delete ncv5.bd_custbank /*目标数据库表*/ where pk_custbank = :old.pk_custbank; end; else raise_application_error(-20001, :old.account || '已从网银付款,不能修改或删行,请取消!'); end if; end DELETE_CUSTBANK;
重要声明:cursor中是not in 这里的是“否”,然后 if pk_bas is not null then
不能理解为“否”而需要理解“数值存在”,这样就是找到非付款客商了,then可以同步删除,否则报错