O comando MERGE é utilizado para selecionar linhas
de uma ou mais tabelas (origem) para
inserções ou alterações em uma ou mais tabelas (destino).
Este comando permite combinar várias operações para reduzir a complexidade de inserções e e atualizações. Com esse comando é possível combinar as instruções de INSERT, UPDATE e DELETE, em uma única instrução.
Este
comando é mais frequentemente utilizado em aplicações de Data Warehousing durante os processos de ETL (Extract, Transform e Load - extração, transformação e carregamento).
Requisitos mínimos
Como o
comando MERGE combina operações
de INSERT, UPDATE e DELETE, o usuário deverá ter os privilégios de (gravação e leitura) na tabela (destino) e claro, o privilégio de leitura na tabela (origem). Para o uso da cláusula de DELETE na tabela (origem) também é necessário o privilégio.
Sintaxe do comando
MERGE [hints] INTO [nome_tabela]
USING [nome_tabela_visão_ou_consulta]
ON
([condição])
WHEN MATCHED THEN [cláusula_de_update]
DELETE
[cláusula_where]
WHEN NOT MATCHED THEN [cláusula_de_insert]
[LOG ERRORS [cláusula_de_log_erros][REJECT
LIMIT [inteiro | ilimitado]];
Criando o ambiente de testes
Para a criação do ambiente de
testes foi criado o bom e velho usuário SCOTT.
CREATE USER scott IDENTIFIED BY tiger
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE TO scott;
ALTER USER scott QUOTA UNLIMITED ON users;
Criação
das tabelas DEPT e DEPT_ONLINE.
CREATE TABLE dept
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
INSERT INTO DEPT VALUES (10, 'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',
'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',
'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS','BOSTON');
COMMIT;
CREATE TABLE dept_online
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
INSERT INTO DEPT_ONLINE VALUES (40, 'OPERATIONS', 'BOSTON');
INSERT INTO DEPT_ONLINE VALUES (20, 'RESEARCH DEV','DALLAS');
INSERT INTO DEPT_ONLINE VALUES (50, 'ENGINEERING', 'WEXFORD');
INSERT INTO DEPT_ONLINE VALUES (60, 'MANAGER', 'MIAMI');
COMMIT;
Exemplos de utilização do comando MERGE
1o. Exemplo
Este
exemplo irá mesclar os valores da tabela
DEPT_ONLINE com a tabela chamada DEPT:
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FROM dept_online;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
20 RESEARCH DEV DALLAS
50 ENGINEERING WEXFORD
60 MANAGER MIAMI
SQL> MERGE INTO dept d
USING (SELECT deptno, dname,
loc
FROM dept_online) o
ON
(d.deptno = o.deptno)
WHEN MATCHED THEN
UPDATE SET d.dname =
o.dname, d.loc = o.loc
WHEN
NOT MATCHED THEN
INSERT (d.deptno,
d.dname, d.loc)
VALUES (o.deptno,
o.dname, o.loc);
4 rows merged.
A tabela
DEPT passou de 4 registros para 6 registros após a execução do comando,
conforme apresentado abaixo:
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20
RESEARCH DEV DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ENGINEERING WEXFORD
60 MANAGER MIAMI
6 rows selected.
2o. Exemplo
Este exemplo mesclará os valores da tabela
em DEPT_ONLINE com a tabela
chamada DEPT, semelhante ao 1o.
exemplo, mas utilizando uma sintaxe diferente:
SQL> SELECT * FROM dept;
DEPTNO
DNAME LOC
---------- -------------- -------------
10
ACCOUNTING NEW YORK
20
RESEARCH DALLAS
30
SALES CHICAGO
40
OPERATIONS BOSTON
SQL> SELECT * FROM dept_online;
DEPTNO
DNAME LOC
---------- -------------- -------------
40
OPERATIONS BOSTON
20
RESEARCH DEV DALLAS
50
ENGINEERING WEXFORD
60
MANAGER MIAMI
SQL> MERGE INTO dept d
USING
dept_online o
ON
(d.deptno = o.deptno)
WHEN
MATCHED THEN
UPDATE SET d.dname = o.dname, d.loc = o.loc
WHEN NOT
MATCHED THEN
INSERT (d.deptno, d.dname, d.loc)
VALUES (o.deptno, o.dname, o.loc);
4
rows merged.
A tabela
DEPT passou de 4 registros para 6 registros após a execução do comando,
conforme apresentado abaixo:
SQL> SELECT * FROM dept;
DEPTNO
DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20
RESEARCH DEV DALLAS
30 SALES CHICAGO
40
OPERATIONS BOSTON
50
ENGINEERING WEXFORD
60 MANAGER MIAMI
6 rows selected.
3o. Exemplo
Este exemplo mesclará os valores literais (e não valores de outra tabela)
usando uma instrução SQL na tabela DEPT.
Importante: A primeira
vez que o comando MERGE for
executado, ele irá inserir o registro novo (50).
Quando executado pela segunda vez irá
atualizar o registro para DEPTNO 50, uma vez que já existe.
SQL> SELECT * FROM
dept;
DEPTNO DNAME LOC
----------
-------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
1a.
Execução
SQL> MERGE INTO
dept a
USING (SELECT 50 deptno,
'ENGINEERING' dname,
'WEXFORD' loc
FROM dual) b
ON (a.deptno = b.deptno)
WHEN NOT MATCHED THEN
INSERT VALUES (b.deptno, b.dname, b.loc)
WHEN
MATCHED THEN
UPDATE SET a.loc = 'WEXFORD, PA';
1 row merged.
Resultado
da tabela DEPT após a execução do comando MERGE (1a. vez):
SQL> SELECT * FROM
dept;
DEPTNO DNAME LOC
----------
-------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ENGINEERING WEXFORD
2a.
Execução
SQL> MERGE INTO
dept a
USING ( SELECT 50 deptno,
'ENGINEERING' dname,
'WEXFORD' loc
FROM dual) b
ON (a.deptno = b.deptno)
WHEN NOT MATCHED THEN
INSERT VALUES (b.deptno, b.dname, b.loc)
WHEN MATCHED THEN
UPDATE SET a.loc = 'WEXFORD, PA';
1 row merged.
Resultado
da tabela DEPT após a execução do comando MERGE (2a. vez):
SQL> SELECT * FROM
dept;
DEPTNO DNAME LOC
----------
-------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ENGINEERING WEXFORD, PA
4o. Exemplo
Neste
exemplo, o comando MERGE é executado com uma sintaxe diferente, gerando o mesmo resultado.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> MERGE INTO dept
USING dual ON (dept.deptno = 50)
WHEN NOT MATCHED THEN
INSERT VALUES (50, 'ENGINEERING', 'WEXFORD')
WHEN MATCHED THEN
UPDATE SET dept.loc = 'WEXFORD, PA';
1 row merged.
Resultado
da tabela DEPT após a execução do comando MERGE (1a. vez):
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ENGINEERING WEXFORD
SQL> COMMIT;
2a.
Execução
SQL> MERGE INTO dept
USING dual ON (dept.deptno = 50)
WHEN NOT MATCHED THEN
INSERT VALUES (50, 'ENGINEERING',
'WEXFORD')
WHEN MATCHED THEN
UPDATE SET dept.loc = 'WEXFORD, PA';
1 row merged.
Resultado
da tabela DEPT após a execução do comando MERGE (2a. vez):
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 ENGINEERING WEXFORD, PA
Referências: Oracle Database SQL Language
Reference, Release 2 (11.2).
MSc. Rubens Oliveira
DBA Oracle Consultor
olivert.dba@consultant.com
