mysql存储过程案例

原创 2015年11月20日 15:20:30

Drop PROCEDURE proc_storage_dis_plan;
DELIMITER //
#lgortParam 配送仓库
#ztSelect 是否选择在途 1 选择 0未选择
#isLoading 是否重新获取数据 1 重新获取 0无需获取
CREATE  PROCEDURE proc_storage_dis_plan(IN lgortParam varchar(50),in ztSelect int,in isLoading int)


BEGIN
aa:BEGIN
if isLoading>0 then

/** 数据获取 **/

delete from ofs_storage_distribution_plan;
insert into ofs_storage_distribution_plan (id,domain,prid,eindt,ebeln,aedat
,kokrs,ebelp,areaCode,areaName,intCode,intName,bstype,matnr,txz01,werks,
zmark,pomenge,vbeln,`status`,emergencyLevel,stoStatus,lgort)
select
CONCAT(sap.DOMAIN,sap.PRID) id,
sap.domain,#系统ID
sap.prid,#采购计划ID
sap.eindt, #计划交货日期(要求到货日期)
sap.ebeln , #采购订单编号
sap.aedat , #采购订单创建日期
sfn.kokrs , #BG
sap.ebelp , #采购订单行
qb.prctr areaCode , #区部代码
qb.ktext areaName , #区部名称
wd.prctr intCode , #网点代码
wd.ktext intName , #网点名称
sap.bstype , #计划类型
sap.matnr , #物料编码
sap.txz01 , #物料名称
sap.werks , #工厂
sap.zmark , #物资类别
sap.pomenge , #订单数量
sap.vbeln , #外向交货单号
sap.`status` , #交货单状态
orc.EMERGENCY_NEW_LEVEL emergencyLevel, #紧急度
orc.STO_NEW_STATE stoStatus , #采购订单状态
sap.lgort #配送仓库

from (select spi.DOMAIN,spi.PRID,
spi.eindt, #计划交货日期(要求到货日期)
spi.ebeln , #采购订单编号
spi.aedat , #采购订单创建日期
spi.ebelp , #采购订单行
esip.bstype , #计划类型
esip.matnr , #物料编码
spi.txz01 , #物料名称
esip.werks , #工厂
esip.zmark , #物资类别
spi.pomenge , #订单数量
esip.prctr , #网点代码
sdi.vbeln , #外向交货单号
sdi.`status` , #交货单状态
sdi.lgort #配送仓库
from sap_pir_info spi ,epse_sap_interface_plan esip ,sap_dn_info sdi
where  spi.MATNR = esip.matnr
and  spi.MATNR = sdi.MATNR
#and spi.EINDT is not null
and DATE_FORMAT(spi.EINDT,'%Y-%m-%d') = DATE_FORMAT(esip.lfdat,'%Y-%m-%d')
and sdi.LGORT = lgortParam
) sap
left join sap_dn_info sdi on sap.MATNR = sdi.MATNR
left join (select * from sap_epse_org_company_data  where zckbs = 'B' ) qb on sap.prctr = qb.prctr
left join (select * from sap_epse_org_company_data  where zckbs = 'C' or zckbs ='D' ) wd on sap.prctr = wd.prctr
left join sap_fi_network sfn on sap.MATNR = sfn.prctr
left join (select
orcs.MATERIEL_CODE, orcs.EMERGENCY_NEW_LEVEL , orcs.STO_NEW_STATE from ofs_receipt_change orcs
where orcs.APPROVAL_STATUS = 3 ) orc on sap.MATNR = orc.MATERIEL_CODE
order by orc.EMERGENCY_NEW_LEVEL, sap.eindt;

delete from ofs_sap_storage;
insert into ofs_sap_storage select * from sap_storage;

end if;

end aa;

if isLoading>0 then

cc:BEGIN

/** 标记是否出错 */
DECLARE sc int default 0;

