Using Explicit Cursors  «Prev  Next»

Lesson 2What is a Cursor?
ObjectiveDefine the Basic Components of a Cursor

What is a Cursor in PL/SQL?

A cursor is used to process multiple records one record at a time. You may use cursors to:
  1. Migrate data from a legacy system
  2. Load data into a data warehouse
  3. Process data from multiple tables in a database

There are several advantages to using cursors within your applications. Where and when you use a cursor depends on the nature of the application and the objective you want to accomplish.

PL/SQL Cursors

specific SELECT or DML statement. The cursors that this chapter explains are session cursors. A session cursor lives in session memory until the session ends, when it ceases to exist. A session cursor that is constructed and managed by PL/SQL is an implicit cursor. A session cursor that you construct and manage is an explicit cursor. You can get information about any session cursor from its attributes (which you can reference in procedural statements, but not in SQL statements). To list the session cursors that each user session currently has opened and parsed, query the dynamic performance view V$OPEN_CURSOR.

Advantages of using Cursors

The advantages of using cursors are as follows:
  1. Cursor processing is server based and it utilizes the server memory. Therefore, processing time is shorter.
  2. By using cursors, you can process records from different tables one at a time and have greater flexibility in manipulating the data.
Using too many cursors may consume a lot of resources, and thus slow down the server.
Before executing a SQL statement, Oracle assigns an area of memory on the database server known as the context area for the statement. The context area contains information that includes the SQL statement and data that is returned or affected by the SQL. This information allows Oracle to complete processing the SQL statement.
The context area is within Oracle’s program global area (PGA). The PGA is a memory buffer that contains data, process information, session information, and cursors for a server process. A cursor is a server-side mechanism by which you can name that context area and manipulate information within it. The SQL in a cursor can be static or dynamic.

Click the link below to read about typical cursor applications.
Typical Cursor Applications
In the next lesson, we will look at implicit cursors, one of the two types of cursors.