Using Explicit Cursors
Lifecycle
Declare the cursor with a name, optional parameters, and its SELECT; then OPEN, FETCH ... INTO ..., and CLOSE. A closed cursor may be reopened, and a parameterised cursor (CURSOR c(p_dept NUMBER) IS ...) makes the WHERE clause reusable - the parameter exists only inside the cursor query.
Attributes
%ISOPEN, %FOUND, %NOTFOUND, %ROWCOUNT. After OPEN, %ROWCOUNT is 0 and %FOUND is NULL; each successful FETCH increments %ROWCOUNT, and %NOTFOUND becomes TRUE only once a fetch returns no row. Touching %FOUND, %NOTFOUND or %ROWCOUNT on a cursor that is not open raises INVALID_CURSOR. A common trap is testing %ROWCOUNT before the first fetch and expecting a meaningful value.
Cursor FOR loops
The FOR r IN c LOOP form opens, fetches and closes automatically, declares r implicitly as c%ROWTYPE, and still closes the cursor if the body raises an exception. It is the safest pattern and the answer to most "which form avoids leaks" questions.
Locking rows
FOR UPDATE [OF column_list] [NOWAIT] locks the fetched rows, and WHERE CURRENT OF cursor_name in an UPDATE or DELETE modifies exactly the current row. NOWAIT raises ORA-00054 rather than waiting; SKIP LOCKED (11g) ignores already-locked rows.
Rule of thumb: always CLOSE a manually managed cursor in the same block that opened it, and never rely on %ROWCOUNT or %NOTFOUND before the first FETCH.