Wednesday, February 24, 2010

Oracle SQL - Usando MERGE e error log

Já encontrei muitos códigos SQL onde se poderia usar o comando MERGE para resolver o problema.

Sintaxe do comando MERGE:

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];


O MERGE normalmente é usado nas situações em que já se tem uma tabela populada(alvo) e necessita inserir os registros da tabela origem que ainda não se encontram na tabela alvo.
Digamos que sua tabela alvo foi atualizada semana passada.Então hoje você resolve fazer um MERGE entre as linhas para caso tenha ocorrido alteração na tabela origem que esta seja propagada/efetuada na tabela alvo via UPDATE .

O interessante então no MERGE são as opções WHEN MATCHED THEN usando INSERT ou UPDATE.E também a cláusula DELETE .

Vamos fazer um exemplo de merge usando a tabela PRODUCTS do schema OE.


dbajcc@DBPOST on 24-FEB-10 >desc  oe.products;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 PRODUCT_ID                                            NOT NULL NUMBER(6)
 LANGUAGE_ID                                                    VARCHAR2(3)
 PRODUCT_NAME                                                   NVARCHAR2(125)
 CATEGORY_ID                                                    NUMBER(2)
 PRODUCT_DESCRIPTION                                            NVARCHAR2(2000)
 WEIGHT_CLASS                                                   NUMBER(1)
 WARRANTY_PERIOD                                                INTERVAL YEAR(2) TO MONTH
 SUPPLIER_ID                                                    NUMBER(6)
 PRODUCT_STATUS                                                 VARCHAR2(20)
 LIST_PRICE                                                     NUMBER(8,2)
 MIN_PRICE                                                      NUMBER(8,2)
 CATALOG_URL                                                    VARCHAR2(50)

dbajcc@DBPOST on 24-FEB-10 >select count(*) from oe.products;

  COUNT(*)
----------
       288

Elapsed: 00:00:00.01
dbajcc@DBPOST on 24-FEB-10 >


Vamos criar uma tabela cópia da tabela oe.products mas no schema dbajcc

dbajcc@DBPOST on 24-FEB-10 >create table jcc_products
  2   as select * from oe.products where rownum=0 ;

Table created.

Elapsed: 00:00:01.57
dbajcc@DBPOST on 24-FEB-10 >

Vamos inserir exatamente 100 linhas nesta nova tabela,lembrando que na tabela oe.products há 288 linhas :

dbajcc@DBPOST on 24-FEB-10 >
dbajcc@DBPOST on 24-FEB-10 >insert into jcc_products
  2  select * from oe.products
  3  where rownum < 101 ;

100 rows created.

Elapsed: 00:00:00.20
dbajcc@DBPOST on 24-FEB-10 >commit ;

Commit complete.

Elapsed: 00:00:00.01
dbajcc@DBPOST on 24-FEB-10 >

Agora vamos usar o MERGE para inserir os outros 188 registros .

Vamos usar a package DBMS_ERRLOG para criar uma tabela de captura de erros:

dbajcc@DBPOST on 24-FEB-10 >EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('jcc_products', 'jcc_products_log');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30
dbajcc@DBPOST on 24-FEB-10 >

Usando o Merge:

dbajcc@DBPOST on 24-FEB-10 >
dbajcc@DBPOST on 24-FEB-10 >merge into jcc_products jp
  2  using oe.products p
on (jp.product_id = p.product_id)
when not matched then insert
  3    4    5  ( jp.language_id,
jp.product_name,
jp.category_id,
jp.product_description,
jp.weight_class,
jp.warranty_period,
jp.supplier_id,
jp.product_status,
jp.list_price,
jp.min_price ,
jp.catalog_url
)
values
( p.language_id,
 p.product_name,
 p.category_id,
 p.product_description,
 p.weight_class,
 p.warranty_period,
 p.supplier_id,
 p.product_status,
 p.list_price,
 p.min_price ,
  6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   p.catalog_url                             
 ); 29

188 rows merged.

Elapsed: 00:00:00.13
dbajcc@DBPOST on 24-FEB-10 >commit ;

Commit complete.

Elapsed: 00:00:00.01
dbajcc@DBPOST on 24-FEB-10 >
dbajcc@DBPOST on 24-FEB-10 >SELECT ORA_ERR_MESG$, ORA_ERR_TAG$ from  jcc_products_log ;

no rows selected

Elapsed: 00:00:00.00
dbajcc@DBPOST on 24-FEB-10 >


Principalmente para quem trabalha com ETL o MERGE é essencial !

No comments: