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.
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;
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 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.