一个双重游标的练习

--create database myTest
USE myTest

--CREATE TABLE
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'Country') AND type = N'U')
CREATE TABLE Country(
	CountryID INT IDENTITY(1,1) PRIMARY KEY,
	CountryName NVARCHAR(255) NOT NULL
)
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'Product') AND type = N'U')
CREATE TABLE Product (
	ProjuctID int IDENTITY(1,1) PRIMARY KEY,
	ProjuctName NVARCHAR(255) NOT NULL,
	ProjuctModel  NVARCHAR(255) NULL
)
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'Country_Projuct') AND type = N'U')
CREATE TABLE Country_Projuct(
	ProjuctID int foreign key references Product(ProjuctID),
	CountryID int foreign key references Country(CountryID)
	PRIMARY KEY (ProjuctID,CountryID)
)


--INSERT DATA

INSERT INTO [dbo].[Country] (COUNTRYNAME)
SELECT 'CHINA' UNION ALL
SELECT 'USA' UNION ALL
SELECT 'CHINA TAIWAN' UNION ALL
SELECT 'CHINA HONGKONG' UNION ALL
SELECT 'JANPAN' UNION ALL
SELECT 'Finland'

INSERT INTO Product(PROJUCTNAME,PROJUCTMODEL)
SELECT 'NOKIA','N70' UNION ALL
SELECT 'NOKIA','N71' UNION ALL
SELECT 'NOKIA','N72' UNION ALL
SELECT 'NOKIA','N73' UNION ALL
SELECT 'NOKIA','N74' UNION ALL
SELECT 'Lenovo','P1' UNION ALL
SELECT 'Lenovo','P2' UNION ALL
SELECT 'Lenovo','P3' UNION ALL
SELECT 'Lenovo','P4' UNION ALL
SELECT 'MICROSOFT','WIN7' UNION ALL
SELECT 'MICROSOFT','XP' UNION ALL
SELECT 'MICROSOFT','2003' UNION ALL
SELECT 'Asus','p12345' UNION ALL
SELECT 'Asus','p22345' UNION ALL
SELECT 'Asus','p32345' UNION ALL
SELECT 'Asus','p42345'

INSERT INTO Country_Projuct
SELECT 1,6 UNION ALL
SELECT 2,6 UNION ALL
SELECT 3,6 UNION ALL
SELECT 4,6 UNION ALL
SELECT 5,6 UNION ALL
SELECT 6,1 UNION ALL
SELECT 7,1 UNION ALL
SELECT 8,1 UNION ALL
SELECT 9,1 UNION ALL
SELECT 10,2 UNION ALL
SELECT 11,2 UNION ALL
SELECT 12,2 UNION ALL
SELECT 13,3 UNION ALL
SELECT 14,3 UNION ALL
SELECT 15,3 UNION ALL
SELECT 16,3 

--BEGION CURSOR

DECLARE @cid int
DECLARE @cName nvarchar(200)
DECLARE  CountryCursor  cursor 
for select CountryID,countryname from country
open CountryCursor
fetch next from CountryCursor into @cid,@cName
while @@FETCH_STATUS = 0
	begin
		print 'Country:'+@cName+' Product：'
		print '-----------------------------------'
		declare @pname nvarchar(200)
		declare @pmodel nvarchar(200)
		declare @rowCount int = 0 -- count  total
		--second cursor
		declare projuctCursor cursor for 
		select Product.ProjuctName,ProjuctModel from Product,Country_Projuct where
		Country_Projuct.CountryID = @cid
		and Country_Projuct.ProjuctID = Product.ProjuctID
	    open projuctCursor
	    fetch next from projuctCursor into @pname,@pmodel
	    
	    while @@FETCH_STATUS = 0
		begin 
			set @rowCount = @rowCount + 1;
			print @pname+' '+@pmodel;
			fetch next from projuctCursor into @pname,@pmodel
		end 
		if @rowCount = 0
		print 'no data'
		print '-----------------------------------' 
	    close projuctCursor
	    deallocate projuctCursor
		fetch next from CountryCursor into @cid,@cName
	end 
close CountryCursor
deallocate CountryCursor


--DELETE  TABLE


IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COUNTRY_PROJUCT]') AND type = N'U') 
DROP TABLE COUNTRY_PROJUCT 
IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type = N'U')
DROP TABLE Product 
IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Country]') AND type = N'U')
DROP TABLE Country 


--drop database myTest

Result:

(6 row(s) affected)

(16 row(s) affected)

(16 row(s) affected)
Country:CHINA Product：
-----------------------------------
Lenovo P1
Lenovo P2
Lenovo P3
Lenovo P4
-----------------------------------
Country:USA Product：
-----------------------------------
MICROSOFT WIN7
MICROSOFT XP
MICROSOFT 2003
-----------------------------------
Country:CHINA TAIWAN Product：
-----------------------------------
Asus p12345
Asus p22345
Asus p32345
Asus p42345
-----------------------------------
Country:CHINA HONGKONG Product：
-----------------------------------
no data
-----------------------------------
Country:JANPAN Product：
-----------------------------------
no data
-----------------------------------
Country:Finland Product：
-----------------------------------
NOKIA N70
NOKIA N71
NOKIA N72
NOKIA N73
NOKIA N74
-----------------------------------