Oracle Multitable inserts - INSERT ALL & INSERT FIRST

Oracle 9i에서 소개된 Multitable insert는 한개의 테이블에 여러 row를 넣거나, 여러개의 테이블에 데이터를 한 insert 명령으로 넣을 수 있습니다.  이전 버전에서는 이런 기능은 PL/SQL로 구현을 해야 했으나 9i 이후로는 한 명령으로 수행할 수 있습니다.

INSERT ALL

INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)

VALUES (ord_nbr, cust_nbr, emp_id,ord_dt, ord_dt + 7, status)

INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)

VALUES (ord_nbr + 1, cust_nbr, emp_id,add_months(ord_dt, 1), add_months(ord_dt, 1) + 7, status)

INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)

VALUES (ord_nbr + 2, cust_nbr, emp_id,add_months(ord_dt, 2), add_months(ord_dt, 2) + 7, status)

INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)

VALUES (ord_nbr + 3, cust_nbr, emp_id,add_months(ord_dt, 3), add_months(ord_dt, 3) + 7, status)

INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)

VALUES (ord_nbr + 4, cust_nbr, emp_id,add_months(ord_dt, 4), add_months(ord_dt, 4) + 7, status)

INTO cust_order (order_nbr, cust_nbr, sales_emp_id,order_dt, expected_ship_dt, status)

VALUES (ord_nbr + 5, cust_nbr, emp_id,add_months(ord_dt, 5), add_months(ord_dt, 5) + 7, status)

SELECT 99990 ord_nbr, c.cust_nbr cust_nbr, e.emp_id emp_id,last_day(SYSDATE) ord_dt, 'PENDING' status

FROM customer c CROSS JOIN employee e

WHERE e.fname = 'MARY' and e.lname = 'TURNER'

and c.name = 'Gentech Industries';

INSERT ALL

INTO employee (emp_id, fname, lname, dept_id, hire_date) VALUES (eid, fnm, lnm, did, TRUNC(SYSDATE))

INTO salesperson (salesperson_id, name, primary_region_id) VALUES (eid, fnm || ' ' || lnm, rid)

SELECT 1001 eid, 'JAMES' fnm, 'GOULD' lnm,d.dept_id did, r.region_id rid

 FROM department d, region r

 WHERE d.name = 'SALES' and r.name = 'Southeast US';

위의 예와 같이 대상이 되는 모든 데이터를 같은/다른 테이블에 multi-row의 insert 가 가능하지만 특정 condition에 따라 insert도 가능합니다. 이 경우 INSERT FIRST를 사용하는데 이는 한개의 조건에 만족할 경우 나머지 조건은 skip되어 수행됩니다. 반대로 INSERT ALL의 경우 모든 조건을 판단하게 됩니다. 

INSERT FIRST

 WHEN order_dt < TO_DATE('2001-01-01', 'YYYY-MM-DD') THEN

  INTO cust_order_2000 (order_nbr, cust_nbr, sales_emp_id,sale_price, order_dt)

  VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)

 WHEN order_dt < TO_DATE('2002-01-01', 'YYYY-MM-DD') THEN

  INTO cust_order_2001 (order_nbr, cust_nbr, sales_emp_id,sale_price, order_dt)

  VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)

 WHEN order_dt < TO_DATE('2003-01-01', 'YYYY-MM-DD') THEN

  INTO cust_order_2002 (order_nbr, cust_nbr, sales_emp_id,sale_price, order_dt)

  VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)

SELECT co.order_nbr, co.cust_nbr, co.sales_emp_id,co.sale_price, co.order_dt

 FROM cust_order co

 WHERE co.cancelled_dt IS NULL

 AND co.ship_dt IS NOT NULL;