create or replace procedure getDIVInfo(div_info out cbmn_divorder%rowtype) is begin select * into div_info from cbmn_divorder; end getDIVInfo;在JDBC中如何获取数据
-- 存储过程 CREATE PROCEDURE sf_进货单 @记帐人 char(10) = NULL AS begin transaction -- 库存库中没有,增加记录 INSERT INTO 库存库(货号,仓库,库存数量,库存金额,库存单价) SELECT DISTINCT j.货号, j.仓库, 0,0,0 FROM 进货单明细 AS J left join 库存库 as k on ( j.仓库=k.仓库 and j.货号=k.货号 ) where k.货号 is null -- 修改库存信息 UPDATE 库存库 SET 库存单价=case when 库存数量<=0 or (库存数量+数量ALL)<=0 then 进价 else (库存金额+税价合计ALL)/(库存数量+数量ALL) end , 库存数量=库存数量+数量ALL, 库存金额=case when 库存数量<=0 or (库存数量+数量ALL)<=0 then 进价*(库存数量+数量ALL) else (库存金额+税价合计ALL) end , 最新进价=进价 FROM (SELECT 仓库,货号,'数量ALL'=sum(进货数量), '进价' = sum(税价合计)/sum(进货数量), '税价合计ALL'=sum(税价合计) FROM 进货单明细 GROUP BY 仓库,货号) AS LSJ WHERE 库存库.仓库=LSj.仓库 AND 库存库.货号=LSj.货号 -- 加入应付款 INSERT INTO 应付款(编号, 进货单号, 货号, 供货商号, 数量, 进货单价, 金额, 进货日期, 状态) SELECT '付'+a.编号,b.编号,a.货号,b.供货商号,进货数量,进价,a.税价合计,进货日期,'应付' FROM 进货单明细 as a, 进货单 as b where a.进货单号=b.编号 -- 加入历史 insert into 进货单历史 select * from 进货单 insert into 进货单明细历史 select * from 进货单明细 -- 清除进货单 delete from 进货单明细 delete from 进货单 commit go
CREATE PROCEDURE sf_销售单 @记帐人 char(10) = NULL AS begin transaction -- 修改库存信息 UPDATE 库存库 SET 库存数量=库存数量-数量ALL, 库存金额=库存单价*(库存数量-数量ALL) FROM (SELECT 仓库,货号,'数量ALL'=sum(销售数量) FROM 销售单明细 GROUP BY 仓库,货号) AS LSJ WHERE 库存库.仓库=LSj.仓库 AND 库存库.货号=LSj.货号 -- 加入应收款 INSERT INTO 应收款(编号, 销售单号, 货号, 客户编号, 数量, 销售价, 金额, 销售日期, 状态) SELECT '收'+a.编号,b.编号,a.货号,b.客户编号,销售数量,销售价,a.税价合计,销售日期,'应收' FROM 销售单明细 as a, 销售单 as b where a.销售单号=b.编号 -- 加入历史 insert into 销售单历史 select * from 销售单 insert into 销售单明细历史 select * from 销售单明细 -- 清除销售单 delete from 销售单明细 delete from 销售单 commit----------------- 自己好好研究下!
CallableStatement prepareCall(String sql)
throws SQLException创建一个 CallableStatement 对象来调用数据库存储过程。CallableStatement 对象提供了设置其 IN 和 OUT 参数的方法,以及用来执行对存储过程的调用的方法。
CONSTRAINT [进货单_供货商_fk] FOREIGN KEY
(
[供货商号]
) REFERENCES [dbo].[供货商清单] (
[供货商号]
)
GO
ALTER TABLE [dbo].[进货单明细] ADD
CONSTRAINT [FK_进货单明细_进货单] FOREIGN KEY
(
[进货单号]
) REFERENCES [dbo].[进货单] (
[编号]
),
CONSTRAINT [进货单明细_货号_fk] FOREIGN KEY
(
[货号]
) REFERENCES [dbo].[商品清单] (
[货号]
)
GO
ALTER TABLE [dbo].[销售单] ADD
CONSTRAINT [销售单_客户编号_fk] FOREIGN KEY
(
[客户编号]
) REFERENCES [dbo].[客户清单] (
[客户编号]
)
GO
ALTER TABLE [dbo].[销售单明细] ADD
CONSTRAINT [FK_销售单明细_销售单] FOREIGN KEY
(
[销售单号]
) REFERENCES [dbo].[销售单] (
[编号]
),
CONSTRAINT [销售单明细_货号_fk] FOREIGN KEY
(
[货号]
) REFERENCES [dbo].[商品清单] (
[货号]
)
GO
1.5.9 创建存储过程
系统使用了两个存储过程,分别实现进货时加大库存、产生应付款和销售时减少库存、产生应收款的功能。
-- 存储过程
CREATE PROCEDURE sf_进货单 @记帐人 char(10) = NULL
AS
begin transaction
-- 库存库中没有,增加记录
INSERT INTO 库存库(货号,仓库,库存数量,库存金额,库存单价)
SELECT DISTINCT j.货号, j.仓库, 0,0,0
FROM 进货单明细 AS J left join 库存库 as k on ( j.仓库=k.仓库 and j.货号=k.货号 )
where k.货号 is null
-- 修改库存信息
UPDATE 库存库 SET 库存单价=case when 库存数量<=0 or (库存数量+数量ALL)<=0 then 进价
else (库存金额+税价合计ALL)/(库存数量+数量ALL) end ,
库存数量=库存数量+数量ALL,
库存金额=case when 库存数量<=0 or (库存数量+数量ALL)<=0
then 进价*(库存数量+数量ALL) else (库存金额+税价合计ALL) end ,
最新进价=进价
FROM
(SELECT 仓库,货号,'数量ALL'=sum(进货数量), '进价' = sum(税价合计)/sum(进货数量),
'税价合计ALL'=sum(税价合计) FROM 进货单明细 GROUP BY 仓库,货号) AS LSJ
WHERE 库存库.仓库=LSj.仓库 AND 库存库.货号=LSj.货号
-- 加入应付款
INSERT INTO 应付款(编号, 进货单号, 货号, 供货商号, 数量, 进货单价, 金额, 进货日期, 状态)
SELECT '付'+a.编号,b.编号,a.货号,b.供货商号,进货数量,进价,a.税价合计,进货日期,'应付'
FROM 进货单明细 as a, 进货单 as b
where a.进货单号=b.编号
-- 加入历史
insert into 进货单历史 select * from 进货单
insert into 进货单明细历史 select * from 进货单明细
-- 清除进货单
delete from 进货单明细
delete from 进货单
commit
go
CREATE PROCEDURE sf_销售单 @记帐人 char(10) = NULL
AS
begin transaction
-- 修改库存信息
UPDATE 库存库 SET 库存数量=库存数量-数量ALL, 库存金额=库存单价*(库存数量-数量ALL)
FROM (SELECT 仓库,货号,'数量ALL'=sum(销售数量) FROM 销售单明细
GROUP BY 仓库,货号) AS LSJ
WHERE 库存库.仓库=LSj.仓库 AND 库存库.货号=LSj.货号
-- 加入应收款
INSERT INTO 应收款(编号, 销售单号, 货号, 客户编号, 数量, 销售价, 金额, 销售日期, 状态)
SELECT '收'+a.编号,b.编号,a.货号,b.客户编号,销售数量,销售价,a.税价合计,销售日期,'应收'
FROM 销售单明细 as a, 销售单 as b
where a.销售单号=b.编号
-- 加入历史
insert into 销售单历史 select * from 销售单
insert into 销售单明细历史 select * from 销售单明细
-- 清除销售单
delete from 销售单明细
delete from 销售单
commit-----------------
自己好好研究下!