数据库 存储过程 进出货为例

create database 进出货存储过程
go
use 进出货存储过程
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')

--用户表
create table Login
(
    UserName varchar(50) primary key,    --用户名
    [Name] varchar(50),    --真实姓名
    Password varchar(50),    --密码    
    Account decimal(18,2)    --账户余额
)
go
insert into login values('zhangsan','张三','666666',50)
insert into login values('lisi','李四','666666',50)
insert into login values('wangwu','王五','666666',50)

--订单表
create table Orders
(
    Code varchar(50) primary key,    --以“用户名yyyyMMddhhmmssms”的形式作主键
    UserName varchar(50) references Login(Username),    --订购人员代号
    OrderTime datetime,    --订购时间
)
go

--订单内容表
create table OrderDetails
(
    Ids int identity primary key,
    OrderCode varchar(50) references Orders(Code),--订单号
    FruitCode varchar(50) references Fruit(Ids),--水果代号
    [Count] int ,    --水果的个数
)
go

select * from fruit
select * from login
select * from orders
select * from orderdetails


--进销存存储过程
alter proc Jinchuhuo --修改
@ids varchar(50),
@name varchar(50),
@price decimal(8,2),
@course 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 --后面那个numbers是原来的数量
            return 2 -- 加库存成功
          end
        else
          begin
            insert into Fruit values(@ids,@name,@price,@course,@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 -1 --有货但是不足
              end    
          end
        else--没有要出货的产品，直接返回0
          begin
            return 0--
          end     
     end
go


declare @a int 
exec @a= Jinchuhuo 'k001','苹果',2.4,'烟台','3',-9,''
select *from Fruit


declare @a int 
exec @a= Jinchuhuo 'k008','芒果',2.4,'海南','2',102,'image/8.gif'
select *from Fruit