Employ DMLs With Multiple Records

It is learned to be a common practice to use DMLs (Data Modification Language/INSERTs, UPDATEs) that operate on a single row recursively, as opposed to issuance of a single statement operates on multiple records. Albeit this may be a good coding style in ordinary business logic, it is considered to be suboptimal under the scope of upgrade performance.

This chapter will outline few examples of such DMLs, and they can be employed on occasions such as Avoid cursor loops.

INSERT Statements

Single Row INSERT Statements

DECLARE
   CURSOR get_employees IS
      SELECT t.employee_id, SUM(t.sales) tot_sales
      FROM   SALES_PER_EMPLOYEE_TAB t
      GROUP  BY t.employee_id;
BEGIN
   FOR rec_ IN get_employees LOOP
      INSERT INTO EMPLOYEE_TAB(EMPLOYEE_ID, EMPLOYEE_NAME, REVENUE) VALUES(rec_.employee_id, 'AAAAAA', rec_.tot_sales);
   END LOOP;

   COMMIT;
END;
/

Multi Row INSERT Statement

The above recursive call to single row statement can be replaced by more efficient statement as follows:

INSERT INTO EMPLOYEE_TAB(EMPLOYEE_ID, EMPLOYEE_NAME, REVENUE)
   SELECT t.employee_id, 'AAAAAA', SUM(t.sales) tot_sales
   FROM   SALES_PER_EMPLOYEE_TAB t
   GROUP  BY t.employee_id;

UPDATE Statements

Similar to INSERT statements, UPDATE statements are also capable of altering multiple records simultaneously.

If the assignment values need to be fetched from a subquery, then multiple assignment values can be fetched simultaneously from the same query to be SET onto multiple table columns using the following notation:

UPDATE dest_tab tt
SET    (tt.code, tt.description) = (SELECT st.code, st.description
                                    FROM   source_tab st
                                    WHERE  st.id = tt.id)
WHERE  EXISTS (SELECT 1
               FROM   source_tab
               WHERE  id = tt.id);

MERGE Statements

MERGE statement is a powerful instruction that combines the functionalities if INSERT and UPDATE into a single command. It refers to a data source that can be either a table or subquery and INSERTs and/or UPDATEs the records of a destination according to it.

MERGE INTO  EMPLOYEE_TAB e
      USING (SELECT t.employee_id, SUM(t.sales) tot_sales
             FROM   SALES_PER_EMPLOYEE_TAB t
             GROUP  BY t.employee_id) s
      ON    (e.employee_id = s.employee_id)
      WHEN  MATCHED THEN
            UPDATE  SET e.revenue = s.tot_sales
      WHEN  NOT MATCHED THEN
            INSERT (employee_id,   employee_name, revenue)
            VALUES (s.employee_id, 'XXXXXX',      s.tot_sales);

The above command SETs the total sales on the subquery onto EMPLOYEE_TAB.revenue if employee_id on subquery is found on EMPLOYEE_TAB, or else it will INSERT a new record to it.