Cursors – Explicit Cursors

What are Cursors? 

A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT statement or a DML statement (Insert, Update or Delete). In layman terms, a Cursor is used to fetch (SELECT) a particular dataset from a much larger dataset which can then be used for data manipulation using other DML Operations like Insert, Update or Delete.

There are two types of Cursors: Implicit Cursors and Explicit Cursors.

In this post, we will discuss mainly about Explicit Cursors.

Explicit Cursors: Unlike Implicit cursors, application developers have control over the life cycle of the explicit cursors by manually performing the OPEN and FETCH operations and then CLOSE the Cursor.

A Cursor is defined in the declarative part of the PL/SQL Block and the following are three stages are part of the Cursor life cycle.

  • OPEN
    •  Open: Database allocates a private work area in the user’s session memory for cursor processing.
    •  Parse SQL: Validates the SQL query for syntax and privileges.
    •  Bind SQL: Provides an input value to the bind variables that are part of the query.
    •  Executes the SQL Statement (that has the required conditions in the WHERE condition).
  • FETCH
    • Fetches the data from the parsed SQL statement. Fetches the data into a block variable and increments the record pointer.
  • CLOSE
    • Closes the cursor and releases the memory back to System Global Area (SGA).

Consider the following dataset to demonstrate an example of an Explicit cursor. Create a sample table and insert data into the table.

The following code will display the data from the table in the required format.

Cursor – FETCH

  • Code from Line Number 2 to 5 is where the code is being defined in the declare section.
  • Code in line number 13 opens the cursor.
  • Code in line number 14 fetches the data from the SQL cursor into a record variable that has a cursor type datatype.
  • Once all the data is fetched, code in line number 15 closes the Cursor.
  • Code from Line 17 to 29, checks if there is any data in the record variable, then loops through the record using an index, and displays the data in the required format, and then closes the loop.

FOR LOOP: 

 

Leave a Reply

  • (will not be published)