【数据库学习日记】——创建存储过程

1.利用游标嵌套和循环语句创建存储过程Proinvoice，要求输出每个订单的发票信息，包括：客户名称、订单日期、发票号码、业务员名称、订单总金额及订单明细信息等，格式如下表所示。

解：一个公司有多个订单，所以要定义一个订单游标order_cur

一个订单中有多种商品，所以要在order_cur中嵌套定义一个product_cur.

CREATE PROCEDURE Proinvoice
AS
	DECLARE @cusName char(40),@orderDate datetime,@invoiceNo char(10)
	DECLARE @proName varchar(40),@quantity int,@price numeric(7,2),@total numeric(9,2)
	DECLARE @sum int,@sumQuantity int,@sumPrice numeric(9,2)
	DECLARE @text char(100)
	select @sum = 0,@sumQuantity = 0,@sumPrice = 0.00
	DECLARE order_cur SCROLL CURSOR FOR
		SELECT a.customerName,orderDate,invoiceNo
		FROM Customer a,OrderMaster b
		WHERE a.customerNo = b.customerNo and a.customerNo = 'C20170003'
		PRINT'==============================================通用机打发票=================================================== '
		PRINT'--------------------------------------------------------------------------------------------------------------'
	OPEN order_cur
	FETCH order_cur INTO @cusName,@orderDate,@invoiceNo
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		SELECT @text = '客户名称：'+'	'+@cusName+'	'+'订购日期：'+'  '+convert(varchar(40),@orderDate)+'	'+'发票号码：'+'	'+@invoiceNo
		PRINT @text
		PRINT'--------------------------------------------------------------------------------------------------------------'
		DECLARE product_cur SCROLL CURSOR FOR
			select a.productName,quantity,price,SUM(quantity*price)
			from Product a,OrderDetail b,OrderMaster c
			where a.productNo = b.productNo and b.orderNo = c.orderNo and c.invoiceNo = @invoiceNo
			group by a.productName,quantity,price
			select @text = '商品名称						数量				单价					金额'
			print @text
		OPEN product_cur
		FETCH product_cur INTO @proName,@quantity,@price,@total
		WHILE(@@FETCH_STATUS = 0)
		BEGIN
			SELECT @text = @proName+'			'+convert(varchar(40),@quantity)+'			'+convert(varchar(20),@price)+'					'+convert(varchar(20),@total)
			PRINT @text
			select @sum = @sum+1,@sumQuantity = @sumQuantity+@quantity,@sumPrice = @sumPrice+@total		
			FETCH product_cur INTO @proName,@quantity,@price,@total
		END		
		PRINT'--------------------------------------------------------------------------------------------------------------'
		select @text = '商品类数：'+'		'+convert(varchar(12),@sum)+'		'+'商品数量'+'		'+convert(varchar(12),@sumQuantity)+'		'+'合计：'+'		'+convert(varchar(20),@sumPrice)
		print @text	
		CLOSE product_cur
		DEALLOCATE product_cur
		FETCH order_cur INTO @cusName,@orderDate,@invoiceNo
	END
	PRINT'--------------------------------------------------------------------------------------------------------------'
	PRINT '报表制作人:	张良																	 制作日期：	2017.03.23 '
	PRINT'--------------------------------------------------------------------------------------------------------------'
	CLOSE order_cur
		DEALLOCATE order_cur
/*执行存储过程*/
exec Proinvoice

注：创建存储过程的SQL语句和执行存储过程的SQL语句要分开执行，如果放在一起执行，会导致没有结果出现。

出现的问题：

刚开始定义的变量orderDate等不是char型或者varchar型，所以在赋值时需要使用convert（）函数实现转换，否则最后没有输出结果。

结果：

 格式的问题暂时还没有找到方法解决，不过不影响结果。

2.创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：

=======大客户中热销的前三种商品的销售信息================

商品编号 商品名称 总销售金额

P20150003 三星 Galaxy A9 31188.00

P20150001 vivo X9 30779.80

P20160002 酷睿四核i5-6500 11992.00

CREATE PROCEDURE productPur
AS
	DECLARE @productNo char(9),@productName char(20),@totalQuantity int
	DECLARE @text char(100)
	DECLARE procur SCROLL CURSOR FOR
		SELECT top 3 a.productNo,productName,SUM(quantity*price)
		FROM Product a,OrderDetail b,OrderMaster c
		WHERE a.productNo = b.productNo and b.orderNo=c.orderNo and c.customerNo in(SELECT top 5 d.customerNo
																					FROM OrderMaster d,OrderDetail e
																					WHERE d.orderNo = e.orderNo
																					GROUP BY d.customerNo
																					ORDER BY SUM(e.quantity*e.price) desc)

		GROUP BY a.productNo,productName
		ORDER BY SUM(b.quantity*b.price) desc
		PRINT '=======大客户中热销的前三种商品的销售信息================'
		PRINT '商品编号		商品名称				总销售金额'
		OPEN procur
		FETCH procur INTO @productNo,@productName,@totalQuantity
		WHILE(@@FETCH_STATUS = 0)
		BEGIN
			SELECT @text=@productNo+'  '+@productName+'  '+convert(char(10),@totalQuantity)
			PRINT @text
			FETCH procur INTO @productNo,@productName,@totalQuantity
		END
		CLOSE procur
		DEALLOCATE procur
/*执行存储过程*/
exec productPur

如果有发现什么不对的地方，欢迎指正~~