Oracle 相关知识点

来源：互联网发布：免费工资管理软件编辑：程序博客网时间：2024/05/22 00:36

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_ywafter insert on hfpsbz.t_pump_liquidlevelfor each rowdeclare  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_PUMPafter insert or update or delete on hfpsbz.PIP_PUMPfor each rowdeclare    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_DELETEafter delete  on  T_SMSfor   each  rowdeclare    --这里是关键的地方，在变量申明的地方，指定自定义事务处理。         pragma autonomous_transaction;   begin   insert into T_SMS_HISTORY select * from T_SMS where TID=:old.TID;    commit; end;

0 0