mysql存储过程案例

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);