水果进出存储

create database fruit
go
use fruit
go
create table Fruit
(
	Ids varchar(50) primary key,
	[Name] varchar(50) not null,
	Price decimal(8,2),
	Source varchar(50),
	Stack varchar(50),
	Numbers int,	--库存数量
	[Image] varchar(50)
)
go
insert into fruit values('k001','苹果',2.4,'烟台','2',100,'image/0.gif')
insert into fruit values('k002','菠萝',1.4,'广东','3',100,'image/1.gif')
insert into fruit values('k003','桔子',2.4,'福州','3',100,'image/2.gif')
insert into fruit values('k004','葡萄',2.4,'新缰','2',100,'image/3.gif')
insert into fruit values('k005','樱桃',2.4,'青岛','4',100,'image/4.gif')
insert into fruit values('k006','桃子',2.4,'花果山','5',100,'image/5.gif')
insert into fruit values('k007','香蕉',2.4,'济南','5',100,'image/6.gif')

select * from fruit
--进销存存储过程
 alter proc Jinchuhuo
@ids varchar(50),
@name varchar(50),
@price decimal(8,2),
@source varchar(50),
@stack varchar(50),
@numbers int,--输入正数代表进货，输入负数代表出货
@image varchar(50)
as
	if @numbers>0                --代表进货
	begin 
		--先查询是否有这种货
		declare @shiok int 
		select @shiok=COUNT(*) from Fruit where Ids = @ids
		if @shiok>0           --有这种货，那就直接改库存
		begin
			update Fruit set Numbers=Numbers+@numbers where Ids=@ids
			return 2              -- 加库存成功
		end
		else                 --没有这种货，新增到数据库表中
		begin
			insert into Fruit values(@ids,@name,@price,@source,@stack,@numbers,@image)
			return 3              --新增数据成功
		end
	end 
	else    --出货
	begin  
	    --先查询是否有要出的货
		declare @isok int         --定义变量用来接收输入编号查询到的信息数量
		select @isok=COUNT(*) from Fruit where Ids = @ids
		if @isok>0         --说明有我们要出货的产品
		begin
			declare @kcnum int      --用来接收库存数量
			select @kcnum=numbers from Fruit where Ids=@ids
			if @kcnum>=abs(@numbers)         -- 够出货的,要修改数据库
			begin
				update Fruit set Numbers=@kcnum+@numbers where Ids=@ids -- 修改库存
				return 1        -- 出货成功
			end
			else
			begin
				return -2          --有货但不足
			end
		end
		else           --没有要出货的产品，直接返回-1
		begin
			return -1          -- 没有这个货
		end
	end
go

declare @a int 
exec @a = jinchuhuo 'k008','玉米',99,'沂源',1,100,'image/4.gif'
print @a
select *from fruit

 

转载于:https://www.cnblogs.com/Mr-xue/p/4461122.html