关于查询插入以及查询更新实例sql&触发器示例

工作札记

 

查询插入：

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;
/