当库中一条记录的起始日期(f2_1981)和截止日期(f3_1981)等于新增记录的起始日期(f2_1981)和截止日期(f3_1981),公告日期(f4_1981)小于新增记录的公告日期(f4_1981),且库中历史记录的那条是否最新(f11_1981)为“1”时,触发此条历史记录的是否最新(f11_1981)为“0”;其余情况不触发。这是我写的一段逻辑,但是测试没用,不知道问题出在哪里 请指教!!
if :new.F2_1981=nvl(V_F2_1981,0.01)
and :new.F3_1981=nvl(V_F3_1981,0.01)
and :new.F4_1981>nvl(V_F4_1981,0.01)
and nvl(V_F11_1981,0.01)=1 then
update wind.tb_object_1981 set F11_1981 = 0 where :new.F1_1981=F1_1981
and :new.F2_1981=V_F2_1981
and :new.F3_1981=V_F3_1981
and :new.F4_1981>V_F4_1981;
end if;
if :new.F2_1981=nvl(V_F2_1981,0.01)
and :new.F3_1981=nvl(V_F3_1981,0.01)
and :new.F4_1981>nvl(V_F4_1981,0.01)
and nvl(V_F11_1981,0.01)=1 then
update wind.tb_object_1981 set F11_1981 = 0 where :new.F1_1981=F1_1981
and :new.F2_1981=V_F2_1981
and :new.F3_1981=V_F3_1981
and :new.F4_1981>V_F4_1981;
end if;
那应该怎么写这个条件?直接 and V_F11_1981=1?
select F2_1981,F3_1981,F4_1981,F11_1981 into V_F2_1981,V_F3_1981,V_F4_1981,V_F11_1981
from wind.tb_object_1981
where F1_1981=:new.F1_1981
and F2_1981=:new.F2_1981
and F3_1981=:new.F3_1981
and F4_1981=:new.F4_1981;
exception when no_data_found then
V_F2_1981 := null;
V_F3_1981 := null;
V_F4_1981 := null;
V_F11_1981 := null;
end;
if :new.F2_1981=nvl(V_F2_1981,0.01)
and :new.F3_1981=nvl(V_F3_1981,0.01)
and :new.F4_1981>nvl(V_F4_1981,0.01)
and V_F11_1981=1 then
update wind.tb_object_1981 set F11_1981 = 0 where :new.F1_1981=F1_1981
and :new.F2_1981=V_F2_1981
and :new.F3_1981=V_F3_1981
and :new.F4_1981>V_F4_1981;
end if;
select F2_1981,F3_1981,F4_1981,F11_1981 into V_F2_1981,V_F3_1981,V_F4_1981,V_F11_1981
from wind.tb_object_1981
where F1_1981=:new.F1_1981
and F2_1981=:new.F2_1981
and F3_1981=:new.F3_1981
and F4_1981=:new.F4_1981;
exception when no_data_found then
V_F2_1981 := null;
V_F3_1981 := null;
V_F4_1981 := null;
V_F11_1981 := null;
end;
一个条件一个条件select
SQL Server的查询分析器可以单步调试SQL语句。不知道oracle下能不能单步调试。