PL/SQL Cursors

PL/SQL cursors are internal data structures used to execute SQL statements. SELECT statements in PL/SQL programs are implicit cursors. They represent memory areas used by SQL processor to execute SQL commands. PL/SQL cursors are essential part of PL/SQL Blocks to manipulate data in tables.

When you run an SQL statement in Oracle, it is executed in memory area called Context Area. Context area maintains information about the query that includes number of rows affected by the SQL statement and a pointer to the area with the SELECT query output.  The rows fetched by the query depending upon query conditions are called the active set. A PL/SQL cursor is a pointer or handle to the context area that stores active set. It allows fetching and processing of data rows after a SELECT statement is executed implicitly or explicitly.

Cursor Types in PL/SQL

Implicit Cursor

Every SQL statement invoked by a user in PL/SQL block and executed by SQL processor is called an implicit cursor. It cannot be controlled programmatically. Its information cannot be processed unlike an explicit cursor.

Features of an Implicit Cursor are

• Any DML statement that executes  in a PL/SQL block
• It is automatically created and managed
• The latest DML statement executed in a PL/SQL block is identified as an implicit cursor with  SQL keyword
• Implicit cursors associated with UPDATE and DELETE DML statements return the count of rows affected with %ROWCOUNT attribute. It is added to SQL- implicit cursor for latest DML as SQL%ROWCOUNT
• An implicit cursor is associated by default with an  SELECT INTO statement fetching values into PL/SQL variables
• The OPEN, FETCH and CLOSE operations of a cursor are performed automatically
• Exception raised by implicit cursor can be difficult to  handle
• Implicit cursor are less efficient

Explicit Cursor

A cursor declared in the DECLARE section of a PL/SQL block and given a name is called an explicit cursor. It has a name so it can be handled in a program to access data. Using the PL/SQL looping statements a programmer can fetch individual rows and manipulate the data.

The features of Explicit Cursors are

• An explicit cursor must always be declared in DECLARE section of a block and associated with a SELECT statement that fills the cursor with required data.
• It can be programmatically controlled in a PL/SQL block.
• Exception raised by Explicit cursor can be easily handled
• An explicit cursor has to be opened, its records have to be fetched and it must be closed after use.
• Explicit Cursors are efficient.

PL/SQL Cursor Attributes

PL/SQL cursors can be handled with the help of four attributes. These attributes are common for implicit and explicit cursors. In case of implicit cursor SQL keyword must be added before the attribute. For explicit cursor use the name of the declared cursor before the cursor attributes.

admin