库存管理的oracle存储过程

最近公司的一个农企信息化改造项目，产品让小弟负责销售订单的一系列模块，其中下订单就已经 很坑了，没想到发货和库存管理也有很多坑，因为库存需要从原有的上游系统提供的视图中获取，做出库也要远程调用接口，所有发货申请提交后到正式出库前的的库存预占和释放就需要项目本地管理，由于之前没有接触过oracle过程，边做边学也算是啃下来了，记录一下基本的使用

基本需求

1. 提交发货申请后，状态为已预占
2. 正式出库后，删除预占记录
3. 统计库存时，需要减去本地预占的库存
4. 通过存储过程，以三天为周期自动释放或占用库存，以保证未正式出库的发货不长时间占用

存储过程

CREATE OR REPLACE
PROCEDURE "UPDATEOCCUPY" is  
v_occu number;v_total number;--库存，用于判断是否自动占用库存
BEGIN
	for oc in (select a.OCCUPY_NUMBER,a.PRODUCT_ID,a.DELIVER_REPOSITORY,a.occupy_end_time,a.occupy_state,a.orderdeliveryoccupy_id,a.orderdeliverypro_id
	from sale_order_delivery_occupy a
	where a.status ='0' and a.DELIVERY_STATE = '0050310172') LOOP		
	--本地预占
					SELECT sum(py.OCCUPY_NUMBER) into v_occu from SALE_ORDER_DELIVERY_OCCUPY py where py.PRODUCT_ID = oc.PRODUCT_ID and py.DELIVER_REPOSITORY = oc.DELIVER_REPOSITORY and py.STATUS = '0';	
		--视图库存
		select q.FBaseQty into v_total from AINVENTORY@JINDIE q LEFT JOIN PD_PRODUCT prd on prd.PRODUCT_ID = oc.PRODUCT_ID 	where  q.FWAREHOUSENUMBER=oc.DELIVER_REPOSITORY and q.MaterialNumber = prd.P_FRAME;
    	IF (oc.occupy_end_time <= to_char(sysdate,'yyyy-MM-dd')) THEN
			case
				when oc.occupy_state ='0050310181' then
-- 				修改状态
					update sale_order_delivery_occupy set RELEASE_TIP = '0050310191' where 			  orderdeliveryoccupy_id = oc.orderdeliveryoccupy_id;
				  update sale_order_delivery_occupy set occupy_state = '0050310182' where 			  orderdeliveryoccupy_id = oc.orderdeliveryoccupy_id;
					 update sale_order_delivery_pro set delivery_state = '0050310175' where 			  orderdeliverypro_id = oc.orderdeliverypro_id;
-- 				修改本次修改时间
					 update sale_order_delivery_occupy set occupy_end_time = to_char(sysdate+3,'yyyy-MM-dd') where 			  orderdeliveryoccupy_id = oc.orderdeliveryoccupy_id;
-- 
				when oc.occupy_state ='0050310182' then
				--判断库存是否可以再次做预占
				IF (oc.OCCUPY_NUMBER <= (v_total - v_occu)) THEN
-- 				修改状态
					update sale_order_delivery_occupy set occupy_state = '0050310181' where 			  orderdeliveryoccupy_id = oc.orderdeliveryoccupy_id;
					update sale_order_delivery_pro set delivery_state = '0050310172' where 			  orderdeliverypro_id = oc.orderdeliverypro_id;
-- 				修改本次修改时间
					 update sale_order_delivery_occupy set occupy_end_time = to_char(sysdate+3,'yyyy-MM-dd') where 			  orderdeliveryoccupy_id = oc.orderdeliveryoccupy_id;
					END IF;
					end case;					
   END IF; 
END LOOP;
END;

ORACLE定时任务

之后用PL/SQL创建一个时间间隔为24h的定时任务去调用存储过程就大功告成

创建方法：https://www.cnblogs.com/yx007/p/6519544.html

END

虽然搞定了业务功能，但是感觉还有可以优化的地方。搜索资料的时候很多文章说存储过程的方式在很多大公司已经是弃用的状态，因为无法做版本控制同时代码可读性太差，不知道以后还有没能用到的地方

查看Oracle数据库的定时任务