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;