Cursor in SQL: Uses, Lifecycle, Types, & More

According to the United States Bureau of Labor Statistics, there is a nine percent projected job growth in roles such as SQL developer and other database administrators between 2021 and 2023. Becoming an SQL developer is a potential career option. The movable arrow on your laptop’s screen or the fingertip that navigates through the phone’s screen? Both of these identify as a point where a user provides input. 

Likewise, an SQL cursor helps you navigate rows in a table when an SQL query executes. Various databases have a memory where the processing of fetched data takes place. 

A cursor in SQL is a pointer that points to this memory area. This memory area is called the context area. We can say that the responsibility for storing the rows after a SQL query has returned falls on the cursor. Let us explore more about it!

What is a Cursor in SQL?

A work area is used in the SQL engine for internal processing to execute the SQL queries. This work area is unique to SQL’s operations and is called a cursor. One of the main applications of the cursor is row-by-row table navigation.

Cursors are used to store the database tables and any response to the SQL query, depending on the type of cursor used. The stored data in the cursor is called an “active data set.”SQL can create and deallocate a cursor. When a SQL query is about to execute, the operating system’s memory will reserve a private SQL area, a cursor. It is either automatically created or user-defined. This area gets populated with the data requested in the SQL query. Data is processed in this memory area as required, and then the memory area is freed when the data processing is complete. To become an excellent SQL developer, you can take an online SQL course.

Use of Cursors in SQL

It is important to understand the use of cursors in SQL to become a proficient SQL developer.

• The use of cursors in SQL is understood with the help of the context area and the ‘result set’, which is a set of data that is usually returned from the database as a result of a query. 
• The cursor keeps track of the result set.
•  It performs multiple operations sequentially against a result set without necessarily referring to the original table.
•  A cursor can update records in a singleton fashion, i.e., row-by-row. 

For example, to update information in an ‘apparel_table’ table containing apparel descriptions, use an ‘UPDATE’ query with a ‘WHERE’ clause to update the apparel_price with a specific ‘apparel_ID’.

UPDATE apparel_table
SET apparel_price = 1500
WHERE apparel_ID = ‘SN15’;

Here, a cursor is created in the memory area during the execution of every SQL statement. It will process the rows on a one-by-one basis to update the apparel price with the said apparel_ID.

A Cursor’s Lifecycle

The lifecycle of a cursor can be understood in five steps, as explained below. 

Note: The ‘cursor_name’ represents the name of the cursor, and the ‘select_statement’ indicates a select query that will return multiple rows. 

• Step 1 – Declare

A cursor is declared using the data type CURSOR. The SELECT statement will return the result set.

Syntax –

DECLARE cursor_name CURSOR
FOR select_Statement ;

• Step 2 – Open

A cursor is opened to store data.

Syntax –

OPEN cursor_name ;

• Step 3 – Fetch

Rows are fetched from the table one at a time to perform data manipulation.

Syntax –

FETCH NEXT FROM cursor INTO variable_list ;

• Step 4 – Close

After fetching data, the cursor is closed directly.

Syntax-

CLOSE cursor_name ;

• Step 5 – Deallocate

This step will delete the cursor and free up memory space.

Syntax –

DEALLOCATE cursor_name ;

Types of Cursors in SQL with Syntax and Examples

Cursors are classified depending on the circumstances under which they are opened. If a cursor is opened for its internal processing, it is known as an “implicit cursor”. If the cursor is opened for processing data on demand, meaning that it’s user-defined, then it’s called an “explicit cursor”.

1. Implicit Cursor

SQL will automatically establish implicit cursors every time a query is run. There is an implicit cursor attached to every DML command (INSERT, UPDATE, and DELETE) that is issued. The cursor contains the data that has to be inserted for INSERT operations. Implicit cursors and the data they contain are not within the control of programmers. Programmers don’t need to declare or open an implicit cursor. Memory allocation and deallocation automatically take place in the implicit cursor. Using a cursor enables the identification of the rows impacted by DELETE and UPDATE operations.     It always has attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.

For implicit cursor use, ‘SQL’  is suffixed before these attributes. For instance, SQL%ROWCOUNT. SQL cursor attributes are accessed using sql%attribute_name.

For example: 

A table TEACHERS that contains information in columns ‘name’, ‘age,” “address,” and ‘salary’. If you want to increase the salary of teachers by 4000, use the SQL%ROWCOUNT attribute to determine the number of rows affected –

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE teachers
   SET salary = salary + 4000; 
   IF sql%notfound THEN 
      dbms_output.put_line('no teachers selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' teachers selected '); 
   END IF;  
END; 
/     

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

6 teachers selected  

The SQL procedure was completed. 

2. Explicit Cursor

Programmers can construct explicit cursors to have better control over the context area. The declaration part of the SQL block needs to contain an explicit definition of the cursor. It is built on a SELECT statement that produces several rows.

To create an explicit cursor, use the syntax –

CURSOR cursor_name IS select_statement; 

The following actions are involved when using an explicit cursor –

• Declaring the Cursor – It defines the cursor with a name and the associated SELECT statement.

For example,

CURSOR t_teachers IS 
SELECT id, name, address FROM teachers;

• Opening the Cursor: It allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it.

 For example,

OPEN t_teachers;

• Fetching the Cursor – It involves accessing one row at a time.

For example, to fetch rows from the above-opened cursor as follows –

FETCH t_teachers INTO t_id, t_name, t_addr;

• Closing the Cursor- It means releasing the allocated memory.

For example, to close the above-opened cursor as follows –

CLOSE t_teachers;

The following is a complete example of actions involved in an explicit cursor:

DECLARE

     t_id teachers.id%type;

     t_name teachersS.No.ame%type;

     t_addr teachers.address%type;

     CURSOR t_teachers IS

          SELECT id, name, address FROM teachers;

BEGIN

       OPEN t_teachers;

       LOOP

       FETCH t_teachers into t_id, t_name, t_addr;

           EXIT WHEN t_teachers%notfound;

           dbms_ouput.put_line (t_id || ‘ ‘ || t_name || ‘ ‘ || t_addr) ;

       END LOOP;

       CLOSE t_teachers;

END;

/

Difference Between the Cursor and Trigger in SQL

Cursor and trigger in SQL are database objects.

S.No.	Cursor	Trigger
1.	A cursor is created to retrieve and manipulate data, row by row.	A trigger is executed automatically in response to data modification operations, such as an INSERT, UPDATE, or DELETE operation.
2.	It is created in response to the execution of an SQL statement.	It is executed in response to a DDL (Data Definition Language) statement, a DML (Data Manipulation Language) statement, or any database modifications.
3.	Because it is automatically created when a SQL query is executed. Therefore, it is not pre-stored.	It is a previously stored program.
4.	A cursor can be used and declared within a trigger by including the declaration statement.	A trigger cannot be created within a cursor.
5.	It is primarily used to sequentially traverse through records in a database.	It is primarily used to maintain the integrity of the database.

Conclusion

With the help of a cursor in SQL, we can perform queries on complex relational databases. Since cursors help to process tables row-by-row and perform multiple calculations, modifications to the data can be done easily. When explicit cursors are in use, control of the data is achieved. Accordingly, multiple operations on the same data can be done without performing multiple SQL queries.