1 ----------------------------------------- 2 --创建一个货物表：编号，货物名称，单位，价格，库存数量，备注。并输入条数据 3  4 drop table kucun 5 create table kucun 6 ( 7     code int primary key,--编号列为主键 8     name varchar(20), 9     danwei varchar(10),10     jiage money,11     shengyu int,12     beizhu varchar(50)13 )14 go15 select *from kucun16 insert into kucun values (1,‘笔记本‘,‘本‘,3,10,‘尚宇 A5办公胶订本‘)17 insert into kucun values (2,‘中性笔‘,‘支‘,1,20,‘白雪 0.5黑色中性笔‘)18 insert into kucun values (3,‘保温杯‘,‘个‘,15,8,‘中宏 300ML保温杯‘)19 insert into kucun values (4,‘台式电脑‘,‘台‘,2500,20,‘联想商务电脑‘)20 insert into kucun values (5,‘键鼠‘,‘套‘,30,15,‘双飞燕键鼠套装‘)21 insert into kucun values (6,‘笔记本电脑‘,‘台‘,4000,5,‘DELL Inspiron15 3000（戴尔灵越）‘)22 insert into kucun values (7,‘无线鼠标‘,‘个‘,80,7,‘双飞燕无线光学鼠标‘)23 insert into kucun values (8,‘优盘‘,‘个‘,70,10,‘Kingston 16G‘)24 insert into kucun values (9,‘无线路由器‘,‘个‘,80,6,‘TP-Link 150M 双天线无线路由器‘)25 insert into kucun values (10,‘插排‘,‘个‘,50,10,‘公牛插排3M‘)26 insert into kucun values (11,‘未知‘,‘个‘,10000,10,‘这你也敢买‘)27 update kucun set danwei=‘台‘ where code=628 ---------------------------------------------29 select *from kucun30 --入库存储过程31 --create proc jinchuProc32 alter proc jinchuProc33 @code int,34 @name varchar(20),35 @danwei varchar(10),36 @jiage decimal(18,2),37 @shengyu int,38 @beizhu varchar(100)39 as40 begin41     declare @count int42     select @count=count(*) from kucun where code=@code43     if @count=0 and @shengyu>0--仓库里没有的，新进来的货物44     begin45         insert into kucun values(@code,@name,@danwei,@jiage,@shengyu,@beizhu)46         return 147     end48     if @count=1 and @shengyu>0--仓库里已有的，进货，需要改变数量49     begin50         declare @ckshengyu int51         select @ckshengyu=shengyu from kucun where code=@code52         update kucun set shengyu=@shengyu+@ckshengyu where code=@code53         return 254     end55     if @shengyu<0 and @count=1--出货并且仓库里有这个货56     begin57         declare @kshengyu int58         select @kshengyu=shengyu from kucun where code=@code59         if @kshengyu>=(-@shengyu)60         begin61             update kucun set shengyu=@kshengyu+@shengyu where code=@code62             return 063         end64         else65         begin66             print ‘不够出货‘67             return -168         end69     end70     if @shengyu<0 and @count=071     begin72         print ‘没有这个货物‘73         return -274     end75 end76 declare @i int77 --exec @i=jinchuproc 3,‘保温杯‘,‘个‘,15,10,‘中宏 300ML保温杯‘78 --exec @i=jinchuproc 3,‘保温杯‘,‘个‘,15,-10,‘中宏 300ML保温杯‘79 --exec @i=jinchuproc 12,‘文件夹‘,‘个‘,5,10,‘得力牌文件夹‘80 --exec @i=jinchuproc 13,‘鼻烟壶‘,‘个‘,30,-11,‘华光陶瓷骨质瓷‘81 exec @i=jinchuproc 14,‘鼻烟壶‘,‘个‘,30,-11,‘华光陶瓷骨质瓷‘82 print @i83 select *from kucun