/** 父级数据参数 */
DECLARE father_ddbh varchar(50); #订单编号
DECLARE father_ddcjrq date; #订单创建日期
DECLARE father_planGoodsDate date ;#计划发货日期
DECLARE father_bg varchar(30); #BG
DECLARE father_areaCode varchar(255); #区部代码
DECLARE father_areaName varchar(10); #区部名称
DECLARE father_intCode varchar(255); #网点代码
DECLARE father_intName varchar(10); #网点名称
DECLARE father_lgort varchar(20); #配送仓库
DECLARE father_jhlx varchar(20); #计划类型
DECLARE father_wzlb varchar(20); #物资类别
DECLARE father_gc varchar(20); #工厂
DECLARE father_counts int default 0;
DECLARE father_id varchar(50); #id


#声明游标cursor_name
DECLARE father_cursor_name CURSOR FOR
select o.ebeln from ofs_storage_distribution_plan o where o.ebeln<>'0' group by o.ebeln;

#设置一个终止标记
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET sc=1;

#打开游标
OPEN father_cursor_name;

#获取游标当前指针的记录,读取一行数据并传给变量a,b
fetch  father_cursor_name into father_ddbh;
#开始循环,判断是否游标已经到达了最后作为循环条件
while sc <> 1 do
select fosdp.id,fosdp.ebeln,fosdp.aedat,fosdp.kokrs,fosdp.areaCode,fosdp.areaName
,fosdp.intCode,fosdp.intName,fosdp.lgort,planGoodsDate,bstype,zmark,werks into father_id,
father_ddbh,father_ddcjrq,father_bg,father_areaCode
,father_areaName,father_intCode,father_intName,father_lgort,father_planGoodsDate,
father_jhlx,father_wzlb,father_gc from
ofs_storage_distribution_plan fosdp where fosdp.ebeln = father_ddbh limit 0,1;
#添加父级数据   默认 为未分配
set father_id = CONCAT(father_id,'00');
insert into ofs_storage_distribution_plan(id,ebeln,matnr,aedat,kokrs,areaCode,areaName
,intCode,intName,lgort,flag,allotState,bstype,zmark,werks) values(father_id,'0',father_ddbh,
father_ddcjrq,father_bg,father_areaCode,father_areaName,father_intCode,father_intName
,father_lgort,'1','0',father_jhlx,father_wzlb,father_gc);

#读取下一行的数据
fetch father_cursor_name into father_ddbh;
end while;
#关闭游标
CLOSE father_cursor_name ;


end cc;

end if;

/**  业务处理   **/

bb:BEGIN

DECLARE aedat date;  #计划交货日期(要求到货日期)
DECLARE eindt date;  #计划交货日期(要求到货日期)
DECLARE planGoodsDate date; #计划发货日期
DECLARE domain varchar(20); #系统ID
DECLARE ebeln varchar(50); #订单编号
DECLARE matnr varchar(50); #物料编码
DECLARE prid varchar(30); #采购计划ID
DECLARE intCode varchar(255); #网点代码
DECLARE bstype varchar(10); #计划类型
DECLARE zmark varchar(10); #物资类别
DECLARE lgort varchar(20); #配送仓库
DECLARE emergencyLevel varchar(20); #紧急度
DECLARE fhclsj varchar(20); #发货处理时间
DECLARE wlyssj varchar(20); #物流运输时间
DECLARE leadTime varchar(20); #leadTime
DECLARE s int default 0;
DECLARE i int default 0;
DECLARE counts int ; #订单物料个数
DECLARE kucun decimal(13,3); #库存
DECLARE sykucun decimal(13,3); #剩余库存
DECLARE pomenge decimal(13,3); #订单数量
DECLARE yfpCount decimal(13,3); #已分配的订单数量

DECLARE isworkdate int; #是否为工作日

DECLARE predictGoodsDate varchar(20); #预计发货日期
DECLARE occupyRepCount varchar(20); #占用库存数量
DECLARE occupyOnlineCount varchar(20); #占用在途数量
DECLARE occupyRepLastCount varchar(20); #占用库存剩余数量
DECLARE allotState varchar(20); #分配状态

