SQL Server/T-SQL Tutorial/Cursor/Declare Cursor
Содержание
- 1 A cursor created in SQL server.
- 2 A DECLARE CURSOR statement that declares a dynamic local cursor
- 3 Cursor on a Single-Column Key
- 4 Declare a cursor variable to hold the cursor output variable
- 5 DECLARE CURSOR FAST_FORWARD FOR
- 6 DECLARE CURSOR GLOBAL FOR
- 7 DECLARE cursor LOCAL
- 8 Four cursors
- 9 INSENSITIVE Cursor
- 10 Local scroll cursor
- 11 Static cursor
- 12 The syntax of the DECLARE CURSOR statement
A cursor created in SQL server.
4> CREATE TABLE Employees(
5> empid int NOT NULL,
6> empname varchar(10) NOT NULL,
7> deptno int NULL ,
8> jobid int ,
9> salary decimal(7,2) NOT NULL
10> )
11> GO
1>
2> INSERT INTO Employees VALUES(1, "Leo", 400, 30, 3456.00)
3> INSERT INTO Employees VALUES(2, "Ros", 200, 20, 4325.00)
4> INSERT INTO Employees VALUES(3, "Chris", 100, 10, 8952.00)
5> INSERT INTO Employees VALUES(4, "Rob", 400, 30, 1234.00)
6> INSERT INTO Employees VALUES(5, "Linda", 400, 30, 4567.00)
7> INSERT INTO Employees VALUES(6, "Lisa", NULL, 30, 8765.00)
8> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> DECLARE
2> @empname VARCHAR(40),
3> @salary MONEY,
4> @empid integer
5> DECLARE getemp_curs CURSOR
6> FOR
7> SELECT empid, empname, salary FROM employees WHERE empid = 1
8> OPEN getemp_curs
9> FETCH NEXT FROM getemp_curs into @empid, @empname, @salary
10> WHILE @@FETCH_STATUS = 0 BEGIN
11> UPDATE employees SET salary = @salary WHERE empid = @empid
12> FETCH NEXT FROM getemp_curs into @empid, @empname, @salary
13> END
14> CLOSE getemp_curs
15> DEALLOCATE getemp_curs
16> GO
(1 rows affected)
1>
2> drop table Employees;
3> GO
A DECLARE CURSOR statement that declares a dynamic local cursor
7> create table Billings (
8> BankerID INTEGER,
9> BillingNumber INTEGER,
10> BillingDate datetime,
11> BillingTotal INTEGER,
12> TermsID INTEGER,
13> BillingDueDate datetime ,
14> PaymentTotal INTEGER,
15> CreditTotal INTEGER
16>
17> );
18> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3>
4> create table Bankers(
5> BankerID Integer,
6> BankerName VARCHAR(20),
7> BankerContactLName VARCHAR(20),
8> BankerContactFName VARCHAR(20),
9> BankerCity VARCHAR(20),
10> BankerState VARCHAR(20),
11> BankerZipCode VARCHAR(20),
12> BankerPhone VARCHAR(20)
13> )
14> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3>
4> DECLARE Billings_Cursor CURSOR
5> FOR
6> SELECT * FROM Billings
7>
8> --A DECLARE CURSOR statement that declares a dynamic local cursor
9>
10> DECLARE BankerBilling_Cursor CURSOR
11> LOCAL DYNAMIC
12> FOR
13> SELECT BankerName, BillingDate, BillingTotal
14> FROM Bankers JOIN Billings ON Bankers.BankerID = Billings.BankerID
15> GO
1>
2> drop table Billings;
3> GO
1> drop table Bankers;
2> GO
1>
Cursor on a Single-Column Key
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20),
13> managerid INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> DECLARE
4> @ID int,
5> @Count int,
6> @DateTime datetime
7> SELECT -- initialize
8> @Count = 0,
9> @DateTime = GETDATE ()
10> DECLARE c CURSOR FOR -- set up cursor
11> SELECT
12> ID
13> FROM
14> Employee
15> OPEN c
16> FETCH c INTO -- get first order
17> @ID
18> WHILE @@FETCH_STATUS = 0 -- loop through orders
19> BEGIN
20> SET
21> @Count = @Count + 1 -- increment counter
22> FETCH c INTO -- pick up next order
23> @ID
24> END
25> CLOSE c -- clean up
26> DEALLOCATE c
27> GO
1>
2>
3> drop table employee;
4> GO
Declare a cursor variable to hold the cursor output variable
22> IF cursor_status("variable", "@Report") != 1
5> CREATE TABLE authors(
6> au_id varchar(11),
7> au_lname varchar(40) NOT NULL,
8> au_fname varchar(20) NOT NULL,
9> phone char(12) NOT NULL DEFAULT ("UNKNOWN"),
10> address varchar(40) NULL,
11> city varchar(20) NULL,
12> state char(2) NULL,
13> zip char(5) NULL,
14> contract bit NOT NULL
15> )
16> 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> DECLARE authors_cursor CURSOR KEYSET FOR
4> SELECT au_lname
5> FROM authors
6> WHERE au_lname LIKE "S%"
7>
8> OPEN authors_cursor
9>
10>
11>
12> DECLARE @Report CURSOR
13>
14>
15> EXEC master.dbo.sp_describe_cursor
16> @cursor_return = @Report OUTPUT,
17> @cursor_source = "global",
18> @cursor_identity = "authors_cursor"
19>
20>
21>
22> IF cursor_status("variable", "@Report") != 1
23> PRINT "No information available from the cursor"
24>
25> ELSE BEGIN
26>
27> WHILE (@@fetch_status = 0)
28> BEGIN
29> FETCH NEXT FROM @Report
30> END
31> END
32>
33>
34> IF cursor_status("variable", "@Report") >= -1
35> BEGIN
36> CLOSE @Report
37> DEALLOCATE @Report
38> END
39> GO
1>
2>
3> CLOSE authors_cursor
4> DEALLOCATE authors_cursor
5> GO
1>
2> drop table authors;
3> GO
DECLARE CURSOR FAST_FORWARD FOR
3>
4> IF OBJECT_ID("dbo.Groups") IS NOT NULL
5> DROP TABLE dbo.Groups;
6> GO
1>
2> CREATE TABLE dbo.Groups
3> (
4> groupid VARCHAR(10) NOT NULL,
5> memberid INT NOT NULL,
6> string VARCHAR(10) NOT NULL,
7> val INT NOT NULL,
8> PRIMARY KEY (groupid, memberid)
9> );
10>
11> INSERT INTO dbo.Groups(groupid, memberid, string, val)
12> VALUES("a", 3, "stra1", 6);
13> INSERT INTO dbo.Groups(groupid, memberid, string, val)
14> VALUES("a", 9, "stra2", 7);
15> INSERT INTO dbo.Groups(groupid, memberid, string, val)
16> VALUES("b", 2, "strb1", 3);
17> INSERT INTO dbo.Groups(groupid, memberid, string, val)
18> VALUES("b", 4, "strb2", 7);
19> INSERT INTO dbo.Groups(groupid, memberid, string, val)
20> VALUES("b", 5, "strb3", 3);
21> INSERT INTO dbo.Groups(groupid, memberid, string, val)
22> VALUES("b", 9, "strb4", 11);
23> INSERT INTO dbo.Groups(groupid, memberid, string, val)
24> VALUES("c", 3, "strc1", 8);
25> INSERT INTO dbo.Groups(groupid, memberid, string, val)
26> VALUES("c", 7, "strc2", 10);
27> INSERT INTO dbo.Groups(groupid, memberid, string, val)
28> VALUES("c", 9, "strc3", 12);
29>
30>
31> DECLARE
32> @Result TABLE(groupid VARCHAR(10), product BIGINT);
33> DECLARE
34> @groupid AS VARCHAR(10), @prvgroupid AS VARCHAR(10),
35> @val AS INT, @product AS BIGINT;
36> DECLARE C CURSOR FAST_FORWARD FOR
37> SELECT groupid, val FROM dbo.Groups ORDER BY groupid;
38> OPEN C
39> FETCH NEXT FROM C INTO @groupid, @val;
40> SELECT @prvgroupid = @groupid, @product = 1;
41> WHILE @@fetch_status = 0
42> BEGIN
43> IF @groupid <> @prvgroupid
44> BEGIN
45> INSERT INTO @Result VALUES(@prvgroupid, @product);
46> SELECT @prvgroupid = @groupid, @product = 1;
47> END
48> SET @product = @product * @val;
49> FETCH NEXT FROM C INTO @groupid, @val;
50> END
51> IF @prvgroupid IS NOT NULL
52> INSERT INTO @Result VALUES(@prvgroupid, @product);
53> CLOSE C;
54> DEALLOCATE C;
55>
56> SELECT groupid, product FROM @Result;
57>
58> GO
groupid product
---------- --------------------
a 42
b 693
c 960
1>
2> drop table Groups;
3> GO
DECLARE CURSOR GLOBAL FOR
6> CREATE TABLE Orders (
7> OrderID int NOT NULL ,
8> CustomerID nchar (5) NULL ,
9> EmployeeID int NULL ,
10> OrderDate datetime NULL ,
11> RequiredDate datetime NULL ,
12> ShippedDate datetime NULL ,
13> ShipVia int NULL ,
14> Freight money NULL DEFAULT (0),
15> ShipName nvarchar (40) NULL ,
16> ShipAddress nvarchar (60) NULL ,
17> ShipCity nvarchar (15) NULL ,
18> ShipRegion nvarchar (15) NULL ,
19> ShipPostalCode nvarchar (10) NULL ,
20> ShipCountry nvarchar (15) NULL
21> )
22> GO
1> CREATE PROCEDURE spCursorScope
2> AS
3> DECLARE @Counter int,
4> @OrderID int,
5> @CustomerID varchar(5)
6>
7> DECLARE CursorTest CURSOR
8> GLOBAL
9> FOR
10> SELECT OrderID, CustomerID
11> FROM Orders
12>
13> SELECT @Counter = 1
14> OPEN CursorTest
15> FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
16> PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " +
17> CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID
18> WHILE (@Counter<=5) AND (@@FETCH_STATUS=0)
19> BEGIN
20> SELECT @Counter = @Counter + 1
21> FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
22> PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " +
23> CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID
24> END
25> GO
1>
2> drop PROCEDURE spCursorScope;
3> GO
1>
2> drop table Orders;
3> GO
DECLARE cursor LOCAL
1> CREATE TABLE Orders (
2> OrderID int NOT NULL ,
3> CustomerID nchar (5) NULL ,
4> EmployeeID int NULL ,
5> OrderDate datetime NULL ,
6> RequiredDate datetime NULL ,
7> ShippedDate datetime NULL ,
8> ShipVia int NULL ,
9> Freight money NULL DEFAULT (0),
10> ShipName nvarchar (40) NULL ,
11> ShipAddress nvarchar (60) NULL ,
12> ShipCity nvarchar (15) NULL ,
13> ShipRegion nvarchar (15) NULL ,
14> ShipPostalCode nvarchar (10) NULL ,
15> ShipCountry nvarchar (15) NULL
16> )
17> GO
1>
2>
3> INSERT INTO Orders VALUES (10248,"1",5,"7/4/1996","8/1/2001","7/16/2001",3,32.38,"V","A","R", NULL,N"51100","France")
9> go
1>
2>
3> create PROCEDURE spCursorScope
4> AS
5> DECLARE @Counter int,
6> @OrderID int,
7> @CustomerID varchar(5)
8> DECLARE CursorTest cursor
9> LOCAL
10> FOR
11> SELECT OrderID, CustomerID
12> FROM Orders
13>
14> SELECT @Counter = 1
15> OPEN CursorTest
16> FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
17> PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " +
18> CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID
19>
20> WHILE (@Counter<=5) AND (@@FETCH_STATUS=0)
21> BEGIN
22> SELECT @Counter = @Counter + 1
23> FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
24> PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " +
25> CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID
26> END
27> GO
1>
2> drop PROCEDURE spCursorScope;
3> GO
1>
2> drop table Orders;
3> GO
Four cursors
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>
INSENSITIVE Cursor
4>
5> CREATE TABLE Product(
6> ProductID int NOT NULL,
7> Name nvarchar(25) NOT NULL,
8> ProductNumber nvarchar(25) ,
9> Color nvarchar(15) NULL,
10> StandardCost money NOT NULL,
11> Size nvarchar(5) NULL,
12> Weight decimal(8, 2) NULL,
13> ProductLine nchar(20) NULL,
14> SellStartDate datetime NOT NULL,
15> SellEndDate datetime NULL
16> )
17> GO
1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22");
2> GO
(1 rows affected)
1> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22");
2> GO
(1 rows affected)
1> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22");
2> GO
(1 rows affected)
1> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22");
2> GO
(1 rows affected)
1> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22");
2> GO
(1 rows affected)
1> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22");
2> GO
(1 rows affected)
1> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22");
2> GO
(1 rows affected)
1> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22");
2> GO
(1 rows affected)
1> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22");
2> GO
(1 rows affected)
1> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22");
2> GO
(1 rows affected)
1>
2>
3>
4> DECLARE curProduct INSENSITIVE CURSOR
5> FOR SELECT ProductID, Name FROM Product
6> DECLARE @ProdID Int
7> DECLARE @ProdName VarChar(100)
8> OPEN curProduct
9> FETCH NEXT FROM curProduct INTO @ProdID, @ProdName
10> WHILE @@Fetch_Status = 0
11> BEGIN
12> PRINT @ProdName
13> FETCH NEXT FROM curProduct INTO @ProdID, @ProdName
14> END
15> CLOSE curProduct
16> DEALLOCATE curProduct
17> GO
Product A
Product B
Product C
Product D
Product E
Product F
Product G
Product H
Product I
Product J
1>
2> drop table Product;
3> GO
Local scroll cursor
5> CREATE TABLE Orders (
6> OrderID int NOT NULL ,
7> CustomerID nchar (5) NULL ,
8> EmployeeID int NULL ,
9> OrderDate datetime NULL ,
10> RequiredDate datetime NULL ,
11> ShippedDate datetime NULL ,
12> ShipVia int NULL ,
13> Freight money NULL DEFAULT (0),
14> ShipName nvarchar (40) NULL ,
15> ShipAddress nvarchar (60) NULL ,
16> ShipCity nvarchar (15) NULL ,
17> ShipRegion nvarchar (15) NULL ,
18> ShipPostalCode nvarchar (10) NULL ,
19> ShipCountry nvarchar (15) NULL
20> )
21> GO
1>
2>
3> INSERT INTO Orders VALUES (10248,"1",5,"7/4/1996","8/1/2001","7/16/2001",3,32.38,"V","A","R", NULL,N"51100","France")
4> go
(1 rows affected)
1>
2>
3> CREATE PROCEDURE spCursorScroll
4> AS
5> DECLARE @Counter int,
6> @OrderID int,
7> @CustomerID varchar(5)
8> DECLARE CursorTest cursor
9> LOCAL
10> SCROLL
11> FOR
12> SELECT OrderID, CustomerID
13> FROM Orders
14> SELECT @Counter = 1
15> OPEN CursorTest
16> FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
17> PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " +
18> CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID
19> WHILE (@Counter<=5) AND (@@FETCH_STATUS=0)
20> BEGIN
21> SELECT @Counter = @Counter + 1
22> FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
23> PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " +
24> CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID
25> END
26> WHILE (@Counter>1) AND (@@FETCH_STATUS=0)
27> BEGIN
28> SELECT @Counter = @Counter - 1
29> FETCH PRIOR FROM CursorTest INTO @OrderID, @CustomerID
30> PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " +
31> CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID
32> END
33>
34> CLOSE CursorTest
35> DEALLOCATE CursorTest
36> GO
1>
2> drop PROCEDURE spCursorScroll;
3> GO
1>
2> drop table Orders;
3> GO
Static cursor
18> create table Bankers(
19> BankerID Integer,
20> BankerName VARCHAR(20),
21> BankerContactLName VARCHAR(20),
22> BankerContactFName VARCHAR(20),
23> BankerCity VARCHAR(20),
24> BankerState VARCHAR(20),
25> BankerZipCode VARCHAR(20),
26> BankerPhone VARCHAR(20)
27> )
28> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2> DECLARE Bankers_Cursor CURSOR
3> STATIC
4> FOR
5> SELECT BankerID, BankerName
6> FROM Bankers
7> ORDER BY BankerName
8> OPEN Bankers_Cursor
9> FETCH NEXT FROM Bankers_Cursor
10> WHILE @@FETCH_STATUS = 0
11> FETCH NEXT FROM Bankers_Cursor
12> CLOSE Bankers_Cursor
13> DEALLOCATE Bankers_Cursor
14> GO
BankerID BankerName
----------- --------------------
1 ABC Inc.
(1 rows affected)
BankerID BankerName
----------- --------------------
2 DEF Inc.
(1 rows affected)
BankerID BankerName
----------- --------------------
9 DFG Inc.
(1 rows affected)
BankerID BankerName
----------- --------------------
0 GHJ Inc.
(1 rows affected)
BankerID BankerName
----------- --------------------
3 HJI Inc.
(1 rows affected)
BankerID BankerName
----------- --------------------
7 OIP Inc.
(1 rows affected)
BankerID BankerName
----------- --------------------
4 QWE Inc.
(1 rows affected)
BankerID BankerName
----------- --------------------
5 RTY Inc.
(1 rows affected)
BankerID BankerName
----------- --------------------
8 SAD Inc.
(1 rows affected)
BankerID BankerName
----------- --------------------
6 YUI Inc.
(1 rows affected)
BankerID BankerName
----------- --------------------
(0 rows affected)
1>
2>
3> drop table Bankers;
4> GO
1>
The syntax of the DECLARE CURSOR statement
DECLARE cursor_name CURSOR
[LOCAL|GLOBAL]
[FORWARD_ONLY|SCROLL]
[FAST_FORWARD|STATIC|KEYSET|DYNAMIC]
[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]
FOR select_statement
[FOR UPDATE [OF column_name [, ...]]]