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.

Implicit Cursor in PL/SQL

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.

Explicit Cursor in PL/SQL

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.

Attribute Return Data Type Working
%ISOPEN BOOLEAN ISOPEN attribute return TRUE if the cursor is open else returns FALSE
%FOUND BOOLEAN FOUND attribute return TRUE if a record is fetched from the cursor. Else returns FALSE. If the cursor reaches end of active set, FOUND returns FALSE. If there are no rows in the cursor, it returns FALSE
%NOTFOUND BOOLEAN NOTFOUND attribute return TRUE if a record is not fetched from the cursor or is not available. IF a record is successfully fetched it returns FALSE. If the cursor reached end of active set, FOUND returns TRUE. If there are no rows in the cursor, it returns TRUE
%ROWCOUNT NUMBER ROWCOUNT attribute the count of rows affected by DML Statements.

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *