declare@goods_typevarchar(20)set@goods_type='毛巾'select GoodsName,SalePrice from Goods
where CategoryNO in(select CategoryNO
from Category
where CategoryName like@goods_type)
go
declare@gnamevarchar(20),@numintset@gname='白酒'select@num=COUNT(*)from Goods
where CategoryNO in(select CategoryNO
from Category
where CategoryName like@gname)if@num>0select*from Goods where CategoryNO in(select CategoryNO from Category where CategoryName like@gname)elseprint'无此类商品'
go
declare@sint,@goods_typevarchar(100)set@goods_type='白酒'select@s=(selectcount(*)from goods, category
where goods.CategoryNO=Category.CategoryNO and CategoryName=@goods_type)if@s>0select*from goods, category
where goods.CategoryNO=Category.CategoryNO and CategoryName=@goods_typeelseprint'无此类商品'
go
while(selectMAX(Number)from Goods)>=10beginif(selectMIN(Number)from Goods)<10beginupdate Goods set Number = Number +10breakendend
go
whileexists(select*from goods where number<10)beginupdate goods set number=number+10end
go
6、声明一个游标,用于对“饼干”类商品的售价降价5%。
declare@gnamevarchar(20)declare@now_goods_idvarchar(20)declare@totalintset@gname='饼干'--声明游标declare sale_cur cursorforselect GoodsNO from Goods where CategoryNO in(select CategoryNO from Category where CategoryName like@gname)--给局部变量赋值select@total=COUNT(*)from Goods where CategoryNO in(select CategoryNO from Category where CategoryName like@gname)--打开游标open sale_cur
--使用游标while@total>0begin--游标下移fetch sale_cur into@now_goods_id--更新数据beginupdate Goods set SalePrice = SalePrice - SalePrice *0.05where GoodsNO like@now_goods_id--break 不可以设置break,否则只生效一条数据endset@total=@total-1end--关闭游标close sale_cur
--释放游标deallocate sale_cur
go
declare@goods_novarchar(20)declare cur_pie cursorforselect GoodsNO
from goods, category
where goods.CategoryNO=Category.CategoryNO and CategoryName='咖啡'forupdateof SalePrice
open cur_pie
fetchnextfrom cur_pie into@goods_nowhile @@FETCH_STATUS=0beginupdate goods set SalePrice=SalePrice*0.95where GoodsNO=@goods_nofetchnextfrom cur_pie into@goods_noendclose cur_pie
deallocate cur_pie
go
createfunction fun_saleinfo(@pre smalldatetime,@last smalldatetime)returnsintbeginreturn(selectSUM(Number)from SaleBill where HappenTime >=@preand HappenTime<=@last)end
go
declare@formerly smalldatetime,@latter smalldatetime
set@formerly='2018-04-01'set@latter='2018-05-01'select dbo.fun_saleinfo(@formerly,@latter)
go
createfunction salereport(@begindate smalldatetime,@enddate smalldatetime)returnsdecimal(18,2)asbegindeclare@sum_amountdecimal(18,2)select@sum_amount=(selectsum(saleprice*S.number)from salebill S,goods G
where S.GoodsNO=G.GoodsNO)return@sum_amountend
go
declare@begindate smalldatetime,@enddate smalldatetime
set@begindate='2018-7-1'set@enddate='2018-7-31'select dbo.salereport(@begindate,@enddate)as'2018年7月份销售金额'
go