Home
COMMIT should not be used inside a loop
Description
The rule "COMMIT should not be used inside a loop" states that the COMMIT statement should not be used inside a loop in PL/SQL code. This is because a COMMIT statement will cause the entire transaction to be committed, and any subsequent changes made within the loop will not be saved. Instead, the COMMIT statement should be used after the loop has been completed, so that all changes are committed at once.
Key Benefits
- Eliminates unnecessary commits: Committing inside a loop can lead to multiple commits being made when only one is necessary.
- Improves performance: Committing inside a loop can significantly slow down the execution of the loop.
- Maintains data integrity: Committing inside a loop can lead to data being committed before it is valid, which can cause data integrity issues.
Non-compliant Code Example
BEGIN
FOR i IN 1 .. CUSTOMER_TABLE.COUNT
LOOP
IF CUSTOMER_TABLE(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT( i || ' = (' || CUSTOMER_TABLE(i).Name || ', ' || CUSTOMER_TABLE(i).PhoneNumber || ')' );
ELSE
DBMS_OUTPUT.PUT( i || ' IS NULL' );
END IF;
COMMIT; --Non compliant code (COMMIT is used inside loop)
END LOOP;
END;
Compliant Code Example
BEGIN
FOR i IN 1 .. CUSTOMER_TABLE.COUNT
LOOP
IF CUSTOMER_TABLE(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT( i || ' = (' || CUSTOMER_TABLE(i).Name || ', ' || CUSTOMER_TABLE(i).PhoneNumber || ')' );
ELSE
DBMS_OUTPUT.PUT( i || ' IS NULL' );
END IF;
END LOOP;
COMMIT; --Compliant code (COMMIT is used outside loop)
END;