oracle 触发器锁表,触发器锁表 - Oracle开发 - ITPUB论坛－中国专业的IT技术社区

create or replace trigger imei_info_oper

after insert on bass_user_imei_info

for each row

declare

i_imei varchar2(15);

i_brand varchar2(32);

i_model varchar2(32);

i_is_mms varchar2(1);

is_exist number;

err_code number;

begin

i_is_mms :='Y';

begin

begin

select substr(imei,1,8) into i_imei from ob_imei_info where user_nbr=:new.msisdn;

is_exist := 1;

exception

when no_data_found then

is_exist := 0;

i_imei := '';

end;

if (is_exist = 1) and (i_imei=substr(:new.imei,1,8)) then  --the old user is exist and phone model is same

null;

else  --the old user is no exist or user phone model is changed

begin

select c.brand_name,b.model_name,decode(b.is_mms,0,'N','Y') into i_brand,i_model,i_is_mms

from ob_mobile_imei_info a,ob_mobile_terminal_cfg b,ob_mobile_brand_cfg c

where a.imei_no=substr(:new.imei,1,8)

and a.terminal_id=b.terminal_id and b.brand_id=c.brand_id;

exception

when no_data_found then

begin

i_brand := 'NO DEFINE';

i_model := 'NO DEFINE';

i_is_mms := 'Y';

end;

end;

if is_exist = 1 then

update ob_imei_info set phone_brand = i_brand,

phone_model=i_model,

support_mms=i_is_mms,

update_time=sysdate,

imei=:new.imei

where user_nbr=:new.msisdn;

else

insert into ob_imei_info(user_nbr,

phone_brand,

phone_model,

support_mms,

update_time,

imei)

values(:new.msisdn,

i_brand,

i_model,

i_is_mms,

sysdate,

:new.imei);

end if;

end if;

end;

exception

when others then

err_code := sqlcode;

insert into fan_tmp values(:new.msisdn,:new.imei,err_code);

end imei_info_oper;

就几个表之间关联，然后统一更新到一个表去。上面说的同一个值不存在，因为我测试了只插入一个值，也会锁表，等待执行时间达到60秒。