Objetivo
Use a instrução MERGE para selecionar linhas de uma ou mais fontes para atualização ou inserção em uma tabela ou view. Você pode especificar condições para determinar se deseja atualizar ou inserir na tabela ou view de destino.
Esta declaração é uma maneira conveniente de combinar várias operações e evita a necessidade de vários comandos DML como INSERT, UPDATE e DELETE, pois combina todos em uma instrução: MERGE. Você não pode atualizar a mesma linha da tabela de destino várias vezes na mesma instrução MERGE.
Pré requisitos
Você deve ter os privilégios INSERT e UPDATE do objeto na tabela de destino e o privilégio READ ou SELECT do objeto na tabela de origem. Para especificar a cláusula DELETE da merge_update_clause, você também deve ter o privilégio do objeto DELETE na tabela de destino.
MERGE [ hint ]
INTO [ schema. ] { table | view } [ t_alias ]
USING { [ schema. ] { table | view }
| subquery
} [ t_alias ]
ON ( condition )
[ merge_update_clause ]
[ merge_insert_clause ]
[ error_logging_clause ] ;
WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
[, column = { expr | DEFAULT } ]...
[ where_clause ]
[ DELETE where_clause ]
WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({ expr | DEFAULT }
[, { expr | DEFAULT } ]...
)
[ where_clause ]
Semântica
Cláusula INTO
Use a cláusula INTO para especificar a tabela de destino ou a view na qual você está atualizando ou inserindo. Para realizar a operação MERGE em uma view, ela deve ser atualizável. Consulte “Notas sobre views atualizáveis” (notes on updatables views) para obter mais informações. Restrição nas view de destino: Você não pode especificar uma view de destino na qual uma trigger INSTEAD OF foi definida.
Cláusula Using
Use a cláusula USING para especificar a fonte dos dados a serem atualizados ou inseridos. A fonte pode ser uma tabela, view ou o resultado de uma subconsulta.
Cláusula ON
Use a cláusula ON para especificar a condição na qual a operação MERGE será atualizada ou inserida. Para cada linha na tabela de destino para a qual a condição de pesquisa é verdadeira, o Oracle Database atualiza a linha com os dados correspondentes da tabela de origem. Se a condição não for verdadeira para nenhuma linha, o banco de dados será inserido na tabela de destino com base na linha da tabela de origem correspondente.
merge_update_clause
A merge_update_clause especifica os novos valores da coluna da tabela de destino. O Oracle executa essa atualização se a condição da cláusula ON for verdadeira. Se a cláusula de atualização for executada, todos os gatilhos de atualização definidos na tabela de destino serão ativados.
Especifique a cláusula where se desejar que o banco de dados execute a operação de atualização apenas se a condição especificada for verdadeira. A condição pode se referir à fonte de dados ou à tabela de destino. Se a condição não for verdadeira, o banco de dados ignorará a operação de atualização.
Especifique a DELETE where_clause para limpar os dados em uma tabela enquanto os preenche ou atualiza. As únicas linhas afetadas por esta cláusula são aquelas na tabela de destino que são atualizadas pela operação de mesclagem. A condição DELETE WHERE avalia o valor atualizado, não o valor original que foi avaliado pela condição UPDATE SET … WHERE. Se uma linha da tabela de destino atender à condição DELETE, mas não estiver incluída na associação definida pela cláusula ON, ela não será excluída. Quaisquer gatilhos de exclusão definidos na tabela de destino serão ativados para cada exclusão de linha.
Você pode especificar esta cláusula por si só ou com a merge_insert_clause. Se você especificar os dois, eles poderão estar em qualquer ordem.
Restrições na cláusula merge_update_clause
Você não pode executar um UPDATE em uma coluna que é referenciada na cláusula ON.
Você não pode especificar DEFAULT quando executa um UPDATE em uma VIEW.
merge_insert_clause
A merge_insert_clause especifica valores a serem inseridos na coluna da tabela de destino se a condição da cláusula ON for falsa. Se a cláusula de inserção for executada, todos os gatilhos de inserção definidos na tabela de destino serão ativados. Se você omitir a lista de colunas após a palavra-chave INSERT, o número de colunas na tabela de destino deverá corresponder ao número de valores na cláusula VALUES.
Para inserir todas as linhas de origem na tabela, você pode usar um predicado de filtro constante na condição da cláusula ON. Um exemplo de um predicado de filtro constante é ON (0 = 1). O Oracle Database reconhece esse predicado e faz uma inserção incondicional de todas as linhas de origem na tabela. Essa abordagem é diferente de omitir a merge_update_clause. Nesse caso, o banco de dados ainda deve executar uma associação. Com predicado de filtro constante, nenhuma associação é executada.
Especifique a cláusula where se desejar que o Oracle Database execute a operação de inserção apenas se a condição especificada for verdadeira. A condição pode se referir apenas à tabela da fonte de dados. O banco de dados Oracle ignora a operação de inserção de todas as linhas para as quais a condição não é verdadeira.
Você pode especificar esta cláusula por si só ou com a merge_update_clause. Você pode especificar em ambos.
Restrições na cláusula merge_insert_clause:
Você não pode especificar DEFAULT quando executa um INSERT em uma VIEW.
Exemplo
O exemplo a seguir usa a tabela bônus (bonuses) no esquema de amostra oe com um bônus padrão de 100. Em seguida, insere na tabela de bônus todos os funcionários que fizeram vendas, com base na coluna sales_rep_id da tabela oe.orders. Por fim, o gerente de recursos humanos decide que os funcionários com um salário igual ou inferior a US $ 8.000 devem receber um bônus. Aqueles que não realizaram vendas recebem um bônus de 1% de seu salário. Quem já realizou vendas obtém um aumento em seu bônus igual a 1% de seu salário. A instrução MERGE implementa essas alterações em uma etapa:
CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
INSERT INTO bonuses(employee_id)
(SELECT e.employee_id FROM employees e, orders o
WHERE e.employee_id = o.sales_rep_id
GROUP BY e.employee_id);
SELECT * FROM bonuses ORDER BY employee_id;
EMPLOYEE_ID BONUS
----------- ----------
153 100
154 100
155 100
156 100
158 100
159 100
160 100
161 100
163 100
MERGE INTO bonuses D
USING (SELECT employee_id, salary,
department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE
SET D.bonus = D.bonus + S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN
INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*.01)
WHERE (S.salary <= 8000);
SELECT * FROM bonuses
ORDER BY employee_id;
EMPLOYEE_ID BONUS
----------- ----------
153 180
154 175
155 170
159 180
160 175
161 170
164 72
165 68
166 64
167 62
171 74
172 73
173 61
179 62
desenvoracle.home.blog 