Cursores no Sql Server 2000 / 2005
Veja um exemplo de cursor com fins didáticos, para iniciantes.
USE NORTHWIND
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'NewOrder')
DROP PROCEDURE NewOrder
GO
CREATE PROCEDURE NewOrder
@custID nchar(5), @empID int, @orderDate datetime,
@shipperID int, @Details varchar(1000)
AS
DECLARE @ErrorCode int
DECLARE @OrderID int
-- Add new row to the Orders table
DECLARE @shipcompany nvarchar(40)
DECLARE @shipAddress nvarchar(60), @shipCity nvarchar(15)
DECLARE @shipRegion nvarchar(15), @shipPCode nvarchar(10)
DECLARE @shipCountry nvarchar(15)
SELECT @shipCompany=CompanyName,
@shipAddress=Address,
@shipCity=City,
@shipRegion=Region,
@shipPCode=PostalCode,
@shipCountry=Country
FROM Customers
WHERE CustomerID = @custID
IF @@ROWCOUNT = 0
RETURN(-100) -- Invalid Customer!
SELECT * FROM Employees WHERE EmployeeID = @empID
IF @@ROWCOUNT = 0
RETURN(-101) -- Invalid Employee!
SELECT * FROM Shippers
WHERE ShipperID = @shipperID
IF @@ROWCOUNT = 0
RETURN(-102) -- Invalid Shipper!
BEGIN TRANSACTION
INSERT Orders (CustomerID, EmployeeID, OrderDate, ShipVia,
ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry)
VALUES (@custID, @empID, @orderDate, @ShipperID,
@shipCompany, @shipAddress, @ShipCity, @ShipRegion,
@shipPCode, @shipCountry)
SET @ErrorCode=@@ERROR
IF (@ErrorCode <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN (-@ErrorCode)
END
SET @OrderID = @@IDENTITY
-- Now add rows to the Order Details table
-- All new rows will have the same OrderID
DECLARE @TotLines int
DECLARE @currLine int
SET @currLine = 0
-- Use the CEILING function because the length of the
-- @Details variable may be less than 18 characters long !!!
SET @TotLines = Ceiling(Len(@Details)/18)
DECLARE @Qty smallint, @Dscnt real, @Price money
DECLARE @ProdID int
WHILE @currLine <= @TotLines
BEGIN
SET @ProdID = SUBSTRING(@Details, @currLine*18 + 1, 6)
SET @Qty = SUBSTRING(@Details, @currLine*18 + 7, 6)
SET @Dscnt = SUBSTRING(@Details, @currLine*18 + 13,6)
SET @currLine = @currLine + 1
SELECT @Price=UnitPrice FROM Products WHERE ProductID=@ProdID
INSERT [Order Details] (OrderID, ProductID, Quantity,
UnitPrice, Discount)
VALUES (@OrderID, @ProdID, @Qty, @Price, @Dscnt)
SET @ErrorCode = @@ERROR
IF (@ErrorCode <> 0) GOTO DetailError
END
COMMIT TRANSACTION
RETURN (0)
DetailError:
ROLLBACK TRANSACTION
RETURN(@ErrorCode)
--------------------------------------------------------------------------------------------------------------------------------
USE Northwind
DECLARE AllContacts CURSOR
KEYSET
FOR
SELECT CompanyName, ContactName, ContactTitle
FROM Customers
GO
OPEN AllContacts
PRINT 'The cursor contains ' + CONVERT(char(3), @@CURSOR_ROWS) + ' rows'
DECLARE @Company varchar(40)
DECLARE @Contact varchar(40)
DECLARE @Title varchar(40)
FETCH FIRST FROM AllContacts INTO @Company, @Contact, @Title
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONVERT(char(30), @Company) + CONVERT(char(25), @Contact) + CONVERT(char(20), @Title)
FETCH NEXT FROM AllContacts INTO @Company, @Contact, @Title
END
CLOSE AllContacts
DEALLOCATE AllContacts
