Explain COMMIT, ROLLBACK and SAVEPOINT in PL/SQL

A transaction spans from the first DML to the next COMMIT or ROLLBACK. COMMIT ends it and makes everything durable. ROLLBACK discards all uncommitted changes. SAVEPOINT name lets ROLLBACK TO name undo only the work done since that marker, keeping earlier changes pending. Best practice: let the top-level caller own the COMMIT — reusable procedures should not commit, so the caller can compose them into one atomic unit (an autonomous transaction is the explicit exception).

BEGIN
  INSERT INTO orders(order_id, customer_id) VALUES (5001, 42);

  SAVEPOINT before_lines;
  BEGIN
    INSERT INTO order_lines(order_id, line_id, item) VALUES (5001, 1, 'A');
    INSERT INTO order_lines(order_id, line_id, item) VALUES (5001, 1, 'B'); -- dup PK
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      ROLLBACK TO before_lines;         -- keep the order header, drop the lines
  END;

  COMMIT;
END;
/