cursor in sql

cursor in sql-: Cursor are used to process a collection of row computed by an embedded sql statement in host language programme.It can be thought of as a pointer that point to a single row from the result of a query that retrieves multiple rows.

In other words we can say that A cursor is a temporary work area created in system memory when an SQL statement is executed. A 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 singlet method

cursor in sql
life cycle of cursor in sql

 

There are four steps involved using cursors:

  1. Declare a cursor -: By naming it defining the structure of the query to be performed within it.
  2. Open the cursor -: The open statement execute the query and binds any variable that are referenced Rows identified by the query called the active set and now available for fetching cursor.
  3. Fetch data from cursor-: As shown in the image after each fetch we test the cursor for any existing row if there are no more rows to process then we need to close the cursor.
  4. Close the cursor-: the close statement releases the active set of rows It is now possible to reopen the cursor to established a fresh active set.

 

Declare a cursor-: By naming it defining the structure of the query to be performed within it. as like

Declare <cursor name> cursor for <select statement>;

Opening the cursor-: open is an execute statement t inhat performs the following operations.

  1. Dynamically allocated memory for a context area that eventually contains crucial processing information.
  2. Parses the select statement.
  3. Binds the input variable as like the value for the input variable name by obtaining there memory address.
  4. Identifies thee active set as like the set of rows that satisfy the search criteria rows in active set are not retrieved into variable when the open statement is execute .
  5. Positions of pointer just before the first row in the active set.

Open <cursor name>;

Fetching data from the cursor-: The fetch statement retrieve the rows in the active set one at a time. After each the cursor advances the next row in the active set.

Fetch <cursor name> into <variable list>;

Closing the Cursor-: The close statement disable the cursor and the active set becomes undefined.this step allows the cursor to be reopened.

close <cursor name>;

 

 

 

Leave a Comment