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

Question

Krishnapriya Rajeev · Answer

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:and 
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.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:
DeclareOpenFetchClose
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 andlt; 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", and “lname” and "salary". and 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.

forum

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

Revati S Misra

Can you answer this question?

1 Answers

Liked By