в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:22:32Z

1 версия

Новая страница

== Fetch cursor value with while loop==



<source lang="sql">
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</source>

== FETCH NEXT FROM==



<source lang="sql">
3>
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</source>

== The syntax of the FETCH statement==



<source lang="sql">
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name
[INTO @variable_name [, ...]]</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/Cursor/Fetch cursor - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия