A cursor in SQL is a temporary work area created in system memory when a SQL statement is executed. A SQL cursor is a set of rows together with a pointer that identifies a current row. It is a database object to retrieve data from a result set one row at a time. It is useful when we want to manipulate the record of a table in a singleton method, in other words, one row at a time. In other words, a cursor can hold more than one row but can process only one row at a time. The set of rows the cursor holds is called the active set.
Types of Cursors in SQL
There are the following two types of cursors in SQL:
- Implicit Cursor
- Explicit Cursor
Main components of Cursors
Each cursor contains the followings 5 parts,
- Declare Cursor: In this part, we declare variables and return a set of values.
- Open: This is the entering part of the cursor.
- Fetch: Used to retrieve the data row by row from a cursor.
- Close: This is an exit part of the cursor and used to close a cursor.
- Deallocate: In this part, we delete the cursor definition and release all the system resources associated with the cursor.
Cursor Scope
Microsoft SQL Server supports the GLOBAL and LOCAL keywords on the DECLARE CURSOR statement to define the scope of the cursor name.
- GLOBAL – specifies that the cursor name is global to the connection.
- LOCAL – specifies that the cursor name is local to the Stored Procedure, trigger, or query that holds the cursor.
Data Fetch Option in Cursors
Microsoft SQL Server supports the following two fetch options for data:
- FORWARD_ONLY – Specifies that the cursor can only be scrolled from the first to the last row.
- SCROLL – It provides 6 options to fetch the data (FIRST, LAST, PRIOR, NEXT, RELATIVE, and ABSOLUTE).
Life Cycle of the cursor
The following steps are involced in a SQL cursor life cycle.
- Declaring Cursor
A cursor is declared by defining the SQL statement.
- Opening Cursor
A cursor is opened for storing data retrieved from the result set.
- Fetching Cursor
When a cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
- Closing Cursor
The cursor should be closed explicitly after data manipulation.
- Deallocating Cursor
Cursors should be deallocated to delete cursor definition and release all the system resources associated with the cursor.
- Implicit Cursors
The implicit cursors are automatically generated by Oracle while an SQL statement is executed, if you don’t use an explicit cursor for the statement.
These are created by default to process the statements when DML statements like INSERT, UPDATE, DELETE etc. are executed.
Orcale provides some attributes known as Implicit cursor’s attributes to check the status of DML operations. Some of them are: %FOUND, %NOTFOUND,
%ROWCOUNT and %ISOPEN.
For example: When you execute the SQL statements like INSERT, UPDATE, DELETE then the cursor attributes tell whether any rows are affected and how many have been affected. If you run a SELECT INTO statement in PL/SQL block, the implicit cursor attribute can be used to find out whether any row has been returned by the SELECT statement. It will return an error if there no data is selected.
The following table specifies the status of the cursor with each of its attribute.
| Attribute | Description |
| %FOUND | Its return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect at least one row or more rows or a SELECT INTO statement returned one or more rows. Otherwise it returns FALSE. |
| %NOTFOUND | Its return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect no row, or a SELECT INTO statement return no rows. Otherwise it returns FALSE. It is a just opposite of %FOUND. |
| %ISOPEN | It always returns FALSE for implicit cursors, because the SQL cursor is automatically closed after executing its associated SQL statements. |
| %ROWCOUNT | It returns the number of rows affected by DML statements like INSERT, DELETE, and UPDATE or returned by a SELECT INTO statement. |
SQL Implicit Cursor Example
Create customers table and have records:
Let’s execute the following program to update the table and increase salary of each customer by 5000. Here, SQL%ROWCOUNT attribute is used to determine the number of rows affected:
Create procedure:
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 5000;
IF sql%notfound THEN
dbms_output.put_line(‘no customers updated’);
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ‘ customers updated ‘);
END IF;
END;
/
Output:
6 customers updated
PL/SQL procedure successfully completed.
Now, if you check the records in customer table, you will find that the rows are updated.
select * from customers;
| ID | NAME | AGE | ADDRESS | SALARY |
| 1 | Ramesh | 23 | Allahabad | 25000 |
| 2 | Suresh | 22 | Kanpur | 27000 |
| 3 | Mahesh | 24 | Ghaziabad | 29000 |
| 4 | Chandan | 25 | Noida | 31000 |
| 5 | Alex | 21 | Paris | 33000 |
| 6 | Sunita | 20 | Delhi | 35000 |
- Explicit Cursors
The Explicit cursors are defined by the programmers to gain more control over the context area. These cursors 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.
Following is the syntax to create an explicit cursor:
CURSOR cursor_name IS select_statement;;
Steps:
You must follow these steps while working with an explicit cursor.
- Declare the cursor to initialize in the memory.
- Open the cursor to allocate memory.
- Fetch the cursor to retrieve data.
- Close the cursor to release allocated memory.
- Declare the cursor:
It defines the cursor with a name and the associated SELECT statement.
Syntax for explicit cursor decleration
CURSOR name IS SELECT statement;
- Open the cursor:
It is used to allocate memory for the cursor and make it easy to fetch the rows returned by the SQL statements into it.
Syntax for cursor open:
OPEN cursor_name;
- Fetch the cursor:
It is used to access one row at a time. You can fetch rows from the above-opened cursor as follows:
Syntax for cursor fetch:
FETCH cursor_name INTO variable_list;
- Close the cursor:
It is used to release the allocated memory. The following syntax is used to close the above- opened cursors.
Syntax for cursor close:
Close cursor_name;
Explicit Cursor Example
Explicit cursors are defined by programmers to gain more control over the context area. It is defined in the declaration section of the PL/SQL block. It is created on a SELECT statement which returns more than one row.
Let’s take an example to demonstrate the use of explicit cursor. In this example, we are using the already created CUSTOMERS table.
Create customers table and have records:
| ID | NAME | AGE | ADDRESS | SALARY |
| 1 | Ramesh | 23 | Allahabad | 20000 |
| 2 | Suresh | 22 | Kanpur | 22000 |
| 3 | Mahesh | 24 | Ghaziabad | 24000 |
| 4 | Chandan | 25 | Noida | 26000 |
Create procedure:
Execute the following program to retrieve the customer name and address.
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;
/
Output:
- Ramesh Allahabad
- Suresh Kanpur
- Mahesh Ghaziabad
- Chandan Noida
PL/SQL procedure successfully completed