Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page