SQL in a Nutshell, 2nd Edition

Name

DECLARE CURSOR Command

Synopsis

The DECLARE command is one of four commands used in cursor processing, along with FETCH, OPEN, and CLOSE. Cursors allow you to process queries one row at a time, rather than in a complete set. The DECLARE CURSOR command specifies the exact records to be retrieved and manipulated one row at a time from a specific table or view.

In other words, cursors are especially important for relational databases because databases are set-based, while most client-centric programming languages are row-based. This is important for two reasons. First, cursors allow programmers to program using methodologies supported by their favorite row-based programming language. Second, cursors run counter to the default behavior of some relational database platforms, which operate on a set of records and, on those specific platforms, may be noticeably slower than standard set-based operations.

Platform	Command
DB2	Supported, with limitations
MySQL	Not supported
Oracle	Supported, with limitations
PostgreSQL	Supported, with limitations
SQL Server	Supported, with limitations

SQL2003 Syntax

DECLARE cursor_name [ {SENSITIVE | INSENSITIVE | ASENSITIVE} ]
[[NO] SCROLL] CURSOR [{WITH | WITHOUT} HOLD]
    [{WITH | WITHOUT} RETURN}]
FOR select_statement 
[FOR {READ ONLY | UPDATE [OF column [,...] ]} ]

Keywords

DECLARE cursor_name

Gives the cursor a unique name in the context in which it is defined — for example, in the database or schema where it is created. The name must be unique, ...