create database chaoshixiaopiao4gouse chaoshixiaopiao4go--创建供应商的表格create table gongying(gno   int primary key identity(1001,1) not null,--供应商编号gname varchar(40),              --供应商名称gdizhi varchar(40)              --供应商地址)--插入供应商内容insert into gongying values('可口可乐公司','美国')insert into gongying values('绿箭集团','天津')insert into gongying values('康师傅集团','北京')insert into gongying values('达利园集团','北京')insert into gongying values('创达公司','深圳')--创建超市货架表格create table huojia(hno    int primary key identity(10001,1) not null,--商品编号hname  varchar(20),                               --商品名称hjin    decimal(18,2),                            --商品进价                           hshou   decimal(18,2),                            --商品售价hshu   int,                                       --商品数量hgno   int,                                       --商品供应商编号)insert into huojia values('可口可乐',2.5,3,20,1001)insert into huojia values('零度',2,3,20,1001)insert into huojia values('绿箭口香糖',1,1.5,20,1002)insert into huojia values('康师傅方便面',3,3.5,20,1003)insert into huojia values('达利园小面包',5,5.5,20,1004)insert into huojia values('薯片',3,3.5,20,1005)--创建小票的表格create table xiaopiao(xno  int primary key identity(1,1),xhno  int,xname varchar(20),xhjia decimal(18,2),xshu  int,xzong decimal(18,2))create proc piao1@shumu int,@huohao intasbegin    declare     @count  int,    @huoshu int,    @sjia  decimal(18,2),    @he    decimal(18,2),    @huoname varchar(20),    @ci int      select @huoshu=hshu from huojia where hno=@huohao;    select @count=COUNT(*) from huojia where hno=@huohao;     if @count>0          begin           set @ci=@ci+1           if @shumu>=0              begin              update huojia set hshu=(@huoshu+@shumu) where hno=@huohao                            select @huoname=hname from huojia where hno=@huohao              select @sjia=hjin from huojia where hno=@huohao              set @he=@sjia*@shumu              insert into xiaopiao values(@huohao,@huoname,@sjia,@shumu,@he)              select @ci=COUNT(*) from xiaopiao              select xno as 小票号,xhno as 商品编号,xname as 商品名称,xhjia as 进价,xshu as 数量,xzong as 总价 from xiaopiao              where xno=@ci              end           else              begin              set @shumu = -@shumu              if  @shumu<=@huoshu                begin                update huojia set hshu=@huoshu-@shumu where hno=@huohao                                select @huoname=hname from huojia where hno=@huohao                select @sjia=hshou from huojia where hno=@huohao                set @he=@sjia*@shumu                insert into xiaopiao values(@huohao,@huoname,@sjia,@shumu,@he)                select @ci=COUNT(*) from xiaopiao                select xno as 小票号,xhno as 商品编号,xname as 商品名称,xhjia as 单价,xshu as 数量,xzong as 总价 from xiaopiao                where xno=@ci                end              else                begin                                select '此商品数量不足！'                select hno as 商品编号,hname as 商品名称,hshou as 单价,hshu as 数量 from huojia                 end              end       end    else       print '超市无此商品！'  endgoexec piao1  -2,10003exec piao1  -2,10002exec piao1  -2,10001select * from xiaopiao