Multitable Insert e Merge

mercoledì 24 ottobre 2007 alle 24:00 | Pubblicato su Diario | Lascia un commento

Oracle fornisce anche un costrutto per fare delle insert su più tabelle contemporaneamente. L’utilizzo tipico di questo costrutto è all’interno di procedure ETL (Extraction, Transformation and Load) quelle procedure che preparano i dati per i sistemi di datawarehouse, ovvero di analisi dati. Anche questo costrutto era gia presente nella versione 9iR2.

Con una multitable insert è possibile fare un singolo passo attraverso i dati sorgente e caricare i dati in modo selettivo su più tabelle. E’ un’ottima alternativa o ad un approccio procedurale o ad più select insert che comporterebbero più passaggi sui dati sorgenti.

Il costrutto può lavorare in modo semplice, senza nessuna logica selettiva se non basata sulle colonne come in questo esempio che riporto dai manuali:



INSERT ALL
 INTO sales
 VALUES (product_id, customer_id, today, 3, promotion_id,quantity_per_day, amount_per_day)
 INTO costs
 VALUES (product_id, today, promotion_id, 3,product_cost, product_price)
SELECT TRUNC(s.sales_date) AS today, s.product_id, s.customer_id,
 s.promotion_id, SUM(s.amount) AS amount_per_day, SUM(s.quantity)
 quantity_per_day, p.prod_min_price*0.8 AS product_cost, p.prod_list_price
 AS product_price
FROM sales_activity_direct s, products p
WHERE s.product_id = p.prod_id AND TRUNC(sales_date) = TRUNC(SYSDATE)
GROUP BY TRUNC(sales_date), s.product_id, s.customer_id, s.promotion_id,
    p.prod_min_price*0.8, p.prod_list_price;
 

Oppure si può aggiungere una logica selettiva basata su condizioni “WHEN” come ad esempio:



INSERT ALL
WHEN promotion_id IN (SELECT promo_id FROM promotions) THEN
INTO sales VALUES (product_id, customer_id, today, 3, promotion_id,
quantity_per_day, amount_per_day)
INTO costs VALUES (product_id, today, promotion_id, 3,
product_cost, product_price)
WHEN num_of_orders > 1 THEN
INTO cum_sales_activity VALUES (today, product_id, customer_id,
promotion_id, quantity_per_day, amount_per_day, num_of_orders)
SELECT TRUNC(s.sales_date) AS today, s.product_id, s.customer_id,
s.promotion_id, SUM(s.amount) AS amount_per_day, SUM(s.quantity)
quantity_per_day, COUNT(*) num_of_orders, p.prod_min_price*0.8
AS product_cost, p.prod_list_price AS product_price
FROM sales_activity_direct s, products p
WHERE s.product_id = p.prod_id
AND TRUNC(sales_date) = TRUNC(SYSDATE)
GROUP BY TRUNC(sales_date), s.product_id, s.customer_id,
s.promotion_id, p.prod_min_price*0.8, p.prod_list_price;
 

Quando si vuole usare la condizione “WHEN” vi sono due possibilità:

  • INSERT FIRST, allora quando la trova un caso che soddisfa la condizione WHEN si ferma
  • INSERT ALL, allora valuta tutte i casi possibili che soddisfano la condizione WHEN

Ecco un esempio di INSERT FIRST:



INSERT FIRST WHEN (sum_quantity_sold > 10 AND prod_weight_class < 5) AND
 sum_quantity_sold >=1) OR (sum_quantity_sold > 5 AND prod_weight_class > 5) THEN
  INTO large_freight_shipping VALUES
      (time_id, cust_id, prod_id, prod_weight_class, sum_quantity_sold)
  WHEN sum_amount_sold > 1000 AND sum_quantity_sold >=1 THEN
  INTO express_shipping VALUES
      (time_id, cust_id, prod_id, prod_weight_class,
       sum_amount_sold, sum_quantity_sold)
WHEN (sum_quantity_sold >=1) THEN INTO default_shipping VALUES
      (time_id, cust_id, prod_id, sum_quantity_sold)
ELSE INTO incorrect_sales_order VALUES (time_id, cust_id, prod_id)
SELECT s.time_id, s.cust_id, s.prod_id, p.prod_weight_class,
       SUM(amount_sold) AS sum_amount_sold,
       SUM(quantity_sold) AS sum_quantity_sold
FROM sales s, products p
WHERE s.prod_id = p.prod_id AND s.time_id = TRUNC(SYSDATE)
GROUP BY s.time_id, s.cust_id, s.prod_id, p.prod_weight_class;
 

Fra le restrioni imposte per poter usare le multitable insert vale la pena di ricordare che la sottoquery non può usare sequence.

Un’altro comando utilizzabile nelle procedure ETL è il comando MERGE che permette di aggiornare o inserire righe secondo una condizione. Si tratta quindi un costrutto tipicamente usato per la sincronizzazione dei dati di un sistema di data warehouse. Ecco un esempio:



MERGE INTO products t USING products_delta s
ON (t.prod_id=s.prod_id)
WHEN MATCHED THEN UPDATE SET
  t.prod_list_price=s.prod_list_price, t.prod_min_price=s.prod_min_price
WHEN NOT MATCHED THEN INSERT (prod_id, prod_name, prod_desc, prod_subcategory,
  prod_subcategory_desc, prod_category, prod_category_desc, prod_status,
  prod_list_price, prod_min_price)
VALUES (s.prod_id, s.prod_name, s.prod_desc, s.prod_subcategory,
  s.prod_subcategory_desc, s.prod_category, s.prod_category_desc,
  s.prod_status, s.prod_list_price, s.prod_min_price);

Lascia un commento »

RSS feed for comments on this post. TrackBack URI

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Crea un sito o un blog gratuitamente presso WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: