毕业设计超市系统（八）存储过程
百度一下：
存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
存储过程的缺点 1：调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2：移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3：重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
4： 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的。维护起来更加麻烦！
在我的毕业设计中用到的过程：
----------------------------
库存信息：
如图：
----------------
上面的剩余天数为计算列：
过程代码：
库存过程1
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--得到所有的库存商品信息
alter PROCEDURE [dbo].[GetAllStorageInfo]
AS
BEGIN
SET NOCOUNT ON;
select storage.auto_id,goodsdetail.goodsname,goodsdetail.types,
storage.count,stock.madedate,goodsdetail.shelflife,
datediff(day,getdate(),stock.madedate+goodsdetail.shelflife*30) as leftdays,
stock.suppname,storage.addrass,storage.info
from storage inner join stock on storage.stockid=stock.auto_id
inner join goodsdetail on storage.goodsid=goodsdetail.goodsid
END
--解释：datediff(day,getdate(),stock.madedate+goodsdetail.shelflife*30)
--day：表示以天数为单位
--getdate()：表示系统日期
--stock.madedate：表示采购表里的生产日期
--goodsdetail.shelflife*30：表示商品明细表里的保质期（月为单位）乘以30是把保质期转换为天数
--stock.madedate+goodsdetail.shelflife*30表示系统过期的日期
set QUOTED_IDENTIFIER ON
go
--得到所有的库存商品信息
alter PROCEDURE [dbo].[GetAllStorageInfo]
AS
BEGIN
SET NOCOUNT ON;
select storage.auto_id,goodsdetail.goodsname,goodsdetail.types,
storage.count,stock.madedate,goodsdetail.shelflife,
datediff(day,getdate(),stock.madedate+goodsdetail.shelflife*30) as leftdays,
stock.suppname,storage.addrass,storage.info
from storage inner join stock on storage.stockid=stock.auto_id
inner join goodsdetail on storage.goodsid=goodsdetail.goodsid
END
--解释：datediff(day,getdate(),stock.madedate+goodsdetail.shelflife*30)
--day：表示以天数为单位
--getdate()：表示系统日期
--stock.madedate：表示采购表里的生产日期
--goodsdetail.shelflife*30：表示商品明细表里的保质期（月为单位）乘以30是把保质期转换为天数
--stock.madedate+goodsdetail.shelflife*30表示系统过期的日期
--------------------------------
传参查询过程：
库存过程2
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[FilterStorage]
@types nvarchar(20),--商品类型
@suppname nvarchar(20),--供应商的名字
@info nvarchar(20)--其他信息
AS
BEGIN
SET NOCOUNT ON;
select storage.auto_id,goodsdetail.goodsname,goodsdetail.types,
storage.count,stock.madedate,goodsdetail.shelflife,
datediff(day,getdate(),stock.madedate+goodsdetail.shelflife*30) as leftdays,
stock.suppname,storage.addrass,storage.info
from storage inner join stock on storage.stockid=stock.auto_id
inner join goodsdetail on storage.goodsid=goodsdetail.goodsid
where (goodsdetail.types like '%'+ @types+'%' and stock.suppname like '%' + @suppname + '%')
and( goodsdetail.goodsname like '%'+@info+'%'
or storage.addrass like '%'+@info+'%'
or storage.info like '%'+@info+'%')
END
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[FilterStorage]
@types nvarchar(20),--商品类型
@suppname nvarchar(20),--供应商的名字
@info nvarchar(20)--其他信息
AS
BEGIN
SET NOCOUNT ON;
select storage.auto_id,goodsdetail.goodsname,goodsdetail.types,
storage.count,stock.madedate,goodsdetail.shelflife,
datediff(day,getdate(),stock.madedate+goodsdetail.shelflife*30) as leftdays,
stock.suppname,storage.addrass,storage.info
from storage inner join stock on storage.stockid=stock.auto_id
inner join goodsdetail on storage.goodsid=goodsdetail.goodsid
where (goodsdetail.types like '%'+ @types+'%' and stock.suppname like '%' + @suppname + '%')
and( goodsdetail.goodsname like '%'+@info+'%'
or storage.addrass like '%'+@info+'%'
or storage.info like '%'+@info+'%')
END
------------------------------------------------
在添加商品的时候会出现重复主键的异常：
可以在过程里面对这些异常进行预知和抛出更友好的异常
如添加商品模块：
运行界面：
--------------------------------------
添加商品的过程代码:
添加商品明细信息
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
alter PROCEDURE [dbo].[AddGoodsDetail]
@goodsid nvarchar(20),
@goodsname nvarchar(20),
@types nvarchar(20),
@price decimal(8,2),
@shelflife int,
@madein nvarchar(20),--
@company nvarchar(20),--出品公司
@info nvarchar(20)
AS
declare @exists int
BEGIN
SET NOCOUNT ON;
select @exists=count(*) from goodsdetail where goodsid=@goodsid or goodsname=@goodsname
if @exists>0
begin
raiserror ('商品[%s]已经存在!',16,1,@goodsname)--这个异常会被调用程序捕捉到.
return
end
insert into goodsdetail
values (@goodsid,@goodsname,@types,@price,@shelflife,@madein,@company,@info)
return @@error--返回错误信息的代码,如果是返回0表示没有错误出现,也就是操作成功
END
set QUOTED_IDENTIFIER ON
go
alter PROCEDURE [dbo].[AddGoodsDetail]
@goodsid nvarchar(20),
@goodsname nvarchar(20),
@types nvarchar(20),
@price decimal(8,2),
@shelflife int,
@madein nvarchar(20),--
@company nvarchar(20),--出品公司
@info nvarchar(20)
AS
declare @exists int
BEGIN
SET NOCOUNT ON;
select @exists=count(*) from goodsdetail where goodsid=@goodsid or goodsname=@goodsname
if @exists>0
begin
raiserror ('商品[%s]已经存在!',16,1,@goodsname)--这个异常会被调用程序捕捉到.
return
end
insert into goodsdetail
values (@goodsid,@goodsname,@types,@price,@shelflife,@madein,@company,@info)
return @@error--返回错误信息的代码,如果是返回0表示没有错误出现,也就是操作成功
END
在我的毕业设计里，凡是涉及到对数据更新、删除、添加的时候都会写这样的过程。最后都返回一个@@error.
------------------------------------------------
导出销售报表的过程：
代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[FilterSaledInfoInDate]
@year int,
@month int,
@day int
AS
BEGIN
SET NOCOUNT ON;
--日不为0表示统计一天的销售报表
if @day<>'0'
begin
select goodsname as 商品名字,sum(total) as 收入,saleprice as 销售价格,
sum(count) as 数量,types as 商品类型,day(shipdate) as DATE
from saled
where year(shipdate)=@year and month(shipdate)=@month and day(shipdate)=@day
group by goodsname,day(shipdate),saleprice,types
return
end
--月不为0表示统计某月的销售情况
if @month<>'0'
begin
select goodsname as 商品名字,sum(total) as 收入,saleprice as 销售价格,
sum(count) as 数量,types as 商品类型,month(shipdate) as DATE
from saled where year(shipdate)=@year and month(shipdate)=@month
group by month(shipdate),goodsname,saleprice,types
order by month(shipdate) asc
return
end
--表示统计年度的销售情况
select goodsname as 商品名字,sum(total) as 收入,saleprice as 销售价格,
sum(count) as 数量,types as 商品类型,year(shipdate) as DATE
from saled
where year(shipdate)=@year
group by year(shipdate),goodsname,saleprice,types
order by year(shipdate) asc
END
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[FilterSaledInfoInDate]
@year int,
@month int,
@day int
AS
BEGIN
SET NOCOUNT ON;
--日不为0表示统计一天的销售报表
if @day<>'0'
begin
select goodsname as 商品名字,sum(total) as 收入,saleprice as 销售价格,
sum(count) as 数量,types as 商品类型,day(shipdate) as DATE
from saled
where year(shipdate)=@year and month(shipdate)=@month and day(shipdate)=@day
group by goodsname,day(shipdate),saleprice,types
return
end
--月不为0表示统计某月的销售情况
if @month<>'0'
begin
select goodsname as 商品名字,sum(total) as 收入,saleprice as 销售价格,
sum(count) as 数量,types as 商品类型,month(shipdate) as DATE
from saled where year(shipdate)=@year and month(shipdate)=@month
group by month(shipdate),goodsname,saleprice,types
order by month(shipdate) asc
return
end
--表示统计年度的销售情况
select goodsname as 商品名字,sum(total) as 收入,saleprice as 销售价格,
sum(count) as 数量,types as 商品类型,year(shipdate) as DATE
from saled
where year(shipdate)=@year
group by year(shipdate),goodsname,saleprice,types
order by year(shipdate) asc
END
--------------------------------------
下面是系统中我感觉最复杂的一个过程了，商品销售。
这个过程因为涉及到促销更变得复杂，要考虑到商品的采购，库存，货架。尤其是多个商品捆绑促销时。
下面是过程的代码：
销售过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UpdateStorageForSale]
@goodsname nvarchar(20),--销售商品的名字
@count int--商品数量
AS
declare @exists int,@goodsid nvarchar(20),@warn as int
begin
SET NOCOUNT ON;
set @exists=0
select @exists=count(onsalename) from onsale where onsalename=@goodsname
if @exists='0'--不是促销商品
begin
select @exists=count(goodsname) from goodsdetail where goodsname=@goodsname
--找不到商品信息
if @exists='0'
begin
raiserror('没有找到商品[%s]的信息！',16,1,@goodsname)
return
end
--找到商品信息,可能有不同批次
select @goodsid=goodsid from goodsdetail where goodsname=@goodsname
--最早采购的那一批商品更新库存
update storage set count=count-@count where goodsid=@goodsid and
stockid<=(select min(auto_id) from stock where goodsid=@goodsid)
--更新货架数据
update shelf set remain=remain-@count where goodsid=@goodsid
--是不是已经到了提示状态
select @warn=remain-min from shelf where goodsid=@goodsid
if @warn <='0'--到了最小在架量
begin
raiserror('商品[%s]的在架量已经过限,请及时调拨！',16,1,@goodsname)
return
end
return @@error
end
--是促销商品,说明这个商品在库存中剩的很少了
--更新库存
update storage set count=count-@count*
(select goodscount from onsale where goodsid=storage.goodsid and onsalename=@goodsname)
where goodsid in
(select goodsid from onsale where onsalename=@goodsname)
--更新货架
update shelf set remain=remain-@count where goodsid=@goodsid and partionname like '%促销%'
select @warn=remain-min from shelf where goodsid=@goodsid and partionname like '%促销%'
--是不是已经到了提示状态
if @warn <='0'
begin
raiserror('商品[%s]的在架量已经过限,请及时调拨！',16,1,@goodsname)
return
end
return @@error
end
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UpdateStorageForSale]
@goodsname nvarchar(20),--销售商品的名字
@count int--商品数量
AS
declare @exists int,@goodsid nvarchar(20),@warn as int
begin
SET NOCOUNT ON;
set @exists=0
select @exists=count(onsalename) from onsale where onsalename=@goodsname
if @exists='0'--不是促销商品
begin
select @exists=count(goodsname) from goodsdetail where goodsname=@goodsname
--找不到商品信息
if @exists='0'
begin
raiserror('没有找到商品[%s]的信息！',16,1,@goodsname)
return
end
--找到商品信息,可能有不同批次
select @goodsid=goodsid from goodsdetail where goodsname=@goodsname
--最早采购的那一批商品更新库存
update storage set count=count-@count where goodsid=@goodsid and
stockid<=(select min(auto_id) from stock where goodsid=@goodsid)
--更新货架数据
update shelf set remain=remain-@count where goodsid=@goodsid
--是不是已经到了提示状态
select @warn=remain-min from shelf where goodsid=@goodsid
if @warn <='0'--到了最小在架量
begin
raiserror('商品[%s]的在架量已经过限,请及时调拨！',16,1,@goodsname)
return
end
return @@error
end
--是促销商品,说明这个商品在库存中剩的很少了
--更新库存
update storage set count=count-@count*
(select goodscount from onsale where goodsid=storage.goodsid and onsalename=@goodsname)
where goodsid in
(select goodsid from onsale where onsalename=@goodsname)
--更新货架
update shelf set remain=remain-@count where goodsid=@goodsid and partionname like '%促销%'
select @warn=remain-min from shelf where goodsid=@goodsid and partionname like '%促销%'
--是不是已经到了提示状态
if @warn <='0'
begin
raiserror('商品[%s]的在架量已经过限,请及时调拨！',16,1,@goodsname)
return
end
return @@error
end
-----------------------------------
在这个系统中很少会用到事务的处理，只有在添加采购的时候同时会更新库存信息
代码 如下：
采购事务
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[AddStock]
@goodsname nvarchar(20),@shipdate datetime,@count int,
@pay decimal(8,2),@madedate datetime,
@suppname nvarchar(20),@info text
AS
declare @goodsid nvarchar(20),
@stockid int
BEGIN
set @goodsid=''
select @goodsid=goodsid from goodsdetail where goodsname=@goodsname
if @goodsid=''
begin
raiserror ('没有找到名称为[%s]对应的商品信息',16,1,@goodsname)
return
end
SET NOCOUNT ON;
begin transaction beginaddstock
insert into stock(goodsid ,shipdate ,count,pay,madedate,suppname,info )
values (@goodsid,@shipdate,@count,@pay,@madedate,@suppname,@info )
save transaction savedstock
set @stockid=@@identity
insert into storage(goodsid,stockid,count,addrass,info)
values(@goodsid,@stockid,@count,'','还没有给该批货物分配库存。')
if @@error <>'0'
begin
rollback transaction savedstock
raiserror ('采购数据保存成功，但是没有能够保存到库存信息中！',16,1)
return @@error
end
commit
return @@error
END
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[AddStock]
@goodsname nvarchar(20),@shipdate datetime,@count int,
@pay decimal(8,2),@madedate datetime,
@suppname nvarchar(20),@info text
AS
declare @goodsid nvarchar(20),
@stockid int
BEGIN
set @goodsid=''
select @goodsid=goodsid from goodsdetail where goodsname=@goodsname
if @goodsid=''
begin
raiserror ('没有找到名称为[%s]对应的商品信息',16,1,@goodsname)
return
end
SET NOCOUNT ON;
begin transaction beginaddstock
insert into stock(goodsid ,shipdate ,count,pay,madedate,suppname,info )
values (@goodsid,@shipdate,@count,@pay,@madedate,@suppname,@info )
save transaction savedstock
set @stockid=@@identity
insert into storage(goodsid,stockid,count,addrass,info)
values(@goodsid,@stockid,@count,'','还没有给该批货物分配库存。')
if @@error <>'0'
begin
rollback transaction savedstock
raiserror ('采购数据保存成功，但是没有能够保存到库存信息中！',16,1)
return @@error
end
commit
return @@error
END
-------------------------------------
最后是一个备份数据库的过程：
备份存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[BackUpFullMarketDb]
@filepath nvarchar(50),--保存备份的路径,包含备份名字,但不能有后缀名
@name nvarchar(20),--备份的名字
@full bit,--如果为true表是全部备份,否则为差异备份
@expire int,--备份过期时间,0表示永不过期
@desc nvarchar(200)--对备份的说明信息
as
if @full='true'
begin
BACKUP DATABASE [MarketDb]
TO DISK = @filepath
WITH DESCRIPTION = @desc, RETAINDAYS = @expire, NOFORMAT, NOINIT,
NAME = @name, SKIP, NOREWIND, NOUNLOAD, STATS = 10
return @@error--返回是不是有错误或异常
end
begin
BACKUP DATABASE [MarketDb]
TO DISK = @filepath
WITH DIFFERENTIAL,DESCRIPTION = @desc, RETAINDAYS = @expire, NOFORMAT, NOINIT,
NAME = @name, SKIP, NOREWIND, NOUNLOAD, STATS = 10
return @@error--返回是不是有错误或异常
end
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[BackUpFullMarketDb]
@filepath nvarchar(50),--保存备份的路径,包含备份名字,但不能有后缀名
@name nvarchar(20),--备份的名字
@full bit,--如果为true表是全部备份,否则为差异备份
@expire int,--备份过期时间,0表示永不过期
@desc nvarchar(200)--对备份的说明信息
as
if @full='true'
begin
BACKUP DATABASE [MarketDb]
TO DISK = @filepath
WITH DESCRIPTION = @desc, RETAINDAYS = @expire, NOFORMAT, NOINIT,
NAME = @name, SKIP, NOREWIND, NOUNLOAD, STATS = 10
return @@error--返回是不是有错误或异常
end
begin
BACKUP DATABASE [MarketDb]
TO DISK = @filepath
WITH DIFFERENTIAL,DESCRIPTION = @desc, RETAINDAYS = @expire, NOFORMAT, NOINIT,
NAME = @name, SKIP, NOREWIND, NOUNLOAD, STATS = 10
return @@error--返回是不是有错误或异常
end
---------------------------------------------------
浙公网安备 33010602011771号