SQLServer(嵌套遊標以生成複襍的報表)

-以下示例顯示了如何嵌套遊標以生成複襍的報告。聲明內部遊標
set no counton
DECLARE @ Vendor _ id int，@ vendor _ name nvarchar (50)，
@ message varchar(80)FOR each Vendor，@ product nvarchar(50)
PRINT '-Vendor Products Report-'
DECLARE Vendor _ CURSOR遊標FOR
SELECT VendorID，Name
FROM Purchasing。Vendor
WHERE preferred Vendor STATUS = 1
ORDER BY Vendor id
OPEN Vendor _ cursor
FETCH NEXT FROM Vendor _ cursor
INTO @ Vendor _ id，@ Vendor _ name
WHILE @ @ FETCH _ STATUS = 0
BEGIN
PRINT ' '
SELECT @ message = '-來自供應商的産品:'
@ Vendor _ name
PRINT @ message
-Declare
DECLARE product _ CURSOR CURSOR FOR
SELECT v . Name
FROM Purchasing。産品供應商pv，生産。産品v
其中pv。ProductID = v.ProductID和
pv。VendorID = @vendor_id -來自外部遊標的變量值
OPEN product _ cursor
FETCH NEXT FROM product _ cursor INTO @ product
IF @ @ FETCH _ STATUS 0
PRINT ' '
WHILE @ @ FETCH _ STATUS = 0
BEGIN
SELECT @ message = ' ' @ product
PRINT @ message/www .exam da . com
FETCH NEXT FROM product _ cursor INTO @ product
END
CLOSE product _ cursor
DEALLOCATE product _ cursor
-獲取下一個供應商。
FETCH NEXT FROM vendor _ cursor
INTO @ vendor _ id，@ vendor _ name
END
CLOSE vendor _ cursor
DEALLOCATE vendor _ cursor

位律師廻複