SQL Server/T-SQL/Cursor/Declare CURSOR
Cursor declaration syntax
<source lang="sql">
DECLARE <cursor name> CURSOR [LOCAL|GLOBAL] [FORWARD_ONLY|SCROLL] [STATIC|KEYSET|DYNAMIC|FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR <SELECT statement> [FOR UPDATE [OF <column name >[,...n]]]
</source>
Declare a cursor
<source lang="sql">
17> create table employee( 18> ID int, 19> name nvarchar (10), 20> salary int, 21> start_date datetime, 22> city nvarchar (10), 23> region char (1)) 24> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
1> 2> DECLARE curEmployee INSENSITIVE CURSOR 3> FOR SELECT ID, Name FROM Employee 4> DECLARE @ID Int 5> DECLARE @Name VarChar(100) 6> 7> OPEN curEmployee 8> FETCH NEXT FROM curEmployee INTO @ID, @Name 9> WHILE @@Fetch_Status = 0 10> BEGIN 11> PRINT @Name 12> FETCH NEXT FROM curEmployee INTO @ID, @Name 13> END 14> CLOSE curEmployee 15> 16> DEALLOCATE curEmployee 17> GO Jason Robert Celia Linda David James Alison Chris Mary 1> 2> 3> drop table employee 4> GO 1>
</source>
DECLARE CURSOR FOR
<source lang="sql">
3> CREATE TABLE authors( 4> au_id varchar(11), 5> au_lname varchar(40) NOT NULL, 6> au_fname varchar(20) NOT NULL, 7> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 8> address varchar(40) NULL, 9> city varchar(20) NULL, 10> state char(2) NULL, 11> zip char(5) NULL, 12> contract bit NOT NULL 13> ) 14> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO 1> 2> 3> 4> CREATE TABLE titles( 5> title_id varchar(20), 6> title varchar(80) NOT NULL, 7> type char(12) NOT NULL, 8> pub_id char(4) NULL, 9> price money NULL, 10> advance money NULL, 11> royalty int NULL, 12> ytd_sales int NULL, 13> notes varchar(200) NULL, 14> pubdate datetime NOT NULL 15> ) 16> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO 1> 2> CREATE TABLE titleauthor( 3> au_id varchar(20), 4> title_id varchar(20), 5> au_ord tinyint NULL, 6> royaltyper int NULL 7> ) 8> GO 1> 2> insert titleauthor values("1", "2", 1, 60) 3> insert titleauthor values("2", "3", 1, 100) 4> insert titleauthor values("3", "4", 1, 100) 5> insert titleauthor values("4", "5", 1, 100) 6> insert titleauthor values("5", "6", 1, 100) 7> insert titleauthor values("6", "7", 2, 40) 8> insert titleauthor values("7", "8", 1, 100) 9> insert titleauthor values("8", "9", 1, 100) 10> GO 1> 2> 3> DECLARE @au_id char(11), @au_lname varchar(40), @title_id char(6), 4> @au_id2 char(11), @title_id2 char(6), @title varchar(80) 5> 6> DECLARE au_cursor CURSOR FOR 7> SELECT au_id, au_lname FROM authors ORDER BY au_id 8> 9> DECLARE au_titles CURSOR FOR 10> SELECT au_id, title_id FROM titleauthor ORDER BY au_id 11> 12> DECLARE titles_cursor CURSOR FOR 13> SELECT title_id, title FROM titles ORDER BY title 14> 15> OPEN au_cursor 16> FETCH NEXT FROM au_cursor INTO @au_id, @au_lname 17> 18> WHILE (@@FETCH_STATUS=0) 19> BEGIN 20> OPEN au_titles 21> FETCH NEXT FROM au_titles INTO @au_id2, @title_id 22> 23> 24> WHILE (@@FETCH_STATUS=0) 25> BEGIN 26> 27> 28> IF (@au_id=@au_id2) 29> BEGIN 30> OPEN titles_cursor 31> FETCH NEXT FROM titles_cursor INTO 32> @title_id2, @title 33> 34> WHILE (@@FETCH_STATUS=0) 35> BEGIN 36> 37> IF (@title_id=@title_id2) 38> SELECT @au_id, @au_lname, @title 39> 40> FETCH NEXT FROM titles_cursor INTO 41> @title_id2, @title 42> END 43> CLOSE titles_cursor 44> END 45> FETCH NEXT FROM au_titles INTO @au_id2, @title_id 46> END 47> 48> CLOSE au_titles 49> FETCH NEXT FROM au_cursor INTO @au_id, @au_lname 50> END 51> CLOSE au_cursor 52> 53> DEALLOCATE titles_cursor 54> DEALLOCATE au_titles 55> DEALLOCATE au_cursor 56> GO
---------------------------------------- --------------------------------------------------------------------------------
1 Joe The
---------------------------------------- --------------------------------------------------------------------------------
1 Joe The
---------------------------------------- --------------------------------------------------------------------------------
1 Joe The
---------------------------------------- --------------------------------------------------------------------------------
2 Jack Emotional
---------------------------------------- --------------------------------------------------------------------------------
2 Jack Emotional
---------------------------------------- --------------------------------------------------------------------------------
2 Jack Emotional
---------------------------------------- --------------------------------------------------------------------------------
3 Pink Prolonged
---------------------------------------- --------------------------------------------------------------------------------
3 Pink Prolonged
---------------------------------------- --------------------------------------------------------------------------------
3 Pink Prolonged
---------------------------------------- --------------------------------------------------------------------------------
4 Blue With
---------------------------------------- --------------------------------------------------------------------------------
4 Blue With
---------------------------------------- --------------------------------------------------------------------------------
4 Blue With
---------------------------------------- --------------------------------------------------------------------------------
5 Red Valley
---------------------------------------- --------------------------------------------------------------------------------
5 Red Valley
---------------------------------------- --------------------------------------------------------------------------------
5 Red Valley
---------------------------------------- --------------------------------------------------------------------------------
6 Black Any?
---------------------------------------- --------------------------------------------------------------------------------
6 Black Any?
---------------------------------------- --------------------------------------------------------------------------------
6 Black Any?
---------------------------------------- --------------------------------------------------------------------------------
7 White Fifty
---------------------------------------- --------------------------------------------------------------------------------
7 White Fifty
---------------------------------------- --------------------------------------------------------------------------------
7 White Fifty 1>
</source>
