7.1 : Cursors

SQL Server 2005

2

• What is a cursor• Types of cursors• Working with cursors• Declare cursor• Open cursor• Fetch cursor• Close cursor

Contents

3

Objectives

At the end of this presentation you should be able to:

• Understand the concept of cursor• Understand why cursor is required in a database• Understand the different types of cursors• Working with cursor

4

What is a Cursor

• A cursor is a database object that helps in accessing and manipulating row by row data in a given result set.

• Cursor serves as a mechanism for an application to operate on a single row or a small set of rows.

5

What is a Cursor (Continued)

• It allows specific rows to be retrieved from result set

• It allows the current row in result set to be modified

• Helps to navigate from the current row in the result set to a different row

• Allows data to be modified by other users to be visible in the result set.

6

Types of Cursors

Cursors can be classified as follows:• Static• Forward_only• Keyset driven• Dynamic

7

Types of cursors (Continued)

Cursor Type

Description

Static It is a cursor that does not reflect any kind of modification that has been made either to the associated tables in the database or to the result set of the cursor itself. Hence, none of the UPDATE,DELETE or INSERT operations is reflected in the cursor unless it is closed and reopened

Forward_ only

Forward_only cursor is the one which doesn’t support backward scrolling .This cursor only fetches rows in a serial order ,i.e. from beginning of the result set till end. The rows from the database are retrieved only if these are explicitly fetched. The changes made by an INSERT , UPDATE and DELETE operations are reflected only when the rows are fetched from cursors

8

Types of Cursors (Continued)

Cursor Type Description

Keyset driven keyset driven cursors are cursors that show changes in the result set caused by UPDATE and DELETE statement while the cursor is open but do not reflect the changes caused by the INSERT operation

Dynamic Dynamic cursors are cursors that reflect all the changes that have been made to the result set while scrolling through it . All changes caused by any of the INSERT, UPDATE or DELETE operations are reflected in these cursors

9

To work with Cursors

• The following process defines the working of a cursor in SQL Server:– The cursor needs to be defined and its attributes

need to be set.– The cursor needs to be opened– The required rows need to be fetched from the cursor– The data in the current row of the cursor can be

modified, if required

10

To Work with Cursors (Continued)

• User can define a cursor and its characteristics set by using the DECLARE CURSOR statement.

• To Declare Cursors :

Syntax

DECLARE cursor_name[INSENSITIVE ]

[SCROLL] CURSOR FOR{select_statement | prepared statement} [FOR {READ ONLY | UPDATE[OF column_list]}]

11

To Work with Cursors (Continued)

• To Open Cursor : User can open a cursor using the open statement.

• To Fetch Data : Once a cursor has been opened, you can retrieve a specific row from the result set of the cursor.SQL Server provides the FETCH statement to accomplish the task.

Syntax OPEN Cursor_name

12

To Work with Cursors (Continued)

• To Fetch Data : Syntax

• To Close cursor : User must close a cursor in order to release the resources held by it. A cursor can be closed with CLOSE statement

• Deallocate Cursor : Will remove the cursor reference. When the cursor is deallocated and if it is the last reference, the structures of the cursor are released.

FETCH [[NEXT|PRIOR|FIRST|LAST]FROM]cursor_name [using

Descriptor :sqlda_struct |INTO : hvar[…]]

CLOSE cursor_name

DEALLOCATE cursor_name

13

Example 1 - Cursor

declare @author_id char(20)declare c2 cursor forward_only forselect au_id from authorsopen c2fetch next from c2 into @author_idwhile @@fetch_status = 0beginprint @author_idfetch next from c2into @author_idendclose c2deallocate c2

14

Example 2 - Cursor

Declare example cursor keysetFor select * from reportOpen exampleFetch last from exampleDelete from report where id=2Fetch relative -1 from exampleSelect @@fetch_statusClose exampleDeallocate example

15

Activity Time(60 minutes)

• Activity: 7.1

Create a cursor to retrieve and display the Publisher_ID and Publisher_Name from the Publisher table.

• Activity 7.2

Create a Cursor which retrieves the Title, Type and Price from the Title table and displays the records one after the other in the following format.

-----------------------------------------------------------------------

Title : The Busy Executives Database Guide

Title Type : Business

Price : 19.99

-----------------------------------------------------------------------

16

Activity Time(60 minutes) continued

• Activity: 7.3Create a cursor to update the Quantity of the Items in the Items Table for the Orders Ordered in the OrderDetail table.Hint: Use Nested Cursor (Cursor within a Cursor)

17

Questions & Comments