笔记三：测试部分编码

利用超市管理数据库的商品表，编程实现：如果商品表中啤酒类平均售价低于10，则将所有啤酒的售价增加10%，直到平均售价达到10为止

use supermarket
WHILE (SELECT AVG(SalePrice) FROM Goods) < 10
BEGIN 
	UPDATE Goods SET SalePrice = SalePrice*1.1
	IF (SELECT AVG(SalePrice) FROM Goods) >= 10
	BREAK
END

创建一个函数fun_avgallgoodsale,求超市管理数据库中所有商品的平均售价

CREATE FUNCTION fun_avgallgoodsale()
RETURNS decimal(18,2)
AS 
	BEGIN
		DECLARE @name varchar(100),@avg_price decimal(18,2)
		SELECT @name = (SELECT GoodsName) FROM Goods
		SELECT @avg_price = (SELECT AVG(SalePrice)) FROM Goods
		RETURN @name
		RETURN @avg_price
	END

创建一个多语句表值函数fun_avggoodsale,求超市管理数据库各类商品的平均售价

USE supermarket
GO
CREATE FUNCTION fun_avggoodsale() 
RETURNS @avg_salePrice TABLE
	(
		good_category varchar(100),
		good_saleprice decimal(18,2)
	)
AS 
BEGIN
	INSERT INTO @avg_salePrice
	SELECT CategoryNO,AVG(SalePrice)
	FROM Goods
	GROUP BY CategoryNO
	RETURN
END

创建一个存储过程proc_avgnumsale,显示指定商品类别的平均数量和平均售价

USE supermarket
GO
CREATE PROCEDURE proc_avgnumsale;1
	@category varchar(100)
AS
SELECT AVG(Number) 平均数量,AVG(SalePrice)
FROM Goods G JOIN Category CA ON G.CategoryNO = CA.CategoryNO
WHERE CategoryName = @category
GO

通过游标cur遍历商品表的数据,并将每个商品的售价增加10%

use supermarket
DECLARE cur CURSOR 
FOR SELECT SalePrice FROM Goods FOR UPDATE
DECLARE @saleprice decimal(18,2)
OPEN cur
FETCH NEXT FROM cur INTO @saleprice
WHILE @@fetch_status = 0
	BEGIN
		UPDATE Goods SET SalePrice = SalePrice * 1.1 WHERE CURRENT OF cur;
	END
CLOSE cur
DEALLOCATE cur