Cursors

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 22.  Advanced SQL Topics


Cursors

graphics/newterm_icon.gif

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 :

 graphics/syntax_icon.gif DECLARE  CURSOR_NAME  CURSOR FOR  SELECT_STATEMENT  [ FOR [READ ONLY  UPDATE [  COLUMN_LIST  ]}] 

The syntax for Oracle is as follows:

 graphics/syntax_icon.gif DECLARE CURSOR  CURSOR_NAME  IS {  SELECT_STATEMENT  } 

The following cursor contains the result subset of all records from the EMPLOYEE_TBL table:

 graphics/mysql_icon.gif 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:

 graphics/syntax_icon.gif OPEN  CURSOR_NAME  

The syntax in Oracle is as follows:

 graphics/syntax_icon.gif OPEN  CURSOR_NAME  [ PARAMETER1 [, PARAMETER2 ]] 

To open the EMP_CURSOR:

 graphics/mysql_icon.gif 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:

 graphics/syntax_icon.gif FETCH  CURSOR_NAME  [ INTO FETCH_LIST ] 

The syntax for Oracle is as follows:

 graphics/syntax_icon.gif FETCH  CURSOR_NAME  {INTO : HOST_VARIABLE [[ INDICATOR ] : INDICATOR_VARIABLE ] [, : HOST_VARIABLE [[ INDICATOR ] : INDICATOR_VARIABLE ]]  USING DESCRIPTOR  DESCRIPTOR  ] 

The syntax for dBASE is as follows:

 graphics/syntax_icon.gif 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:

 graphics/mysql_icon.gif 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.

graphics/note_icon.gif

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 associated memory 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:

 graphics/syntax_icon.gif CLOSE  CURSOR_NAME  DEALLOCATE CURSOR  CURSOR_NAME  

When a cursor is closed in Oracle, the resources and name are released without the DEALLOCATE statement. The syntax for Oracle is as follows:

 graphics/syntax_icon.gif 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:

 graphics/syntax_icon.gif CLOSE  CURSOR_NAME  
graphics/note_icon.gif

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.



Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net