Explanation of Cursor, Command to control Cursor with an example

April 5, 2022 by quescol

A cursor is a working area where an SQL statement is executed. It is used to process individual rows returned by the database system for a query.

There are two types of cursor

Implicit cursor: It is a predefined cursor that is associated with DML hence the declaration is not required in this cursor. Some of the attributes used as implicit cursors are %FOUND, %NOT FOUND, %OPEN, %ROWCOUNT.
Explicit cursor: It is a user-defined cursor hence the declaration is required for this cursor. It is associated with the SELECT statement of SQL which returns more than one row. The associated commands used are DECLARE, OPEN, CLOSE, FETCH, etc.

Various cursor attributes

Cursor attributes	Description
%FOUND	Returns true if the DML statement affects at least one row else returns false.
%NOT FOUND	Returns true if no row is affected by the DML statement else returns false
%OPEN	Returns false implicit function or explicit function is close and returns true if the explicit function is open.
%ROWCOUNT	Returns the number of affected rows
DECLARE	Used to declare the curser in declare section
OPEN	Used to open the cursor in the execution section
CLOSE	Used to close the cursor in the execution section before the end of PL/SQL.
FETCH	Used to fetch the data from the cursor

Now let’s have a look at the example

DECLARE
	C_id customer.id
	C_name customer.name
	Cursor C1 IS SELECT id, name from customer;
BEGIN
OPEN C1
LOOP
	FETH C1 INTO C_id, C_name;
	EXIT WHEN C1%NOTFOUND;
	Dbms.output.put_line(“id:=”||C_id||”name:=”||C_name);
END LOOP;
CLOSE C1;
END;
/