ORDER BY DECODE(BLAH, [COLUMN NUMBER]) on a single column query. How does it work?

The ORDER BY can use one of three expressions. Firstly an alias of the select list, secondly the number of a column in the select list or thirdly an SQL expression which may use zero or more columns from the source tables.

So when you use ORDER BY SUBSTR(col,2,10) you order by a 10 character substring of the column value starting from the second character.

Similarly when use

ORDER BY decode(col,'DOG',1,'CAT',2,'EEL', 3, 5)

you translate DOG into value 1, CAT into value 2, EEL into value 3 and others into value 5. Then order by the resulting numeric value (ie DOG first, then CAT, then EEL, finally anything else).

You can achieve the same ordering using

ORDER BY decode(col,'DOG','A','CAT','B','EEL', 'C', 'D')

It creates the set that will be used for ordering.

If animal_type = l_type_to_be_matched, use a -1 as the sort value for that row
else if animal_type = l_current_type, use 0 as the sort value of that row
else if axt.type_search_priority is null then use 100 as the sort value of that row
else use axt.type_search_priority as the sort value for that row.

It gives a kind of conditional sort cirteria. Often used to ensure that certain items are always at the top or bottom of a sorted set.