Oracle PL/SQL for DBAs

Chapter 2. Cursors

A cursor is a vehicle for extracting data from an Oracle database. Cursors determine the columns and objects (tables, views, etc.) from which data will be extracted, and they provide criteria to determine exactly which records should be extracted. To start off our discussion, here are two very simple example cursors:

    SELECT name
      FROM emp;

    CURSOR curs_get_emp IS
    SELECT name
      FROM emp;

Oracle DBAs are no doubt familiar with this basic cursor syntax but might describe the functionality (particularly of the first example) by using terms like “SELECT statement,” “query,” or “fetching data.” On the surface, that is all cursors may appear to do—fetch data. Most DBAs know that cursors are a more integral part of database functionality, but they may not be aware of how understanding and manipulating cursors can dramatically improve the performance of their Oracle database applications.

Although traditionally used for querying data, cursors have become pervasive in PL/SQL. This chapter discusses how cursors and PL/SQL interact from a DBA perspective. It describes how reusing cursors can improve performance and discusses the differences between explicit and implicit cursors, and how each may affect your database. It also explores the performance benefits of Oracle’s optimization of “soft-closed” cursors. The chapter also discusses REF (dynamic) cursors, cursor parameters, and cursor expressions (nested cursors).

There has been a great deal of spirited debate over the years ...