What is Cursor in SQL? Command to control Cursor with Example

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.

Types of cursor

Based on their scope and usage

1. Implicit cursor
2. Explicit cursor

Based on their functionality and behavior

1. Static
2. Dynamic
3. Forward-Only
4. Keyset-Driven

Detailed Explanation

1. Explicit Cursors

Explicit Cursors are declared by programmers in their SQL code, giving them direct control over the cursor and its operations. This includes managing the cursor lifecycle with OPEN, FETCH, and CLOSE commands. They are useful for more complex operations where you need to process or manipulate data row by row.

Example: In PL/SQL,

DECLARE
    CURSOR c_students IS SELECT name FROM students;
BEGIN
    FOR student_record IN c_students LOOP
        -- Process each student
    END LOOP;
END;

2. Implicit Cursors

Implicit Cursors are automatically created by SQL when executing DML statements (INSERT, UPDATE, DELETE, SELECT INTO) that do not return a result set to the application. They are managed by the database system itself, requiring no explicit declaration or management by the programmer.

Example: In a PL/SQL block, when you perform a SELECT INTO statement, an implicit cursor is used to fetch the data.

3. Static Cursors

Static Cursors create a temporary copy of the data to work with. Once a static cursor is opened, the result set does not reflect changes made in the database. They are useful when you need a consistent snapshot of the database.

4. Dynamic Cursors

Dynamic Cursors reflect all changes made in the database while the cursor is open. They allow viewing updates, insertions, and deletions made by other transactions. However, this type of cursor can be more resource-intensive.

5. Forward-Only Cursors

Forward-Only Cursors move in a single direction, from the first row towards the last row, and do not support scrolling backward. They are the default type of cursor in many SQL databases due to their efficiency.

6. Keyset-Driven Cursors

Keyset-Driven Cursors are a type of dynamic cursor where the database server creates a key set (the set of keys that uniquely identify rows) for the rows that are in the result set when the cursor is opened. Updates to non-key columns in rows are visible, but inserts and deletes are not.

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;
/