Oracle 相關知識點

1、為Oracle表中的型別為DATE的TIME欄位，向後加13天，SQL語句如下：

update st_bridge_waterlevel set TIME=to_date(to_char(TIME+13,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss');

2、液位超警戒觸發器

create or replace trigger tr_syit_bz_yw
after insert on hfpsbz.t_pump_liquidlevel
for each row
declare
  bzname   varchar2(20); 
  bzyw    numeric(10,4);
  dqyw    numeric(10,4);
  dt      date;
  dt2    date;
begin
   select sysdate-2/24 into dt2 from dual;
   select WARNV into bzyw from SYIT_BZ_V where PSID=:new.PSID;
   select PUMPNAME into bzname from SYIT_BZ_V where PSID=:new.PSID;
   dqyw := :new.liquidlevel;
   if :new.liquidlevel> bzyw then
       select NVL(max(warntm),dt2) into dt from hfoa.t_Sms where siteid=:new.PSID and warncontent like '%警戒水位%';
       if(ROUND(TO_NUMBER(:new.monitortime - dt) * 24)>=1) then
           insert into hfoa.t_Sms(sysid,siteid,sitename,warncontent,warntm)
           values('A',:new.psid,bzname,'['||bzname||'] '||:new.poolpump||'達到'||to_char(dqyw)||'釐米,已超過警戒水位'||to_char(dqyw-bzyw)||'釐米。',:new.monitortime);
       end if;
   end if;
end;

3、為PIP_PUMP建立insert、update和delete觸發器

create or replace trigger tr_SYIT_PIP_PUMP
after insert or update or delete on hfpsbz.PIP_PUMP
for each row
declare
    integrity_error exception;
    errno            integer;
    errmsg           char(200);
begin
   if inserting then
       insert into SYIT_BZ_V(PSID,PUMPNAME,PUMPTYPE,WARNV)
       values(:new.PSID,:new.PUMPNAME,:new.PUMPTYPE,100);
   elsif updating then 
       update SYIT_BZ_V set PSID = :new.PSID,PUMPNAME = :new.PUMPNAME,PUMPTYPE = :new.PUMPTYPE where BID = :OLD.BID;
   elsif deleting then 
       delete from SYIT_BZ_V where BID = :OLD.BID;
   end if;
   exception 
    when integrity_error then
       raise_application_error(errno, errmsg);
end;

4、T_SMS刪除觸發器

create or replace trigger  TR_SYIT_T_SMS_DELETE
after delete  on  T_SMS
for   each  row
declare
    --這裡是關鍵的地方，在變數申明的地方，指定自定義事務處理。     
    pragma autonomous_transaction;   
begin
   insert into T_SMS_HISTORY select * from T_SMS where TID=:old.TID;
    commit; 
end;