CREATE OR REPLACE TRIGGER TRI_Cellid_Last
AFTER INSERT ON t_Cellid_Log
FOR EACH ROW
BEGIN
if INSERTING THEN
merge into t_cellid_last last
using t_cellid_log log
on (log.mcc=last.mcc and log.mnc=last.mnc and log.lac=last.lac and log.cellid=last.cellid)
WHEN MATCHENED THEN UPDATE
SET last.longitude=:new.longitude,last.latitude=:new.latitude,last.inaccuracy=:new.inaccuracy
where mcc=:new.mcc and mnc=:new.mnc and lac=:new.lac and cellid=:new.cellid
WHEN NOT MATCHED THEN INSERT
values(seq_cell_last_id.nextVal,:new.mcc,:new.mnc,:new.lac,:new.cellid,:new.longitude,:new.latitude,:new.inaccuracy,:new.UTCTIME,:new.TIMELOG)
end if;
END;
AFTER INSERT ON t_Cellid_Log
FOR EACH ROW
BEGIN
if INSERTING THEN
merge into t_cellid_last last
using t_cellid_log log
on (log.mcc=last.mcc and log.mnc=last.mnc and log.lac=last.lac and log.cellid=last.cellid)
WHEN MATCHENED THEN UPDATE
SET last.longitude=:new.longitude,last.latitude=:new.latitude,last.inaccuracy=:new.inaccuracy
where mcc=:new.mcc and mnc=:new.mnc and lac=:new.lac and cellid=:new.cellid
WHEN NOT MATCHED THEN INSERT
values(seq_cell_last_id.nextVal,:new.mcc,:new.mnc,:new.lac,:new.cellid,:new.longitude,:new.latitude,:new.inaccuracy,:new.UTCTIME,:new.TIMELOG)
end if;
END;
解决方案 »
- 数据select出来的单个字段数据集转换成字符串
- oracle 11g 问题,待高手帮忙!!!
- 透明网关从sqlserver中取数据插入到oracle commit不成功
- 统计sql问题
- 求和大于某个数
- Oracle 异常处理中的then后能给变量赋值吗?如果可以怎么赋值?
- 在redhat 8安装oracle9i 建数据库时出现的一个问题!高手请进!问了好多人都不人解答
- 关于动态游标的问题?比较急。
- dba高手:请问Oracle Migration Workbanch(sql7.0插件)进行数据移植时,出现 “fialed to load source model.数据大小超出此类型的最大
- 远程连接oracle问题“The Network Adapter could not...............
- group by 后按分类给出各自的序号
- 主键、唯一约束问题
UPDATE t_cellid_last
SET longitude=:new.longitude,latitude=:new.latitude,inaccuracy=:new.inaccuracy
where mcc=:new.mcc and mnc=:new.mnc and lac=:new.lac and cellid=:new.cellid;
if sql%NOTFOUND then --如果修改时没有找到对应的记录,则插入新记录
INSERT into t_cellid_last
values( seq_cell_last_id.nextVal,:new.mcc,:new.mnc,:new.lac,:new.cellid,:new.longitude,:new.latitude,:new.inaccuracy,:new.UTCTIME,:new.TIMELOG);
end if;
end if;
而且即使能成功,每插入一条数据都去merge一次,性能很糟糕吧
可以隔一段时间定时同步,或者不要merge,改成下面的试试
CREATE OR REPLACE TRIGGER TRI_Cellid_Last
AFTER INSERT ON t_Cellid_Log
FOR EACH ROW
declare
v_count number;
BEGIN
select count(1) into v_count from t_cellid_last last
where :new.mcc=last.mcc and :new.mnc=last.mnc and :new.lac=last.lac and :new.cellid=last.cellid and rownum<2;
if v_count>0 then
update t_cellid_last set longitude=:new.longitude,latitude=:new.latitude,inaccuracy=:new.inaccuracy
where mcc=:new.mcc and mnc=:new.mnc and lac=:new.lac and cellid=:new.cellid;
else
insert into t_cellid_last values(seq_cell_last_id.nextVal,:new.mcc,:new.mnc,:new.lac,:new.cellid,:new.longitude,:new.latitude,:new.inaccuracy,:new.UTCTIME,:new.TIMELOG);
end if;
END;
而MERGE语句一次则进行批量更新
这个是矛盾的,肯定存在问题!