На данный момент я использую следующее обновление или вставку инструкции Oracle:
BEGIN
UPDATE DSMS
SET SURNAME = :SURNAME
WHERE DSM = :DSM;
IF (SQL%ROWCOUNT = 0) THEN
INSERT INTO DSMS
(DSM, SURNAME)
VALUES
(:DSM, :SURNAME);
END IF;
END;
Это работает нормально, за исключением того, что оператор обновления выполняет фиктивное обновление, если данные совпадают с предоставленными значениями параметров. Я бы не возражал против фиктивного обновления в обычной ситуации, но над этой таблицей построена система репликации/синхронизации, использующая триггеры в таблицах для захвата обновленных записей, и частое выполнение этого оператора для многих записей просто означает, что я вызову огромный трафик в триггерах и система синхронизации.
Есть ли какой-нибудь простой способ переформулировать этот код, чтобы оператор обновления не обновлял запись, если в этом нет необходимости, без использования следующего кода проверки IF-EXISTS, который я считаю недостаточно гладким и, возможно, также не самым эффективным для этой задачи?
DECLARE
CNT NUMBER;
BEGIN
SELECT COUNT(1) INTO CNT FROM DSMS WHERE DSM = :DSM;
IF SQL%FOUND THEN
UPDATE DSMS
SET SURNAME = :SURNAME
WHERE DSM = :DSM
AND SURNAME != :SURNAME;
ELSE
INSERT INTO DSMS
(DSM, SURNAME)
VALUES
(:DSM, :SURNAME);
END IF;
END;
Я также пытался использовать оператор MERGE INTO, но он не работает для обновлений, когда значение не изменяется (обновление ничего не изменяет, и вставка выполняется, но происходит нарушение PK).
Полный образец MERGE INTO:
CREATE TABLE DSMS(
dsm VARCHAR2(10) NOT NULL PRIMARY KEY,
surname VARCHAR2(10) NOT NULL
);
> Table created
-- :DSM = 'xx', :SURNAME = 'xx'
MERGE INTO DSMS D
USING (SELECT :DSM AS DSM,
:SURNAME AS SURNAME
FROM DUAL) V
ON (D.DSM = V.DSM)
WHEN MATCHED THEN
UPDATE
SET SURNAME = V.SURNAME
WHERE D.SURNAME <> V.SURNAME
WHEN NOT MATCHED THEN
INSERT (DSM, SURNAME)
VALUES (V.DSM, V.SURNAME);
> Ok - record inserted
-- :DSM = 'xx', :SURNAME = 'xx'
MERGE INTO DSMS D
USING (SELECT :DSM AS DSM,
:SURNAME AS SURNAME
FROM DUAL) V
ON (D.DSM = V.DSM)
WHEN MATCHED THEN
UPDATE
SET SURNAME = V.SURNAME
WHERE D.SURNAME <> V.SURNAME
WHEN NOT MATCHED THEN
INSERT (DSM, SURNAME)
VALUES (V.DSM, V.SURNAME);
> ORA-00001 - Unique constraint violated (PK violation)
Похоже, что Oracle использует UPDATE...IF SQL%ROWCOUNT=0 THEN INSERT... внутри предложения MERGE INTO? Второй оператор MERGE INTO терпит неудачу, потому что обновление ничего не обновляет, поэтому выполняется INSERT, что приводит к нарушению PK, поскольку строка уже существует, просто значения не изменились.
DSMS
наTestMerge
в вашем примере оба утверждения сработали для меня нормально. - person Quassnoi   schedule 26.05.201010.2.0.1.0
. Образец таблицы, который вы создали, называетсяTestMerge
, но в вашихMERGE
положениях вы используете таблицуDSMS
:MERGE INTO DSMS D …
. - person Quassnoi   schedule 27.05.2010