There are two types that I know of.

The first type might be like you would process an Adjacency List Hierarchy. You read the top level with one select. Then you start a loop based on a rowcount > 0. The loop reads an entire level at each iteration. To wit, the loop is reading sets of rows with one iteration per set.

The other type is probably not what most people would expect. R. Barry Young coined the phrase for these "set based loops" (which is exactly what I used to call them). The phrase is "Pseudo-Cursor" and every INSERT, SELECT, UPDATE, and DELETE uses them. They are the machine language level loops behind the scenes that cause (for example) a SELECT to return a result set.

You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.