游标例子

select * from dbo.Products

select * from dbo.Orders

select * from [Order Details]

select * from dbo.Categories Select b.ProductID,datepart(Year,a.OrderDate) as [Year],datepart(Month,a.OrderDate) as [Month],Sum(b.Quantity) as Quantity, c.ProductName,Count(a.OrderID) as Orders from Orders a Inner Join [Order Details] b On a.OrderID = b.OrderID Inner Join Products c On b.ProductID=c.ProductID Group By b.ProductID,a.OrderDate,c.ProductName Order by b.ProductID Asc,a.OrderDate Asc go

--第一题

Alter PROCEDURE USP_Product1 AS BEGIN DECLARE @ID int,@TempID int DECLARE @ProductName NVARCHAR(50) DECLARE @Year NVARCHAR(4) DECLARE @Month NVARCHAR(2) DECLARE @OrderCount int DECLARE @DetailsCount int DECLARE @TotalCount int DECLARE @TotalDetails int DECLARE Cursor_Product CURSOR LOCAL FOR

--STEP1 声明游标 Select b.ProductID,datepart(Year,a.OrderDate) as [Year],datepart(Month,a.OrderDate) as [Month],Sum(b.Quantity) as Quantity, c.ProductName,Count(a.OrderID) as Orders from Orders a Inner Join [Order Details] b On a.OrderID = b.OrderID Inner Join Products c On b.ProductID=c.ProductID Group By b.ProductID,a.OrderDate,c.ProductName Order by b.ProductID Asc,a.OrderDate Asc FOR READ ONLY

CREATE TABLE #ProductDetails (产品编号 INT ,产品名称 NVARCHAR(50),月份 NVARCHAR(10),订单总数 INT,出货总量 INT)

--创建临时表 set @TempID=null --判断是否为同一个商品 set @TotalCount = 0 set @TotalDetails = 0 OPEN Cursor_Product

--STEP2 打开游标 WHILE(1=1) BEGIN

--STEP3 从游标中提取行 FETCH NEXT FROM Cursor_Product INTO @ID,@Year,@Month,@DetailsCount,@ProductName,@OrderCount IF(@@FETCH_STATUS<>0)BREAK

--跳出循环 if @TempID is null

--判断是第一次的话为@TempID赋值 BEGIN set @TempID=@ID END if @TempID<>@ID

--判断是否需要插入小计 BEGIN insert into #ProductDetails Values(null,null,N'小计：',@TotalCount,@TotalDetails) set @TotalCount = 0 set @TotalDetails = 0 set @TempID=@ID Continue

--跳入下次循环 END set @TotalCount = @TotalCount+@OrderCount set @TotalDetails = @TotalDetails+@DetailsCount insert into #ProductDetails Values(@ID,@ProductName,Convert(Nvarchar(50),@Year)+N'年'+Convert(Nvarchar(50),@Month)+N'月',@OrderCount,@DetailsCount) END select * from #ProductDetails CLOSE Cursor_Product

--STEP4 关闭游标 DEALLOCATE Cursor_Product

 --STEP5 释放游标 END GO EXEC USP_Product1 -- DROP Proc USP_Product1 GO Select c.ProductName,Count(a.OrderID) as Orders,SUM(c.UnitPrice*b.Quantity) as Price from Orders a Inner Join [Order Details] b On a.OrderID = b.OrderID Inner Join Products c On b.ProductID=c.ProductID Where c.CategoryID=1 Group By c.ProductName Order by c.ProductName Asc Go

--第二题 Alter PROCEDURE USP_Product2 AS BEGIN DECLARE @ID int,@Temp int DECLARE @CategoryName NVARCHAR(50) DECLARE @ProductName NVARCHAR(50) DECLARE @POCount int DECLARE @POPrices money --小计 DECLARE @OrderCount int DECLARE @OrderPrices money --总计 DECLARE @Orders int DECLARE @Prices money

-- 声明游标1 DECLARE Cursor_Categories CURSOR LOCAL FOR select CategoryID,CategoryName from Categories FOR READ ONLY CREATE TABLE #ProductDetails (产品类别 INT ,类别名称 NVARCHAR(50),产品名称 NVARCHAR(50),下单总次数 INT,下单总价格 INT)

--创建临时表 --小计 SET @OrderCount = 0 SET @OrderPrices = 0

--总计 SET @Orders = 0 SET @Prices = 0 OPEN Cursor_Categories

-- 打开游标1 WHILE(1=1) BEGIN

-- 从游标1中提取行 FETCH NEXT FROM Cursor_Categories INTO @ID,@CategoryName IF(@@FETCH_STATUS<>0)BREAK

--跳出循环 DECLARE Cursor_Product CURSOR LOCAL FOR

-- 声明游标2 Select c.ProductName,Count(a.OrderID) as Orders,SUM(c.UnitPrice*b.Quantity) as Price from Orders a Inner Join [Order Details] b On a.OrderID = b.OrderID Inner Join Products c On b.ProductID=c.ProductID Where c.CategoryID=@ID Group By c.ProductName Order by c.ProductName Asc FOR READ ONLY Set @Temp = null OPEN Cursor_Product

-- 打开游标2 WHILE(1=1) BEGIN FETCH NEXT FROM Cursor_Product INTO @ProductName,@POCount,@POPrices IF(@@FETCH_STATUS<>0)BREAK

--跳出循环 if(@Temp is Null) -- 第一次进入 BEGIN SET @Temp=1 SET @OrderCount=@POCount SET @OrderPrices=@POPrices Insert Into #ProductDetails Values(@ID,@CategoryName,@ProductName,@POCount,@POPrices) END Else BEGIN SET @OrderCount= @OrderCount + @POCount SET @OrderPrices= @OrderPrices + @POPrices Insert Into #ProductDetails(产品名称,下单总次数,下单总价格) Values(@ProductName,@POCount,@POPrices) END END Insert Into #ProductDetails(产品名称,下单总次数,下单总价格) Values('小计：',@OrderCount,@OrderPrices)

--累加总计 SET @Orders = @Orders + @OrderCount SET @Prices = @Prices + @OrderPrices CLOSE Cursor_Product

-- 关闭游标2 DEALLOCATE Cursor_Product

-- 释放游标2 END Insert Into #ProductDetails(产品名称,下单总次数,下单总价格) Values('总计：',@Orders,@Prices) select * from #ProductDetails CLOSE Cursor_Categories

-- 关闭游标1 DEALLOCATE Cursor_Categories

-- 释放游标1 END GO EXEC USP_Product2 -- DROP PROC USP_Product2