forum

Home / DeveloperSection / Forums / What is a PL/SQL cursor? Explain with an example.

Ask Question
Revati S Misra

Revati S Misra

Follow
Skilled in SEO, content writing, and digital marketing. Completed several years of working in many organizations including multinational companies. I love to learn new things in life that keep me motivated.

Can you answer this question?

Answer

1 Answers

Krishnapriya Rajeev

Krishnapriya Rajeev

04-Apr-2023

A PL/SQL cursor is a mechanism that allows us to retrieve data from a result set one row at a time. It enables us to manipulate data retrieved from a SELECT statement, and it is particularly useful when we need to perform operations on a set of records one at a time.

When processing an SQL statement, a memory area called the context area is created, which contains the information required for processing the statement, such as the number of rows processed. The context area is accessed through a cursor, which is a pointer to the context area that is controlled by PL/SQL. The cursor contains the rows, also referred to as the active set, returned by the SQL statement.

Cursors are of 2 types: 

  1. Implicit cursors - When an SQL statement is executed without an explicit cursor, Oracle automatically creates an implicit cursor to process the statement. Implicit cursors are associated with DML statements (INSERT, UPDATE, and DELETE) and are not controlled by the programmer. They contain information related to the SQL statement, such as the number of rows affected.
  2. Explicit cursors - Explicit cursors are cursors that are defined by the programmer to have more control over the context area. They must be declared in the PL/SQL block's declaration section and are created for a SELECT statement that returns more than one row.

The syntax to create an explicit cursor is as follows:

CURSOR cursor_name IS select_statement;

The steps required to use an explicit cursor are as follows:

  • Declare
  • Open
  • Fetch
  • Close

Here is an example of how to use a PL/SQL cursor:

DECLARE
CURSOR staff_cur IS SELECT staffno, fname, lname, salary FROM staff;
staffno staff.staffno%TYPE;
fname staff.fname%TYPE;
lname staff.lname%TYPE;
salary staff.salary%TYPE;
cnt INT := 0;
BEGIN
 OPEN staff_cur;
 LOOP
 FETCH staff_cur INTO staffno, fname, lname, salary;
 EXIT WHEN staff_cur%NOTFOUND;
 IF salary < 18000 THEN
 cnt := cnt + 1;
 END IF;
 END LOOP;
 CLOSE staff_cur;
 DBMS_OUTPUT.PUT_LINE('Number of staff with salary less than 18000: 
' || cnt);
END;
/
#OUTPUT:
 Number of staff with salary less than 18000: 5

In this example, we declare a cursor called "staff_cur" that retrieves the staffno, first name, last name, and salary from the staff table. We then open the cursor and use a loop to retrieve each row of data one at a time using the FETCH statement. We store the retrieved data in variables "staffno", "fname",  “lname” and "salary".  We increment the count by 1 if the salary is less than 18000. The loop continues until there are no more rows to fetch, which is determined by the NOTFOUND attribute of the cursor. Finally, we close the cursor using the CLOSE statement. The count is then displayed.

Using a cursor in this way allows us to retrieve and manipulate data one row at a time, making it a powerful tool for data processing and analysis.

Liked By