函数例子

create or replace trigger tri_bef_upd_resentity
  before update on resentity
  for each row
declare
c_areacode      varchar2(100);
v_routeinfo     varchar2(100);
v_routeinfo1    varchar2(200);
v_seqnum        number(3);
v_resrouteid    number(9);
v_valcode       varchar2(60);
v_ifcheck       varchar2(2);
v_areacode      varchar2(20);
c_resentitycode varchar2(60);
v_resentitycode varchar2(60);
c_check         varchar2(2);
v_result        varchar2(2);
v_msg           varchar2(2000);
-- 实体是带设备的交接箱(remark字段)，如果更新了设备信息，要相应改变路由
cursor c1 is
select * from resroute where endresentityid = :old.id;
cursor c2 is
select * from resroute a where a.id in (select resrouteid from resroutedet b where b.endentityid = :old.id);
begin
if :new.resentitytypeid in (13, 33) then
--新旧都不为空，但是不同，更新路由
if :new.remark is not null and :old.remark is not null and :old.remark != :new.remark then
for r1 in c1 loop
delete from useresroute where resrouteid = r1.id;
rmroute.p_generate_useresroute(:new.remark, r1.id, r1.depth - 1, r1.beginresentityid, r1.endresentityid);
end loop;
--旧的不为空，新的为空，删除路由
elsif :old.remark is not null and :new.remark is null then
delete from useresroute where resrouteid in (select id from resroute where endresentityid = :old.id);
delete from resroutedet where resrouteid in (select id from resroute where endresentityid = :old.id);
delete from resroute where endresentityid = :old.id;
--旧的为空，新的不为空，新增路由
elsif :old.remark is null and :new.remark is not null then
if :new.resentitytypeid = 13 then
--DBMS_OUTPUT.PUT_LINE('****:' || :OLD.ID);
for r2 in c2 loop
select seq_resroute.nextval into v_resrouteid from dual;
v_routeinfo  := substr(r2.routeinfo, 0, instr(r2.routeinfo, ',' || :old.id || ',') + length(:old.id));
v_routeinfo1 := substr(r2.routeinfo1, 0, instr(r2.routeinfo1, ',' || :old.resentitycode || ',')) ||
:new.resentitycode;
insert into resroute
(id, beginresentityid, endresentityid, routeinfo, routeinfo1, depth, breakpoint, spareresnum, equiptype)
values
(v_resrouteid, r2.beginresentityid, :old.id, v_routeinfo, v_routeinfo1, r2.depth - 1, r2.breakpoint - 1,
r2.spareresnum, r2.equiptype);
select seqnum
into v_seqnum
from resroutedet
where resrouteid = r2.id and endentityid = :old.id and rownum = 1;
insert into resroutedet
(id, resrouteid, seqnum, entityid, porttypeid, portcoderuleid, beginportseqnum, endportseqnum, begincode,
endcode, ispreconnect, routedetinfo1, routedetinfo, resconnectid, endentityid)
select seq_resroutedet.nextval, v_resrouteid, seqnum, entityid, porttypeid, portcoderuleid, beginportseqnum,
endportseqnum, begincode, endcode, ispreconnect, routedetinfo1, routedetinfo, resconnectid,
endentityid
from resroutedet
where resrouteid = r2.id and seqnum <= v_seqnum;
rmroute.p_generate_useresroute(:new.remark, v_resrouteid, r2.depth - 2, r2.beginresentityid,
r2.endresentityid);
end loop;
end if;
end if;
--:NEW.INSTALLPLACE := SUBSTR(:NEW.INSTALLADDRESS,INSTR(:NEW.INSTALLADDRESS,'$%')+2);
--:NEW.INSTALLADDRESS := SUBSTR(:NEW.INSTALLADDRESS,0,INSTR(:NEW.INSTALLADDRESS,'$%')-1);
end if;
if instr(:new.installaddress, '$%') > 0 then
:new.installplace   := substr(:new.installaddress, instr(:new.installaddress, '$%') + 2);
:new.installaddress := substr(:new.installaddress, 0, instr(:new.installaddress, '$%') - 1);
end if;


select areacode into c_areacode from area where areatype = 'B';
if c_areacode = 'SHAOXING' then
--修改实体要同步给97
if :new.resentitytypeid in (70, 74, 67, 12, 13, 14, 15, 16, 19, 33) then
if :new.resentitytypeid in (19, 15, 14, 12, 70) then
c_resentitycode := :old.resentitycode;
v_resentitycode := :new.resentitycode;
else
c_resentitycode := :old.installplace;
v_resentitycode := :new.installplace;
c_check         := substr(v_resentitycode, 1, 1);
end if;
begin
select n.jqbm into v_areacode from gis.tejz n where n.bm = :new.areacode;
if c_check = 'E' then
v_areacode := 'E' || v_areacode;
end if;
exception
when others then
RAISE_APPLICATION_ERROR(-20901, '局向代码不能为空');
end;
if c_resentitycode is null then
RAISE_APPLICATION_ERROR(-20901, '97实体编码不能为空');
elsif v_areacode is null then
RAISE_APPLICATION_ERROR(-20901, '局向代码不能为空');
else
Inf_Sx97_Gis.p_Inf_all_resentity(2, c_resentitycode, v_resentitycode, :new.resentityname, :new.resentitytypeid, v_areacode,
:new.capacity, :new.installaddress, v_result, v_msg);
if v_result = 'F' then
RAISE_APPLICATION_ERROR(-20901, '插入实体表同步给97时出错，错误为：' || v_msg);
end if;
end if;
end if;
end if;
--特殊设备
if :new.resentitytypeid in (64, 65, 66, 68) then
--编码校验
select display
into v_ifcheck
from dictionary
where code = 'check_specialcode' and entityid = (select id from entity where entitycode = 'rm_buttonIfShow');
if v_ifcheck = 'Y' then
if :new.capacity = 0 then
raise_application_error(-20901, '线路数不能为0');
end if;
v_valcode := substr(:new.resentitycode, 0, 1);
if :new.resentitytypeid = 64 then
if v_valcode <> 'J' then
raise_application_error(-20901, '编码应以 J 开头');
end if;
elsif :new.resentitytypeid = 65 then
if v_valcode <> 'Q' then
raise_application_error(-20901, '编码应以 Q 开头');
end if;
elsif :new.resentitytypeid = 66 then
if v_valcode <> 'K' then
raise_application_error(-20901, '编码应以 K 开头');
end if;
end if;
end if;
end if;
end tri_bef_upd_resentity;
/