库存管理.sql

毕业设计作品--超市管理系统

use DB_SMS
go
-------------------------------------------------------------------------------------------------------------------
--1. proc_getStorageInfo
--功能：查询库存信息
--输入：商品名称，商品编号，商品分类，供应商
--输出：库存信息(TB_GOODSTYPE, TB_GOODS, TB_STOCK_GOODS, TB_STOCK_SHEET, TB_STORAGE, TB_EMPLOYEE, TB_PROVIDER)
-------------------------------------------------------------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_getStorageInfo')
	drop procedure proc_getStorageInfo
go
create procedure proc_getStorageInfo
	@gsName varchar(30) = '',		--商品名称
	@gsId varchar(17) = '',			--商品编号
	@gtName varchar(20)= '',		--商品分类	
	@proName varchar(30) = ''		--供货商名称
as
	if @gsName != ''
		--根据商品名称查询库存信息
		select gsName, stoCount, gtName, stoTime
		from TB_GOODS_TYPE as gt, TB_GOODS as gs, TB_STORAGE as sto
		where gt.gtId = gs.gtId and gs.gsId = sto.gsId and gsName like '%'+@gsName+'%'
	else if @gsId != ''
		--根据商品编号查询库存信息
		select gsName, stoCount, gtName, stoTime
		from TB_GOODS_TYPE as gt, TB_GOODS as gs, TB_STORAGE as sto
		where gt.gtId = gs.gtId and gs.gsId = sto.gsId and sto.gsId like '%'+@gsId+'%'
	else if @gtName != ''
		--根据商品类型查询库存信息
		select gsName, stoCount, gtName, stoTime
		from TB_GOODS_TYPE as gt, TB_GOODS as gs, TB_STORAGE as sto
		where gt.gtId = gs.gtId and gs.gsId = sto.gsId and gtName like '%'+@gtName+'%'
	else if @proName != ''
		--根据供货商名称查询库存信息
		select gsName, stoCount, gtName, stoTime
		from TB_GOODS_TYPE as gt, TB_GOODS as gs, TB_STOCK_GOODS as stg, 
			TB_STOCK_SHEET as sts, TB_STORAGE as sto, TB_PROVIDER as pro
		where gt.gtId = gs.gtId and gs.gsId = stg.gsId and gs.gsId = sto.gsId
			and sts.stsId = stg.stsId and  sts.proId = pro.proId and proName like '%'+@proName+'%'
	else 
		--查询所有库存信息
		select gsName, stoCount, gtName, stoTime
		from TB_GOODS_TYPE as gt, TB_GOODS as gs, TB_STORAGE as sto
		where gt.gtId = gs.gtId and gs.gsId = sto.gsId
go

-------------------------------------------------------------------------------------------------------------------
--2. proc_storageAlarm
--功能：库存预警(当库存中某个商品数量低于警戒线时，发出警告)
--输入：警戒线(此功能先做所有商品的警戒线一致的情况)
--输出： 数量不足的商品信息(商品名和当前数量)
-------------------------------------------------------------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_storegeAlarm')
	drop procedure proc_storegeAlarm
go
create procedure proc_storegeAlarm
	@alarmLine int		--警戒线
as
	select gsName, stoCount
	from TB_GOODS as gs, TB_STORAGE as sto
	where gs.gsId = sto.gsId and stoCount < @alarmLine
go

select * from tb_goods
select * from tb_storage