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 dofetch 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 about the proper use of cursors in the Oracle database. For example, DBAs and developers argue that one type of cursor is faster than another, or they question the effectiveness of Oracle's reuse model. Over the years, there have also been major advances in the cursor features provided in recent Oracle releases. This chapter does not try to persuade you to take one particular path in implementing and tuning cursors in your system. The proper use of cursors depends, to a large extent, on the characteristics of your organization and its data and applications. Here we simply try to explain the major features and provide some guidance to help you decide when to use them.