DECLARE ebelnState varchar(50); #订单编号  更改状态时
DECLARE matnrState varchar(50); #物料编码  更改状态时
DECLARE pomengeState decimal(13,3); #物料数量  更改状态时
DECLARE stoStatusState varchar(50); #物料状态  更改状态时

/** 标记是否出错 */
declare t_error int default 0;


#声明游标cursor_name
DECLARE cursor_name CURSOR FOR
select
t.domain,t.prid,
t.ebeln, #订单编号
t.matnr, #物料编码
t.pomenge, #订单数量
t.intCode, #网点代码
t.bstype, #计划类型
t.zmark, #物资类别
t.lgort, #配送仓库
t.emergencyLevel, #紧急度
t.eindt, #计划交货日期(要求到货日期)
t.planGoodsDate,#计划发货日期
t.predictGoodsDate,#预计发货日期
t.aedat # 采购订单创建日期
from ofs_storage_distribution_plan t
where t.flag = 2 #为子叶子
and   t.eindt is not null  #计划交货日期(要求到货日期)
and   t.stoStatus is null  # STO状态为非冻结,删除
# 排序 优先级别, 计划交货日期,采购订单创建日期 ,订单编号 ,紧急度 ,计划类型
order by  t.`level` desc,t.eindt,t.aedat,t.ebeln,t.emergencyLevel,t.bstype;

#设置一个终止标记
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;


/** 如果出现sql异常,则将t_error设置为1后继续执行后面的操作 */
declare continue handler for sqlexception set t_error=1; # 出错处理

# 开始事务
START TRANSACTION;

#初始化库存
delete from ofs_sap_storage;
insert into ofs_sap_storage select * from sap_storage;

set s = 0;

#打开游标
OPEN cursor_name;

#获取游标当前指针的记录,读取一行数据并传给变量a,b
fetch  cursor_name into domain,prid,ebeln,matnr,pomenge,intCode,bstype,zmark,lgort
,emergencyLevel,eindt,planGoodsDate,predictGoodsDate,aedat;
#开始循环,判断是否游标已经到达了最后作为循环条件
while s <> 1 do
if predictGoodsDate is null then
#发货处理时间
select od.leadtime into fhclsj from ofs_dispatchtime od where od.depotCode = lgort and
od.urgentFlg = emergencyLevel and od.materialType = bstype;
#物流运输时间
select ot.transporttime into wlyssj from ofs_transporttime ot where ot.depotCode = lgort
and ot.urgentFlg = emergencyLevel and ot.materialType = bstype and ot.deptCode = intCode;
set leadTime = fhclsj + wlyssj ;
#计划发货时间= 预计发货时间 = 计划交货时间 - leadTime
select DATE_ADD(eindt,INTERVAL -leadTime DAY) into planGoodsDate;
#判断是否为工作日
select count(0) into isworkdate from ofs_work_date owd where owd.date =
planGoodsDate and owd.state = 'Y';
while isworkdate >0 do
#当为非工作日时  向后推到工作日
select DATE_ADD(planGoodsDate,INTERVAL 1 DAY) into planGoodsDate;
select count(0) into isworkdate from ofs_work_date owd where owd.date =
planGoodsDate and owd.state = 'Y';
end while;

update ofs_storage_distribution_plan osd set osd.predictGoodsDate = planGoodsDate
where osd.matnr = ebeln;

end if;

#查出物料库存
select ss.LABST into kucun from ofs_sap_storage ss where ss.MATNR = matnr and ss.LGORT = lgort;
set sykucun =  kucun - pomenge;
if sykucun >= 0 then

#如果物料库存满足订单物料所需数量   则该条记录状态为  已分配   1已分配 0未分配

update ofs_storage_distribution_plan osdp set  osdp.allotState = '1' ,
osdp.predictGoodsDate = planGoodsDate , osdp.occupyRepCount = pomenge,
osdp.occupyOnlineCount = occupyOnlineCount , osdp.occupyRepLastCount =sykucun
where osdp.DOMAIN = domain and osdp.PRID = prid ;

