Create Or Replace Trigger sq_data_test Before Insert On DATA
For Each Row
DECLARE
old_value NUMBER(10,3);
sub_value NUMBER(10,3);
countnum number(10);
Begin
Select seq_cdpt.nextval Into:new.sn From dual;
if(:new.channel_name=199) then--传感器传值是雨量
:new.up_state:=:new.value;
select count(*) into countnum from data_h where channel_name=199 and gateway_logo=:new.gateway_logo;
if(countnum=0) then
sub_value:=:new.value-0;---第一次传过来值
select value into old_value from (select * from (select * from data_h where channel_name=199
and gateway_logo=:new.gateway_logo order by data_time desc)a where rownum=1);
sub_value:=:new.value-old_value;end if;
if(sub_value>0) then
:new.value:=sub_value+4;
else if(sub_value<=0 and old_value<1000) then
:new.value:=sub_value+4;
else----计数器清零后,后面还要再加end if
:new.value:=:new.value;
end if;
end if;
End
标准格式:
1、declare subvalue number(10,3);
begin
subvalue:=0.000;
if(subvalue>=0) then
dbms_output.put_line('hehe');
else
dbms_output.put_line('good');
end if;
end";
2、声明两个参数(多个参数)
declare countnum number(10);
countnum_man number(10);
begin
select count(*) into countnum from data_h where gateway_logo='1200201408011086' and data_time=sysdate;
select count(*) into countnum_man from data_h where gateway_logo='1200201408011086' and channel_name=199 and value=20;
dbms_output.put_line(countnum_man);
if( countnum_man<=0) then
dbms_output.put_line('hehe');
end if;
end;