Oracle SQL

Speed up frequently called lookup functions by caching their results for all sessions with RESULT_CACHE, invalidated automatically.

Declare pure PL/SQL functions DETERMINISTIC so Oracle can skip repeated calls and allow them in function-based indexes and virtual columns.

Follow an exception through blocks and procedures until a handler catches it, avoid the declaration-error trap, and log and re-raise.

Handle any Oracle error by name with PRAGMA EXCEPTION_INIT, and give your own RAISE_APPLICATION_ERROR errors names that callers can catch.

Find out which error a PL/SQL handler caught with SQLCODE and SQLERRM, and target common errors with predefined exception names.

Unite, intersect, and subtract nested tables like sets, and test membership and subsets, in PL/SQL and SQL without loops.

Build whole records and collections in a single expression, with values by position, by name, by index, or computed by an iterator.

Keep small ordered lists with a natural maximum, such as a top three, in varrays, and see how they compare with the other collections.

Work with unbounded lists in PL/SQL with nested tables, handle gaps after deletions, and pass lists of values to SQL with TABLE().

Store values under string or integer keys with associative arrays, use them as fast in-memory lookups, and handle missing keys safely.

Return one or more result sets from a PL/SQL block or procedure to the client with DBMS_SQL.RETURN_RESULT, without OUT cursor parameters.

Reuse one explicit cursor for many similar queries by giving it parameters with defaults, passed by position or by name.

Find out how many rows an UPDATE or DELETE changed, or whether it changed any, with the implicit cursor attributes of PL/SQL.

Step by any amount, cover several ranges, filter iterations, and loop over collection values, indexes, or pairs in one PL/SQL FOR loop.

Give PL/SQL types meaningful names with built-in limits, such as a 0 to 100 percentage, and let the language enforce them on every assignment.

Mark points in a transaction with SAVEPOINT and undo only the changes made after one with ROLLBACK TO SAVEPOINT, keeping earlier work.

Get the values before and after an update in the same statement with RETURNING OLD and NEW, for a single row or many with BULK COLLECT.

Keep a bulk INSERT or UPDATE going when some rows fail: log the bad rows to an error table with LOG ERRORS and load the rest.