What is Cursor in SQL ?

Cursor is a Temporary Memory or Temporary Work Station. It is

Allocated by Database Server at the Time of Performing DML(Data
Manipulation Language) operations on Table by User. Cursors are used to
store Database Tables. There are 2 types of Cursors: Implicit Cursors, and
Explicit Cursors. These are explained as following below.

1. Implicit Cursors:
Implicit Cursors are also known as Default Cursors of SQL SERVER. These
Cursors are allocated by SQL SERVER when the user performs DML
operations.

2. Explicit Cursors :
Explicit Cursors are Created by Users whenever the user requires them.
Explicit Cursors are used for Fetching data from Table in Row-By-Row
Manner.

How to create Explicit Cursor:

1. Declare Cursor Object.

Syntax : DECLARE cursor_name CURSOR FOR SELECT * FROM table_name

DECLARE s1 CURSOR FOR SELECT * FROM studDetails

2. Open Cursor Connection.
Syntax : OPEN cursor_connection
OPEN s1

1. Fetch Data from cursor.

There are total 6 methods to access data from cursor. They are as follows :
FIRST is used to fetch only the first row from cursor table.
LAST is used to fetch only last row from cursor table.
NEXT is used to fetch data in forward direction from cursor table.
PRIOR is used to fetch data in backward direction from cursor table.
ABSOLUTE n is used to fetch the exact nth row from cursor table.
RELATIVE n is used to fetch the data in incremental way as well as
decremental way.
Syntax : FETCH NEXT/FIRST/LAST/PRIOR/ABSOLUTE n/RELATIVE n FROM
cursor_name

2. FETCH FIRST FROM s1

3. FETCH LAST FROM s1

4. FETCH NEXT FROM s1

5. FETCH PRIOR FROM s1

6. FETCH ABSOLUTE 7 FROM s1

7. FETCH RELATIVE -2 FROM s1

8. Close cursor connection.

Syntax : CLOSE cursor_name
CLOSE s1
9. Deallocate cursor memory.
Syntax : DEALLOCATE cursor_name
DEALLOCATE s1
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is
executed, when there is no explicit cursor for the statement. Programmers cannot
control the implicit cursors and the information in it.

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit

cursor is associated with this statement. For INSERT operations, the cursor holds
the data that needs to be inserted. For UPDATE and DELETE operations, the cursor
identifies the rows that would be affected.

In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor,
which always has attributes such as %FOUND, %ISOPEN, %NOTFOUND,
and %ROWCOUNT. The SQL cursor has additional
attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with
the FORALL statement. The following table provides the description of the most
used attributes −

S.No Attribute & Description

%FOUND

1
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.

%NOTFOUND

2
The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or
DELETE statement affected no rows, or a SELECT INTO statement returned no
rows. Otherwise, it returns FALSE.
 %ISOPEN
3 Always returns FALSE for implicit cursors, because Oracle closes the SQL
cursor automatically after executing its associated SQL statement.

%ROWCOUNT
4 Returns the number of rows affected by an INSERT, UPDATE, or DELETE
statement, or returned by a SELECT INTO statement.

Any SQL cursor attribute will be accessed as sql%attribute_name as shown below

in the example.

Example
We will be using the CUSTOMERS table we had created and used in the previous
chapters.

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
The following program will update the table and increase the salary of each
customer by 500 and use the SQL%ROWCOUNT attribute to determine the number
of rows affected −

DECLARE

total_rows number(2);

BEGIN

UPDATE customers

SET salary = salary + 500;

IF sql%notfound THEN

dbms_output.put_line('no customers selected');

ELSIF sql%found THEN

total_rows := sql%rowcount;

dbms_output.put_line( total_rows || ' customers selected ');

END IF;

END;

When the above code is executed at the SQL prompt, it produces the following
result −

6 customers selected

PL/SQL procedure successfully completed.

If you check the records in customers table, you will find that the rows have been
updated −

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2500.00 |
| 2 | Khilan | 25 | Delhi | 2000.00 |
| 3 | kaushik | 23 | Kota | 2500.00 |
| 4 | Chaitali | 25 | Mumbai | 7000.00 |
| 5 | Hardik | 27 | Bhopal | 9000.00 |
| 6 | Komal | 22 | MP | 5000.00 |
+----+----------+-----+-----------+----------+

Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control over
the context area. An explicit cursor should be defined in the declaration section of
the PL/SQL Block. It is created on a SELECT Statement which returns more than
one row.

The syntax for creating an explicit cursor is −

CURSOR cursor_name IS select_statement;

Working with an explicit cursor includes the following steps −

 Declaring the cursor for initializing the memory

 Opening the cursor for allocating the memory
 Fetching the cursor for retrieving the data
 Closing the cursor to release the allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT
statement. For example −

CURSOR c_customers IS

SELECT id, name, address FROM customers;

Opening the Cursor
Opening the cursor allocates the memory for the cursor and makes it ready for
fetching the rows returned by the SQL statement into it. For example, we will open
the above defined cursor as follows −

OPEN c_customers;

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example, we will
fetch rows from the above-opened cursor as follows −

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will
close the above-opened cursor as follows −

CLOSE c_customers;

Example
Following is a complete example to illustrate the concepts of explicit cursors
&minua;

DECLARE

c_id customers.id%type;

c_name customers.name%type;

c_addr customers.address%type;

CURSOR c_customers is

SELECT id, name, address FROM customers;

BEGIN

OPEN c_customers;
 LOOP

FETCH c_customers into c_id, c_name, c_addr;

EXIT WHEN c_customers%notfound;

dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

END LOOP;

CLOSE c_customers;

END;

When the above code is executed at the SQL prompt, it produces the following
result −

1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.

Cursor Actions – The Lifecycle of a Cursor
The lifecycle of a cursor typically involves five stages:

1. Declare: The first step is to declare a cursor. This step instructs the

system to generate a cursor with the given data set. The data set is

constructed using a SQL statement. At this stage, the active set is created,

but the cursor’s temporary workspace is not yet opened in the memory.

2. Open: Next, the system is instructed to open the cursor. At this stage,

the temporary workspace is loaded in the memory with the active set, and a

pointer is generated, which points to the first row in the active set.

3. Fetch: This is the recurring step in the whole process. The pointer’s

current row is fetched, and the desired task is performed on the row data.

The pointer moves to the next row in the cursor.

4. Close: After the data manipulation is done, the cursor needs to be

closed.

5. Deallocate: This is the final step to delete the cursor and release the

memory, processor and other system resources allocated to the cursor.