有时候在与数据库打交道时,要做很多业务逻辑判断,比如说,要判断某条数据在数据库里是否存在,存在了要更新,不存在才可以执行添加操作,或者存在了,要更新某些字段等等,数据量小的时候我们可以一一判断,但是一旦数据量大了,几万几十万甚至几百万,这......咳咳
最近就在做一个导入EXCEL的功能,需求和上面说的差不多,虽然用的是批处理,但从EXCEL中没读取一条数据还要到数据库里查询一遍,有没有才可以添加到批处理的集合里,才几千条数据,竟然要5、6分钟,<!--StartFragment --> ,想想,也学学触发器,写一个吧,每次添加的时候,让数据库自己做判断,存在了,就替换掉原来的,否则就直接添加,代码贴出来:
<!--StartFragment -->
CREATE OR REPLACE TRIGGER INBOUND_TIME_BAND_TRIGGER BEFORE INSERT
ON LANIC_INBOUND_TIME_BAND FOR EACH ROW
DECLARE
INTEGRITY_ERROR EXCEPTION;
COUNTSIZE NUMBER(10);
MONDAY VARCHAR2(20);
TUESDAY VARCHAR2(20);
WEDNESDAY VARCHAR2(20);
THURSDAY VARCHAR2(20);
FRIDAY VARCHAR2(20);
SATURDAY VARCHAR2(20);
SUNDAY VARCHAR2(20);
ERRNO NUMBER(10);
ERRMSG VARCHAR2(200);
BEGIN
IF INSERTING THEN
SELECT count(*) INTO COUNTSIZE
FROM LANIC_INBOUND_TIME_BAND M
WHERE :NEW.DESTINATION=M.DESTINATION AND :NEW.TIMEBAND =M.TIMEBAND;
IF (COUNTSIZE > 0) THEN
SELECT
M.MONDAY,
M.TUESDAY,
M.WEDNESDAY,
M.THURSDAY,
M.FRIDAY,
M.SATURDAY,
M.SUNDAY
INTO MONDAY,THURSDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY,SUNDAY
FROM LANIC_INBOUND_TIME_BAND M
WHERE :NEW.DESTINATION=M.DESTINATION AND :NEW.TIMEBAND =M.TIMEBAND;
UPDATE LANIC_INBOUND_TIME_BAND M
SET
M.MONDAY=MONDAY,
M.TUESDAY=TUESDAY,
M.WEDNESDAY=WEDNESDAY,
M.THURSDAY=THURSDAY,
M.FRIDAY=FRIDAY,
M.SATURDAY=SATURDAY,
M.SUNDAY=SUNDAY
WHERE :NEW.DESTINATION=M.DESTINATION AND :NEW.TIMEBAND =M.TIMEBAND;
END IF;
END IF;
EXCEPTION
WHEN INTEGRITY_ERROR THEN
RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
用到触发器的参考一下吧,这个例子比较简单。