存储过程.sql

小型超市进销存系统

---TB050272 王继州           综合设计储存过程、触发器

use supermarket

------------===========自定义视图===========---------------
--查询供货商对应自己的供货商品视图

go
create view wjz_progood
as
select tord.providerid ,t.providername,tor.goodsid,tb.upc
from t_orderdetail tor,t_order tord,t_provider t,t_goods tb
where tor.orderid=tord.orderid and tord.providerid=t.providerid
and tor.goodsid=tb.goodsid
go
--待退货商品和对应的供货商的视图
create view wjz_proback
as
select w.providerid,t.goodsid,t.goodsname,tb.upc,t.goodsnum,t.typename,t.brand,t.spec
from wjz_progood w,t_waitgoods t,t_goods tb
where w.goodsid=t.goodsid and t.goodsid=tb.goodsid
go


-----------========自动生成ID号的函数=========-------------

create proc proid
@tablename  varchar(20),@columnname varchar(20),@stra varchar(20 )='noid' output
as
declare @strb varchar(2),@strc varchar(20),@num int
if exists(select * from sysobjects where name='a' and type='u')
drop table a
execute('select top 1 ' + @columnname + ' into a from ' + @tablename + ' order by '+ @columnname + ' desc')
set @stra=(select * from a)
set @num=len(@stra)
set @strb=left(@stra,1)
set @strc=right(@stra,len(@stra)-1)
set @strc=cast(@strc as int)+1
set @strc=replace(space(@num-1-len(cast(@strc as char)))+@strc,' ','0')
set @stra=@strb + @strc
go
----------退货详情表中添加数据-------
create procedure wjz_bgdadd
@backid varchar(8),@goodid varchar(8),@goodname varchar(20),@upc varchar(15),@num int
as
declare @price1 money,@price money
select @price1=inprice from t_price where goodsid=@goodid
set @price=@price1*@num
insert into t_bgooddetail values(@backid,@goodid,@goodname,@upc,@num,@price)
go
-----------退货单中添加内容-----------
create procedure wjz_bgtadd
@backid varchar(8),@prid varchar(5),@employid varchar(8)
as 
declare @tstart datetime,@endtime datetime,@m money
set @tstart=getdate()
set @endtime=dateadd(day,7,@tstart)
select @m=sum(inprice) from t_bgooddetail where backid=@backid
insert into t_bgoodtable(backid,providerid,stime,protime,allmoney,employeeid) values(@backid,@prid,@tstart,@endtime,@m,@employid)
go


---------======触发器=======-------------
---
create trigger wjz_epladd
on t_employee for insert
as 
declare @eid varchar(5)
select @eid=employeeid from inserted
insert into t_purview values(@eid,1,'123')
go


----退货单表的状态更新的时候,待退货商品的数量改变
create trigger wjz_bwch
on t_bgoodtable for update
as
declare @bid varchar(8),@goodid varchar(8),@num int,@num2 int,@status varchar(4)
select @bid=backid ,@status=state from inserted
if @status='确认'
begin
declare p cursor forward_only 
for select goodsid,num from t_bgooddetail where backid=@bid
open p
fetch next from p into @goodid,@num
while @@fetch_status=0
begin
	select @num2=goodsnum from t_waitgoods where goodsid=@goodid
	if @num=@num2--如果待退货商品的数量为0则直接从表中删除
		begin 
		delete t_waitgoods where goodsid=@goodid
		end 
	else
		begin
		update t_waitgoods set goodsnum=@num2-@num where goodsid=@goodid
		end
fetch next from p into @goodid,@num
end
close p
deallocate p
end

go
---根据入库商品的价格调整零售的价格
create trigger wjz_price
on t_order for update
as
declare @orderid varchar(8),@goodid varchar(8),@num float,@price money,@price1 decimal(8,1),@num2 int,
@goodname varchar(20),@spec varchar(10),@brand varchar(20),@type varchar(20),@status varchar(4),@num3 int,@allmoney money
select @orderid=orderid,@status=orderstate from inserted
if @status='确认'
begin
select @allmoney=sum(num*inprice) from t_orderdetail where orderid=@orderid
update t_order set allmoney=@allmoney where orderid=@orderid
declare p cursor forward_only
for select goodsid,inprice from t_orderdetail where orderid=@orderid
open p
fetch next from p into @goodid,@price
while @@fetch_status=0
begin 
	select @num2=num from t_orderdetail where goodsid=@goodid
	select @num3=store from t_goods where goodsid=@goodid
	set @num3=@num3+@num2
	update t_goods set store=@num3 where goodsid=@goodid
	if not exists(select * from t_price where goodsid=@goodid)
		begin
		select @goodname=goodsname,@spec=spec,@brand=brand,@type=typename from t_goods where goodsid=@goodid
		insert into t_price(goodsid,goodsname,spec,brand,typename) values(@goodid,@goodname,@spec,@brand,@type)
		end
	select @num=rate from t_price where goodsid=@goodid
	set @price1=@num*@price
	update t_price set inprice=@price,outprice=@price1 where goodsid=@goodid
	fetch next from p into @goodid,@price
end
close p
deallocate p
end
go
--------------检查待退货商品
create trigger wjz_qback
on t_qback for update
as
declare @bid varchar(8),@goodid varchar(8),@goodname varchar(20),@num int,
@brand varchar(20),@type varchar(20),@spec varchar(10),@num2 int
select @bid=qbackid from inserted
declare p cursor forward_only
for select goodsid,goodsname,num from t_qbackdetail where qbackid=@bid
open p
fetch next from p into @goodid,@goodname,@num
while @@fetch_status=0
begin
	select @type=typename,@brand=brand,@spec=spec from t_goods where goodsid=@goodid
	if exists(select * from t_waitgoods where goodsid=@goodid)
		begin
		select @num2=goodsnum from t_waitgoods where goodsid=@goodid
		set @num2=@num2+@num
		update t_waitgoods set goodsnum=@num2 where goodsid=@goodid
		end
	else
		begin
		insert into t_waitgoods values(@goodid,@goodname,@num,@type,@brand,@spec)
		end
	fetch next from p into @goodid,@goodname,@num
end
close p
deallocate p

---杨峙凌---存储过程
--删除流水号时同时修改卖场相应商品的库存
if exists(select * from sysobjects where name='proYZLaaa' and type='p')
drop procedure proYZLaaa
go
create procedure proYZLaaa @saleid int
as
declare a cursor for 
select goodsid,num from t_sdetail where saleid=@saleid
declare @goodsid varchar(8),@num int
open a
fetch next from a into @goodsid,@num
while @@fetch_status=0
begin
update t_market set num=num+@num where goodsid=@goodsid
fetch next from a into @goodsid,@num
end
close a
deallocate a
go
--张超--存储过程
--当修改订单状态为“已提”时自动减少相应库存数量
create proc zc_aaa @corderid int 
as
declare zc_bbb cursor for
select goodsid,num from t_corderdetail where corderid=@corderid
declare @goodsid varchar(8),@num int
open zc_bbb
fetch next from zc_bbb into @goodsid,@num
while @@fetch_status=0
begin
update t_goods set store=store-@num where goodsid=@goodsid
fetch next from zc_bbb into @goodsid,@num
end
close zc_bbb
deallocate zc_bbb