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