#更改订单状态为已分配
update ofs_storage_distribution_plan osdp0 set  osdp0.allotState = '1'
where osdp0.matnr = ebeln ;

#已分配  需要减少库存中该物料数量
update ofs_sap_storage oss set oss.LABST=sykucun where oss.MATNR = matnr
and oss.LGORT = lgort;

else

/**如果物料库存不满足订单物料所需数量   则该订单下所有物料分配状态为  未分配    1已分配 0未分配
同时把已分配的该订单下物料 订单数量进行回滚*/

#查询订单有多少种物料
select count(0) into counts from ofs_storage_distribution_plan osdp2
where osdp2.ebeln = ebeln ;

while i<counts do
select osdp3.ebeln ,osdp3.matnr ,osdp3.pomenge ,osdp3.stoStatus
into ebelnState,matnrState,pomengeState ,stoStatusState from
ofs_storage_distribution_plan osdp3 limit i,1;
if  stoStatusState > 0 then
#已分配  需要回滚  增加库存中该物料数量
update ofs_sap_storage oss2 set oss2.LABST=oss2.LABST+pomengeState where
oss2.MATNR = matnrState and oss2.LGORT = lgort;

set kucun = kucun + pomengeState;
end if;
set i=i+1;
end while;

#该订单状态设置为  未分配
update ofs_storage_distribution_plan osdp4 set osdp4.allotState = '0' where
osdp4.ebeln = ebeln or osdp4.matnr = ebeln;


update ofs_storage_distribution_plan osdp set
osdp.predictGoodsDate = planGoodsDate , osdp.occupyRepCount = '0',
osdp.occupyOnlineCount = occupyOnlineCount , osdp.occupyRepLastCount =kucun
where osdp.DOMAIN = domain and osdp.PRID = prid ;
end if;


#读取下一行的数据
fetch cursor_name into domain,prid,ebeln,matnr,pomenge,intCode,bstype,zmark,lgort
,emergencyLevel,eindt,planGoodsDate,predictGoodsDate,aedat;
end while;
#关闭游标
CLOSE cursor_name ;

if t_error=1 then
rollback; -- 事务回滚
else
commit; -- 事务提交
end if;

END bb;

END ;
//
DELIMITER ;

#call proc_storage_dis_plan('RDC1',1,1);
call proc_storage_dis_plan(1,0,0);
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

MySql中数据库存储过程与触发器案例

1.存储过程简介 我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(StoredProcedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,...
  • lz_94
  • lz_94
  • 2016年05月21日 21:20
  • 306

mysql存储过程中in、out、inout参数使用实际案例

1.参数in的使用(代表输入,意思说你的参数要传到存过过程的过程里面去) //为了避免存储过程中分号(";")结束语句,我们使用分隔符告诉mysql解释器,该段命令是否已经结束了。 /** 案例...

存储过程案例

  • 2011年11月28日 14:31
  • 2KB
  • 下载

jsp 存储过程的应用案例

  • 2009年05月01日 13:49
  • 725KB
  • 下载

SQLServer 复杂存储过程并发优化(案例)

一个存储过程,1500行代码,内部有一个查询,关联使用了200多张表(其中有重复的表),并发线程执行,耗时15秒左右,结果返回一般几行记录。这个存储过程是系统中最耗时、最消耗性能的。今天突然想着得优化...

FREECHAR开发案例(带存储过程)

  • 2008年07月22日 14:10
  • 2.28MB
  • 下载

mysql存储过程

  • 2017年11月07日 17:14
  • 2KB
  • 下载

SQL Server存储过程入门案例详解[转]

SQL Server存储过程入门案例详解存储过程是存储于数据库中的一组T-SQL语句。有了存储过程之后,与数据库的交互就没有必要在程序中写一堆的SQL语句,而只需用一条语句调用适当的存储过程来完成就可...

mysql游标存储过程例子

  • 2017年08月15日 15:39
  • 5KB
  • 下载

Mysql存储过程游标触发器

  • 2015年07月09日 21:15
  • 2KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql存储过程案例
举报原因:
原因补充:

(最多只允许输入30个字)