Cursores no Sql Server 2000 / 2005

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (2)  (0)

Veja um exemplo de cursor no Sql Server 2000 / 2005, dica para iniciantes.

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

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?