Table of contents
1.
Introduction
2.
Types of Cursors
3.
How to use explicit cursors?
3.1.
EXAMPLE
4.
FAQs
5.
Key Takeaways
Last Updated: Mar 27, 2024

PL/SQL Cursors

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Oracle creates a work area for internal processing and information of an executing SQL statement. That work area is known as the Context Area. 

A cursor is a pointer to the Context Area that contains all information related to the processing of SQL statements. Cursors are used when users need to manipulate data in a row-wise manner. The data held by the cursor is called Active Data Set.

Recommended topics, Coalesce in SQL and Tcl Commands in SQL

Types of Cursors

1. Implicit Cursors: When we execute a SQL statement and an explicit cursor is not used, the Oracle engine automatically opens a cursor for internal processing. Those cursors are called implicit cursors. These cursors are created when DML statements like DELETE, INSERT, UPDATE, and SELECT are executed.

Oracle has provided some attributes to check the status of DML statements. Whenever we execute a SQL statement, the cursor attributes tell about it affects any rows or not and how many rows are affected. The cursor attributes are listed below:

  • %FOUND: It returns TRUE if an INSERT, UPDATE or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.

    Example: SQL%FOUND

  • %NOT FOUND: Its return value is TRUE if DML statements like INSERT, DELETE, and UPDATE affect no row, or a SELECT INTO statement return no rows. Otherwise, it returns FALSE. It is just the opposite of %FOUND.

    Example: SQL%NOTFOUND

  • %ROW COUNT: It returns the number of rows affected by DML statements like INSERT, DELETE, and UPDATE or returned by a SELECT INTO statement.

     Example: SQL%ROWCOUNT

  • %IS OPEN: It always returns FALSE for implicit cursors because the SQL cursor is automatically closed after executing its associated SQL statements.

     Example: SQL%IS OPEN

2.Explicit Cursors: The cursor that has to be created, maintained, and closed by a program through PL/SQL code to execute any SELECT query that returns more than one row is called Explicit Cursor.

It is a user-defined cursor declared in the Declare section of the PL/SQL block and is used in its Executable area.

How to use explicit cursors?

  • DECLARE the Cursor

Declaration of the cursor is made in Declare section of the PL/SQL code by writing a SQL statement that retrieves data for processing.

Syntax

CURSOR cursor_name IS SELECT query;

Example

CURSOR c_users IS 

SELECT id, name, email FROM users;

 

  • OPEN the Cursor

Opening the cursor is done in the Begin section of the PL/SQL code. When we open a cursor, we allocate memory to the cursor for fetching records. 

Syntax

OPEN cursor_name;

 

Example

OPEN c_users;

 

  • FETCH the Cursor

We fetch the data from the cursor one row at a time into memory variables using the FETCH command.

Syntax

 fetch cursor_name into list_of_variables;

 

Example

For example, we could have a cursor defined as:

CURSOR c1

IS

      SELECT course_number

      FROM courses_tbl

      WHERE course_name = name_in;

We can fetch the data using the following command. It will first course_number into the variable called cnumber.

FETCH c1 into cnumber;
  • CLOSE the cursor:

We have to close the cursor to release the allocated memory. The following syntax is used to close the above-opened cursors.

Syntax

CLOSE cursor_name;

 

Example

CLOSE c_users;

 

EXAMPLE

roll_no         name         age       branch
11         Anu       20       CS
12       Asha       21     MECHANICAL
13       Arpit     18     CIVIL
14       Chetan     20       CS
15       Nihal     19       CIVIL

 

The above table student will be used in the following program, where we will use the SELECT query to fetch the names of all the students, store them in a cursor, and then loop around the cursor to print the names.

DECLARE

CURSOR student_cursor IS SELECT name FROM Student ;

snm Student.name %type;

BEGIN

OPEN student_cursor;

IF student_cursor%ISOPEN FALSE then

dbms_output.put_line('Cannot open cursor');

ELSE

LOOP

FETCH student_cursor INTO snm;

IF student_cursor%NOTFOUND then

Exit;

END IF;

dbms_ output.put_line('' ||snm);

END LOOP;

dbms_output.put_line('Total Records: ' ||student_cursor%rowcount);

CLOSE student_cursor;

END;

Output

FAQs

  1. Why is the cursor important?
    Database experts use cursors to process individual rows returned by database system queries. Cursors enable the manipulation of whole result sets at once. In this situation, a cursor allows for the sequential processing of rows in a result set.
     
  2. What occurs when a cursor is opened?
    Whenever we open a cursor, the values of the bind variables are examined. After that, the active set (the query result) is determined based on the importance of the bind variables. The active set pointer is set to the first row.
     
  3. What are the drawbacks of implicit cursors?
    The drawbacks of implicit cursors are given below:
    It is less efficient as compared to the explicit cursor. 
    It is more vulnerable to data errors.
    It gives you fewer programmatic controls.

Key Takeaways

The cursor is a pointer to a context area that contains information about executing SQL statements. There are two different types of cursors. Oracle engine automatically generates implicit cursors, whereas users create explicit cursors to manipulate SELECT statements. We learned about the syntax of cursors and their attributes.

Visit here to learn more about various topics related to database and management systems. Check out the Top 100 SQL Problems to master frequently asked questions in large companies and land into your dream company. Also, try  Coding Ninjas Studio to practice a wide range of DSA questions asked in many interviews.

Live masterclass