cursordemo8.sql

来自「T-SQL示例大全」· SQL 代码 · 共 66 行

/*
文件名称: CursorDemo8.sql
目        的: 以嵌套方式使用数据指针来产生复杂的报表
*/
USE 北风贸易
GO

SET NOCOUNT ON

-- 声明变量以便用来存放 FETCH 命令所返回的数据值
DECLARE @CustomerID nvarchar(5),
                   @CompanyName nvarchar(40),
                   @Contact nvarchar(30),
                   @Amount money

PRINT '-------------- 北京市客户订货报表 --------------'
PRINT REPLICATE('-',60)


DECLARE customer_cursor CURSOR FAST_FORWARD FOR
     SELECT 客户编号,公司名称,联系人 FROM 客户
         WHERE 地址 LIKE '%北京市%'
         ORDER BY 客户编号

OPEN customer_cursor

FETCH NEXT FROM customer_cursor 
    INTO @CustomerID, @CompanyName, @Contact

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT ' '
   PRINT '客户基本数据: ' + ' ' + @CompanyName + ' ' + @Contact

   -- 根据外部数据指针的 CustomerID 来声明一个内部数据指针
   DECLARE order_cursor CURSOR FAST_FORWARD FOR 
        SELECT 总金额 = SUM(b.单价 * b.数量 * (1-b.折扣))
        FROM 订货主档 a INNER JOIN 订货明细 b ON a.订单号码 = b.订单号码
        WHERE a.客户编号 = @CustomerID   -- 来自外部数据指针的变量值

   OPEN order_cursor
   FETCH NEXT FROM order_cursor INTO @Amount

   WHILE @@FETCH_STATUS = 0
   BEGIN
         IF @Amount IS NULL
             PRINT '<<  此位客户没有下订单  >>' 
         ELSE
                 PRINT '订 货 总 金 额:   '  +  LTRIM(CONVERT(char(12),@Amount,2))
      PRINT REPLICATE('-',60)
      FETCH NEXT FROM order_cursor INTO @Amount
   END

   CLOSE order_cursor
   DEALLOCATE order_cursor
   
   -- 提取下一位客户
   FETCH NEXT FROM customer_cursor 
       INTO @CustomerID, @CompanyName, @Contact
END

CLOSE customer_cursor
DEALLOCATE customer_cursor
GO