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
life cycle of cursor in sql
There are four steps involved using cursors:
- Declare a cursor -: By naming it defining the structure of the query to be performed within it.
- 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.
- 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.
- 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.
- Dynamically allocated memory for a context area that eventually contains crucial processing information.
- Parses the select statement.
- Binds the input variable as like the value for the input variable name by obtaining there memory address.
- 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 .
- 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>;