SQL Server/T-SQL Tutorial/Cursor/Fetch cursor

Материал из SQL эксперт
Перейти к: навигация, поиск

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>