O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required