工作札记
?
查询插入：
insert into tempdevice(dpath,dname) select ttt1.devicepath,ttt1.displayname
????????? from tblnmdevice ttt1
????????? join tblbusinessdeviceunion ttt2 on ttt1.devicepath =
????????????????????????????????????????????? ttt2.devicepath
????????? join tblbusinesssys ttt3 on ttt3.tbluuid = ttt2.businessid
???????? where ttt3.systemcode = '/NM_Topo/A33'? ;
?
查询更新：
update tblnmdevice tt1
?? set tt1.displayname = (select t1.displayname||'路由器'
??????????????????????????? from tblnmdevice t1
?????????????????????????? where t1.devicepath = tt1.devicepath)
?where tt1.devicepath in
?????? (select ttt1.devicepath
????????? from tblnmdevice ttt1
????????? join tblbusinessdeviceunion ttt2 on ttt1.devicepath =
????????????????????????????????????????????? ttt2.devicepath
????????? join tblbusinesssys ttt3 on ttt3.tbluuid = ttt2.businessid
???????? where ttt3.systemcode = '/NM_Topo/A33' and ttt1.displayname not like '%路由器' and ttt3.pid is not null)
;
?
?
触发器示例：
?
prompt
prompt Creating trigger aiud_nmdevice
prompt ======================================
prompt
?
CREATE OR REPLACE TRIGGER aiud_nmdevice
? AFTER INSERT OR UPDATE OR DELETE ON tblnmdevice
FOR EACH ROW
DECLARE
??? v_count Number(20);
??? v_tablename VARCHAR(36) := 'Tblnmdevice';
??? v_uploadtype VARCHAR(50) := 'upload_actionlog';
??? v_primaryKey VARCHAR(36) := 'devicepath';
BEGIN
??? IF inserting THEN
????? IF :new.operationstatus IS NULL THEN
????? INSERT INTO tbluploadactionlog (logid, logtype, actiontype, tablename, dataid, actiontime, actionstate,dataname)
????? VALUES (SEQ_UPLOADACTION.Nextval, v_uploadtype, '1', v_tablename, :new.devicepath, SYSDATE, '1', v_primaryKey);
????? END IF;
??? ELSIF updating THEN
?????
??????? SELECT COUNT(log.logid) INTO v_count FROM tbluploadactionlog log WHERE log.dataid =:new.devicepath AND log.actiontype ='2';
??????? IF v_count = 0 THEN
????????? INSERT INTO tbluploadactionlog (logid, logtype, actiontype, tablename, dataid, actiontime, actionstate, dataname)
????????? VALUES (SEQ_UPLOADACTION.Nextval, v_uploadtype, '2', v_tablename, :new.devicepath, SYSDATE, '1', v_primaryKey);
??????? ELSIF v_count <> 0 THEN
????????? UPDATE tbluploadactionlog actionlog SET actionlog.actiontime = SYSDATE
????????? WHERE actionlog.dataid =:new.devicepath AND actionlog.actiontype='2';
??????? END IF;
?????
??? ELSIF deleting THEN
????? IF :old.operationstatus IS NULL THEN
????? INSERT INTO tbluploadactionlog (logid, logtype, actiontype, tablename, dataid, actiontime, actionstate, dataname)
????? VALUES (SEQ_UPLOADACTION.Nextval, v_uploadtype, '3', v_tablename, :old.devicepath, SYSDATE, '1', v_primaryKey);
????? END IF;
??? END IF;
END aiud_nmdevice;
/
?