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.

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

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.

• 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.