DEV Community

loading...
Cover image for Understanding 'Cursor' in SQL

Understanding 'Cursor' in SQL

fahadhassan1213 profile image Fahad Hassan ・1 min read

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

Alt Text

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

Discussion (0)

Forem Open with the Forem app