SQL Cursor

Introduction to Cursor

  • Cursor is a temporary work area created in the system memory when a SQL statement is executed.
  • It contains information on a SELECT statement and the rows of data accessed by it.
  • It can hold more than one row, but can process only one row at a time.
  • Cursor is used to access the resultset present in the memory.
  • This resultset contains the records returned on execution of a query.
There are two types of Cursor,
1. Explicit
2. Implicit

1. Explicit Cursor

  • Explicit cursor is created when you are executing a SELECT statement that returns more than one row.
  • It stores multiple records. Only one record can be processed at a time, which is called as Current Row.
  • When you fetch a row the current row position moves to the next row.

2. Implicit Cursor

  • PL/SQL creates an implicit cursor whenever an SQL statement is executed through the code, unless the code employs an explicit cursor.
  • The developer does not explicitly declare the cursor, thus, known as Implicit Cursor.
  • If the oracle engine has opened a cursor for its internal processing, then it is implicit cursor.
  • These cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement.

Attributes of Explicit cursor

AttributesDescription
%FOUNDTrue, if the SQL statement has changed any rows.
%NOTFOUNDTrue, if record was not fetched successfully.
%ROWCOUNTThe number of rows affected by the SQL statement.
%ISOPENTrue, if there is a SQL statement being associated to the cursor or the cursor is open.

Drawbacks of Cursor

  • Cursor allows row by row processing of recordset. For every row, a network roundtrip is made unlike in a SELECT query, where there is just one network roundtrip.
  • Cursor needs more I/O and temporary storage resources, thus it is slower.
  • Implicit cursors are less efficient than explicit cursors.
  • Implicit cursors are more vulnerable to data errors.
  • Poorly written cursors can completely decrease available memory.

Cursor Variable

  • A cursor variable is a pointer that distinguishes the current row in a resultset from a multi-row query.
  • These variables allow cursors to be opened independently of being processed.
  • It can be passed as parameters between the application layers and the server side components.
  • These variables can be used to reduce the client-server network traffic.
  • In case of a cursor, Oracle opens an anonymous work area that stores processing information.
  • This area can be accessed by cursor variable which points to this area.
  • One must define a REF CURSOR type, and then declare cursor variables of that type to do so.

Example

/* Create the cursor type. */
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;

/* Declare a cursor variable of that type. */
company_curvar company_curtype;


Why are cursor variables easier to use than cursors?

Cursor variables are preferred over a cursor for following reasons
  • A cursor variable is not tied to a specific query.
  • One can open a cursor variable for any query returning the right set of columns. Thus, more flexible than cursors.
  • A cursor variable can be passed as a parameter.
  • A cursor variable can refer to different work areas.
▲