What are cursors in SQL and how are they built?
In some SGDB it is possible to open data cursors from the work environment itself, normally stored procedures are used for this. The syntax for defining a cursor is as follows:
DECLARE
cursor-name
FOR
query-spec
For example:
DECLARE
My_Cursor
FOR
SELECT emp_num, name, position, salary
FROM employees
WHERE num_dept=”informatics”
This command is purely declarative, it simply specifies the rows and columns to retrieve. The query is executed when the cursor is opened or activated. The clause is optional and specifies an ordering for the cursor rows; if not specified, the ordering of the rows is defined by the DBMS manager.
To open or activate a cursor, the OPEN SQL command is used, the syntax in the following:
open
cursor-name
Opening the cursor evaluates the query appearing in its definition, using the current values of any parameters referenced in the query, to produce a collection of rows. The pointer is positioned in front of the first row of data (current record), this statement does not retrieve any rows.
Once the cursor is open, the FETCH clause is used to retrieve the cursor rows, the syntax is as follows:
FETCH
cursor-name
INTO
variable-list
List – variables are the variables that will contain the data retrieved from the cursor row, in the definition they must be separated by commas. In the list of variables, as many variables must be defined as there are columns in the row to be retrieved.
To close a cursor, the CLOSE command is used. This command makes the pointer on the current record disappear. The syntax is:
CLOSE
cursor-name
Finally, and to eliminate the cursor, the DROP CURSOR command is used. Its syntax is the following:
DROP CURSOR
cursor-name
Example (on SQL-SERVER):
Open a cursor and move around it
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName
FROM Northwind.dbo.Employees
WHERE LastName like ‘B%’
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
Open a cursor and print its content
SET NO COUNT ON
DECLARE
@au_id varchar(11),
@au_fname varchar(20),
@au_lname varchar(40),
@messagevarchar(80),
@titlevarchar(80)
PRINT “——– Utah Authors report ——–”
DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state=”UT”
ORDER BY au_id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ” ”
select
@message = “—– Books by Author: ” +
@au_fname + ” ” + @au_lname
PRINT @message
DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND ta.au_id = au_id
OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title
IF @@FETCH_STATUS 0
PRINT ” <
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = ” ” + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title
END
CLOSE titles_cursor
DEALOCATE titles_cursor
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END
CLOSE authors_cursor
DEALOCATE authors_cursor
GO
move a cursor
USE pubs
GO
DECLARE authors_cursor CURSOR FOR
SELECT au_lname
FROM authors
WHERE au_lname LIKE “B%”
ORDER BY au_lname
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM authors_cursor
END
CLOSE authors_cursor
DEALOCATE authors_cursor
Loop through a cursor by saving the values in variables
USE pubs
GO
DECLARE @au_lname varchar(40)
DECLARE @au_fname varchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname
FROM authors
WHERE au_lname LIKE “B%”
ORDER BY au_lname, au_fname
OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT “Author: ” + @au_fname + ” ” + @au_lname
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
END
CLOSE authors_cursor
DEALOCATE authors_cursor
Facebook Comments
Disqus