4.27 数据库 水果进存消 函数

create database Fruit
create table fruit
(
Ids varchar(50),
Name varchar(50),
Price decimal(18,2),
Source varchar(50),
Stack int,
Numbers int,
Image varchar(50)
)
insert into fruit values('k001','苹果',2.4,' 烟台',2,91,'image/0.gf')
insert into fruit values('k002','菠萝',1.4,' 广东',3,74,'image/1.gf')
insert into fruit values('k004','葡萄',2.4,' 新疆',2,98,'image/3.gf')
insert into fruit values('k006','蟠桃',1.4,' 蟠桃园',6,3,'image/5.gf')
insert into fruit values('k007','香蕉',2.4,' 济南',5,100,'image/6.gf')
select*from fruit

--进销存存储过程
alter  proc  JinChuKu
@Ids varchar(50),
@Name varchar(50),
@Price decimal(18,2),
@Source varchar(50),
@Stack int,
@Numbers int,
@Image varchar(50)
as
    if @Numbers>0 --代表进货
    begin
    declare @shiok int --定义一个变量
    select @shiok=Numbers from fruit where Ids=@Ids  --用这一个变量来接收输入@Ids所对应的数量
        if @shiok>0 --如果有这种货，就直接修改库存
        begin
        update fruit set Numbers=@Numbers+Numbers where Ids=@Ids --修改数量以输入的@Ids所对应的那一行为准
        return 2--进货成功
        end
        else --没有这种货，添加这种货
        begin
        insert into fruit values(@Ids,@Name,@Price,@Source,@Stack,@Numbers,@image)
        return 1--'添加品种成功'
        end
    end
    else--代表出货
    begin
    declare @isok int
    select @isok=count(*) from fruit where Ids=@Ids
        if @isok>0 --有这种货，就出货
        begin
            if @isok>ABS(@Numbers) --库存的大于出货的
            begin
            update fruit set Numbers=@Numbers+Numbers where Ids=@Ids
            return 0--'出货成功'
            end
            else
            begin
            return -1--'有货，但不足'
            end
        end
        else --没有这种货
        begin
        return -2--'直接没有这种货'
        end
    end
go

declare @a int
exec @a=JinChuKu 'k001','苹果',2.4,'烟台',2,-10,'image/0.gf'
print @a
select *from fruit