Cursors

In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

To use cursors in SQL procedures, you need to do the following:
Declare a cursor that defines a result set.
Open the cursor to establish the result set.
Fetch the data into local variables as needed from the cursor, one row at a time.
Close the cursor when done

To work with cursors you must use the following SQL statements:
DECLARE CURSOR
OPEN
FETCH
CLOSE

Example

CREATE PROCEDURE sum_salaries(OUT sum INTEGER)

LANGUAGE SQL

BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE p_sum INTEGER;

DECLARE p_sal INTEGER;

DECLARE c CURSOR FOR SELECT SALARY FROM EMPLOYEE;

SET p_sum = 0;

OPEN c;

FETCH FROM c INTO p_sal;

WHILE(SQLSTATE = '00000') DO

SET p_sum = p_sum + p_sal;

FETCH FROM c INTO p_sal;

END WHILE;

CLOSE c;

SET sum = p_sum;

END

Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Example

DECLARE @Ename char(50)

DECLARE c1 CURSOR READ_ONLY FOR Select ename from emp

OPEN c1

FETCH NEXT FROM c1 INTO @Ename

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @Ename

FETCH NEXT FROM c1 INTO @Ename

END

CLOSE c1

DEALLOCATE c1

If you are going to update the rows as you go through them, you can use the UPDATE clause when you declare a cursor. You'll also have to remove the READ_ONLY clause from above.

DECLARE c1 CURSOR FOR SELECT au_id, au_lname FROM authors

FOR UPDATE OF au_lname

You can code your UPDATE statement to update the current row in the cursor like this

UPDATE authors

SET au_lname = UPPER(Smith)

WHERE CURRENT OF c1