【数据库学习日记】——使用游标和循环语句创建存储过程

题目：请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出，输出格式如下。

===================客户订单表====================                                                   

---------------------------------------------------                                                 

客户名称： 统一股份有限公司                                                                   

客户地址： 天津市                                                                             

总金额： 38683.80                                                                             

---------------------------------------------------                                                 

商品编号 总数量 平均价格                                                             

P20150001 3 2798.60                                                          

P20150003       5 2599.00                                                          

报表制作人 陈辉 制作日期 03 19 2017

CREATE PROCEDURE proSearchCustomer (@cusNo char(10))
AS
	DECLARE @cusName char(40),@address char(20),@total numeric(9,2)
	DECLARE @text char(100)
	DECLARE sear_cur SCROLL CURSOR FOR
		SELECT a.customerName,a.address,SUM(b.orderSum)
		FROM Customer a,OrderMaster b
		WHERE a.customerNo = b.customerNo and a.customerNo = 'C20150001'
		GROUP BY a.customerName,a.address
		PRINT'===================客户订单表==================== '
		PRINT'--------------------------------------------------- '
	OPEN sear_cur
	FETCH sear_cur INTO @cusName,@address,@total
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		SELECT @text = '客户名称：'+'		'+@cusName
		PRINT @text
		SELECT @text = '客户地址：'+'		'+@address
		PRINT @text
		SELECT @text = '总金额：'+'		'+convert(char(12),@total)
		PRINT @text
		FETCH sear_cur INTO @cusName,@address,@total
	END
		PRINT '---------------------------------------------------'
	CLOSE sear_cur
	DEALLOCATE sear_cur
	DECLARE @productNo char(10),@num int,@avg numeric(9,2)
	DECLARE searpro_cur SCROLL CURSOR FOR
		SELECT productNo,SUM(quantity),AVG(price)
		FROM OrderMaster a,OrderDetail b
		WHERE a.orderNo = b.orderNo and a.customerNo = 'C20150001'
		GROUP BY productNo
		SELECT @text = '商品编号		总数量		平均价格'
		PRINT @text
	OPEN searpro_cur
	FETCH searpro_cur INTO @productNo,@num,@avg
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		SELECT @text = @productNo+'		'+convert(char(8),@num)+'			'+convert(char(12),@avg)
		PRINT @text
		FETCH searpro_cur INTO @productNo,@num,@avg
	END
	PRINT '报表制作人	陈辉	制作日期	03 19 2017 '
	CLOSE searpro_cur
	DEALLOCATE searpro_cur
/*执行存储过程*/
exec proSearchCustomer C20050001