Cursors
To most people, a cursor is commonly known as a blinking dot or square that appears on the monitor and indicates where you are in a file or application. That is not the same type of cursor discussed here. An SQL cursor is an area in database memory where the last SQL statement is stored. If the current SQL statement is a database query, a row from the query is also stored in memory. This row is the cursor's current value or current row. The area in memory is named and is available to programs.
A cursor is typically used to retrieve a subset of data from the database. Thereby, each row in the cursor can be evaluated by a program, one row at a time. Cursors are normally used in SQL that is embedded in procedural-type programs. Some cursors are created implicitly by the database server, whereas others are defined by the SQL programmer. Each SQL implementation may define the use of cursors differently.
This section shows syntax examples from two popular implementations: Microsoft SQL Server and Oracle.
The syntax to declare a cursor for Microsoft SQL Server is as follows:
DECLARE CURSOR_NAME CURSOR FOR SELECT_STATEMENT [ FOR [READ ONLY | UPDATE [ COLUMN_LIST ]}]
The syntax for Oracle is as follows:
DECLARE CURSOR CURSOR_NAME IS {SELECT_STATEMENT}
The following cursor contains the result subset of all records from the EMPLOYEE_TBL table:
DECLARE CURSOR EMP_CURSOR IS
SELECT * FROM EMPLOYEE_TBL
{ OTHER PROGRAM STATEMENTS }
According to the ANSI standard, the following operations are used to access a cursor once it has been defined:
| OPEN | Opens a defined cursor |
| FETCH | Fetches rows from a cursor into a program variable |
| CLOSE | Closes the cursor when operations against the cursor are complete |
Opening a Cursor
When a cursor is opened, the specified cursor's SELECT statement is executed and the results of the query are stored in a staging area in memory.
The syntax to open a cursor in dBase is as follows:
OPEN CURSOR_NAME
The syntax in Oracle is as follows:
OPEN CURSOR_NAME [ PARAMETER1 [, PARAMETER2 ]]
To open the EMP_CURSOR:
OPEN EMP_CURSOR
Fetching Data from a Cursor
The contents of the cursor (results from the query) can be retrieved through the use of the FETCH statement once a cursor has been opened.
The syntax for the FETCH statement in Microsoft SQL Server is as follows:
FETCH CURSOR_NAME [ INTO FETCH_LIST ]
The syntax for Oracle is as follows:
FETCH CURSOR_NAME {INTO : HOST_VARIABLE [[ INDICATOR ] : INDICATOR_VARIABLE ] [, : HOST_VARIABLE [[ INDICATOR ] : INDICATOR_VARIABLE ]] | USING DESCRIPTOR DESCRIPTOR ]
The syntax for dBase is as follows:
FETCH CURSOR_NAME into MEMORY_VARIABLES
To fetch the contents of EMP_CURSOR into a variable called EMP_RECORD, your FETCH statement may appear as follows:
FETCH EMP_CURSOR INTO EMP_RECORD
Closing a Cursor
You can obviously close a cursor if you can open a cursor. Closing a cursor is quite simple. After it's closed, it is no longer available to user programs.
Note
Closing a cursor does not necessarily free the memory associated with the cursor. In some implementations, the memory used by a cursor must be deallocated by using the deallocate statement. When the cursor is deallocated, the memory associated is freed and the name of the cursor can then be reused. In other implementations, memory is implicitly deallocated when the cursor is closed. Memory is available for other operations, such as opening another cursor, when space used by a cursor is reclaimed.
The Microsoft SQL Server syntax for the closing of a cursor and the deallocation of a cursor is as follows:
CLOSE CURSOR_NAME DEALLOCATE CURSOR CURSOR_NAME
When the cursor is closed in Oracle, the resources and name are released without the DEALLOCATE statement. The syntax for Oracle is as follows:
CLOSE CURSOR_NAME
To release the resources in dBase, the table must be closed and reopened before the resources are released and the name can be reused. The syntax for dBase is as follows:
CLOSE CURSOR_NAMENote
As you can see from the previous examples, variations among the implementations are extensive, especially with advanced features of and extensions to SQL, which are covered during Hour 24, "Extensions to Standard SQL." You must check your particular implementation for the exact usage of a cursor.