Oracle PL/SQL Best Practices

Writing SQL in PL/SQL

SQL-00: Establish and follow clear rules for how to write SQL in your application.

General SQL and Transaction Management

SQL-01: Qualify PL/SQL variables with their scope names when referenced inside SQL statements.

SQL-02: Use incremental COMMITs to avoid rollback segment errors when changing large numbers of rows.

SQL-03: Use autonomous transactions to isolate the effect of COMMITs and ROLLBACKs (Oracle8i).

Querying Data from PL/SQL

SQL-04: Put single-row fetches inside functions; never hard-code a query in your block.

SQL-05: Hide reliance on the dual table.

SQL-06: Define multi-row cursors in packages so they can be used from multiple programs.

SQL-07: Fetch into cursor records, never into a hard-coded list of variables.

SQL-08: Use COUNT only when the actual number of occurrences is needed.

SQL-09: Use a cursor FOR loop to fetch all rows in a cursor unconditionally.

SQL-10: Never use a cursor FOR loop to fetch just one row.

SQL-11: Specify columns to be updated in a SELECT FOR UPDATE statement.

SQL-12: Parameterize explicit cursors.

SQL-13: Use RETURNING to retrieve information about modified rows (Oracle8).

SQL-14: Use BULK COLLECT to improve performance of multi-row queries (Oracle8i).

Changing Data from PL/SQL

SQL-15: Encapsulate INSERT, UPDATE, and DELETE statements behind procedure calls.

SQL-16: Reference cursor attributes immediately after executing the SQL operation.

SQL-17: Check SQL%ROWCOUNT when updating or removing data that “should” be there.

SQL-18: Use FORALL ...