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