Interacting with the Oracle Database Server: SQL Statements in PL/SQL
SELECT INTO
A SELECT ... INTO must return exactly one row. Zero rows raises NO_DATA_FOUND; two or more raises TOO_MANY_ROWS. The classic exception is an aggregate with no GROUP BY, which always returns exactly one row - SELECT SUM(sal) INTO v FROM emp WHERE 1=0 succeeds and leaves v NULL. A query with GROUP BY returns one row per group, so it can still raise TOO_MANY_ROWS.
Implicit cursor attributes
After DML you can read SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT and SQL%ISOPEN. They describe the most recent SQL statement, and any intervening statement resets them, so copy the values into variables immediately. SQL%ISOPEN is always FALSE for implicit cursors. After a successful SELECT INTO, SQL%ROWCOUNT is 1.
Transaction control and RETURNING
COMMIT, ROLLBACK and SAVEPOINT are legal inside PL/SQL; an exception handler does not undo earlier DML automatically, so recovery logic must roll back or roll back to a savepoint explicitly. RETURNING column INTO variable on INSERT/UPDATE/DELETE retrieves generated keys or modified values without a second query.
Sequences
Since Oracle 11g, seq.NEXTVAL and seq.CURRVAL may appear in PL/SQL expressions, not only in SQL statements.
Rule of thumb: any single-row lookup you write must have a plan for both NO_DATA_FOUND and TOO_MANY_ROWS, and any query without a unique-key predicate is a candidate for TOO_MANY_ROWS.