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)BEGINaa:BEGINif 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)selectCONCAT(sap.DOMAIN,sap.PRID) id,sap.domain,#系统IDsap.prid,#采购计划IDsap.eindt, #计划交货日期（要求到货日期）sap.ebeln , #采购订单编号sap.aedat , #采购订单创建日期sfn.kokrs , #BGsap.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 sdiwhere  spi.MATNR = esip.matnrand  spi.MATNR = sdi.MATNR#and spi.EINDT is not nulland DATE_FORMAT(spi.EINDT,'%Y-%m-%d') = DATE_FORMAT(esip.lfdat,'%Y-%m-%d')and sdi.LGORT = lgortParam) sapleft join sap_dn_info sdi on sap.MATNR = sdi.MATNRleft join (select * from sap_epse_org_company_data  where zckbs = 'B' ) qb on sap.prctr = qb.prctrleft join (select * from sap_epse_org_company_data  where zckbs = 'C' or zckbs ='D' ) wd on sap.prctr = wd.prctrleft join sap_fi_network sfn on sap.MATNR = sfn.prctrleft join (selectorcs.MATERIEL_CODE, orcs.EMERGENCY_NEW_LEVEL , orcs.STO_NEW_STATE from ofs_receipt_change orcswhere orcs.APPROVAL_STATUS = 3 ) orc on sap.MATNR = orc.MATERIEL_CODEorder 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 thencc:BEGIN/** 标记是否出错 */DECLARE sc int default 0;/** 父级数据参数 */DECLARE father_ddbh varchar(50); #订单编号DECLARE father_ddcjrq date; #订单创建日期DECLARE father_planGoodsDate date ;#计划发货日期DECLARE father_bg varchar(30); #BGDECLARE 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_nameDECLARE father_cursor_name CURSOR FORselect 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,bfetch  father_cursor_name into father_ddbh;#开始循环，判断是否游标已经到达了最后作为循环条件while sc <> 1 doselect 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 fromofs_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:BEGINDECLARE aedat date;  #计划交货日期（要求到货日期）DECLARE eindt date;  #计划交货日期（要求到货日期）DECLARE planGoodsDate date; #计划发货日期DECLARE domain varchar(20); #系统IDDECLARE ebeln varchar(50); #订单编号DECLARE matnr varchar(50); #物料编码DECLARE prid varchar(30); #采购计划IDDECLARE 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); #leadTimeDECLARE 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_nameDECLARE cursor_name CURSOR FORselectt.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 twhere 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,bfetch  cursor_name into domain,prid,ebeln,matnr,pomenge,intCode,bstype,zmark,lgort,emergencyLevel,eindt,planGoodsDate,predictGoodsDate,aedat;#开始循环，判断是否游标已经到达了最后作为循环条件while s <> 1 doif predictGoodsDate is null then#发货处理时间select od.leadtime into fhclsj from ofs_dispatchtime od where od.depotCode = lgort andod.urgentFlg = emergencyLevel and od.materialType = bstype;#物流运输时间select ot.transporttime into wlyssj from ofs_transporttime ot where ot.depotCode = lgortand ot.urgentFlg = emergencyLevel and ot.materialType = bstype and ot.deptCode = intCode;set leadTime = fhclsj + wlyssj ;#计划发货时间= 预计发货时间 = 计划交货时间 - leadTimeselect 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 = planGoodsDatewhere 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 =sykucunwhere 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 = matnrand oss.LGORT = lgort;else/**如果物料库存不满足订单物料所需数量   则该订单下所有物料分配状态为  未分配    1已分配 0未分配同时把已分配的该订单下物料 订单数量进行回滚*/#查询订单有多少种物料select count(0) into counts from ofs_storage_distribution_plan osdp2where osdp2.ebeln = ebeln ;while i<counts doselect osdp3.ebeln ,osdp3.matnr ,osdp3.pomenge ,osdp3.stoStatusinto ebelnState,matnrState,pomengeState ,stoStatusState fromofs_storage_distribution_plan osdp3 limit i,1;if  stoStatusState > 0 then#已分配  需要回滚  增加库存中该物料数量update ofs_sap_storage oss2 set oss2.LABST=oss2.LABST+pomengeState whereoss2.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' whereosdp4.ebeln = ebeln or osdp4.matnr = ebeln;update ofs_storage_distribution_plan osdp setosdp.predictGoodsDate = planGoodsDate , osdp.occupyRepCount = '0',osdp.occupyOnlineCount = occupyOnlineCount , osdp.occupyRepLastCount =kucunwhere 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 thenrollback; -- 事务回滚elsecommit; -- 事务提交end if;END bb;END ;//DELIMITER ;#call proc_storage_dis_plan('RDC1',1,1);call proc_storage_dis_plan(1,0,0);