• Cursor cursor_name Is your_select_statement
  • Cursor cursor_name(param TYPE) Is your_select_statement_using_param
  • FOR x in (your_select_statement) LOOP ...


Declared Cursors are difficult to use, and you should prefer FOR loops in most cases. What's very interesting in cursors compared to simple FOR loops, is that you can parameterize them.

It's better to avoid doing loops with PL/SQL and cursors instead of using Oracle SQL anyway. However, For people accustomed to procedural language, it can be far easier to understand.

If you want to check if a record exists, and then do different things depending on whether the record exists or not, then it makes sense to use MERGE statements in pure ORACLE SQL queries instead of using cursor loops. (Please note that MERGE is only available in Oracle releases >= 9i).