Understanding 'Cursor' in SQL

What is Cursor?

In SQL cursor is a temporary work area created in system memory when a SQL statement is executed. In other words the cursor is a database object which is very useful when we want to manipulate the record of a table

Note: Cursor can hold more than one row but can process only one row at a time

How to use a Cursor?

There are four steps to use a Cursor

• Declare : This part includes declaring the cursor with a cursor name.
• Open : This is the entering part of the cursor.
• Fetch : This part includes row by row retrieval of data.
• Close : This part includes exiting from cursor, used to close cursor.
• Deallocate : This is the deallocation part of cursor in which all the system resources associated with cursor will be released.

Syntax

1. DECLARE cursor_name CURSOR
2. OPEN cursor_name
3. FETCH NEXT FROM cusror_name

/implement sql queries/

1. CLOSE cursor_name
2. DEALLOCATE cursor_name

Parametrized Cursor

SQL allows to pass parameters to a cursor. Sometime when we want provide condition with WHERE clause we use parametrized cursor.

Syntax of Parametrize Cursor

DECLARE cursor_name(parameter_name parameter_datetype)

to open a cursor with parameters we use following syntax

OPEN cursor_name(value_list)

In this syntax you have to pass the arguments corresponding to the parameters of the cursor