oracle 存储过程举例,oracle存储过程举例

CREATE OR REPLACE PROCEDURE A_P_SCHEDULE_TO_STOCK(PLAN_TYPE1 IN VARCHAR2,ORG_CODE1 IN VARCHAR2,PLAN_D11 IN DATE,PLAN_D21 IN DATE) AS

STOCKNUM NUMBER;

BEGIN

--- 期初库存的月度结转

DECLARE

CURSOR C_1 IS

SELECT P.ORDER_ID,P.PART_NUMBER

FROM (SELECT P.ORDER_ID,(P.FINISH_NUM + NVL(O.OK_NUM,0)) AS FINISH_NUM,P.PART_NUMBER

FROM (SELECT ORDER_ID,SUM(FINISH_NUM) AS FINISH_NUM,PART_NUMBER

FROM P_SCHEDULE

WHERE RPC_TYPE = '包装'

AND PLAN_D1 = PLAN_D11

AND PLAN_D2 = PLAN_D21

AND ORG_CODE = ORG_CODE1

AND PLAN_TYPE=PLAN_TYPE1

GROUP BY ORDER_ID,PART_NUMBER) P

LEFT JOIN (SELECT * FROM O_STOCK WHERE OK_DATE = PLAN_D11) O

ON O.ORDER_ID = P.ORDER_ID

AND P.PART_NUMBER = O.PART_NUMBER) P

LEFT JOIN O_PURCHASE_ORDER O

ON O.ORDER_ID = P.ORDER_ID

WHERE O.I_STATUS <> '删除'

AND (O.NUM - P.FINISH_NUM) > 0;

C_ROW_1 C_1%ROWTYPE;

BEGIN

OPEN C_1;

LOOP

FETCH C_1

INTO C_ROW_1;

EXIT WHEN C_1%NOTFOUND;

DECLARE

CURSOR C_2 IS

SELECT OK_NUM,FINISH_NUM,ORG_CODE,PART_NUMBER,ORDER_ID,STAGE_CODE FROM (

SELECT P.*,O.OK_DATE,O.USER_CODE,NVL(O.OK_NUM,0) OK_NUM FROM (SELECT STAGE_CODE,SUM(FINISH_NUM) AS FINISH_NUM

FROM P_SCHEDULE WHERE ORDER_ID = C_ROW_1.ORDER_ID AND PLAN_TYPE=PLAN_TYPE1

GROUP BY STAGE_CODE,ORG_CODE) P

LEFT JOIN (SELECT * FROM O_STOCK WHERE OK_DATE = PLAN_D11 ) O ON O.ORDER_ID = P.ORDER_ID

AND P.PART_NUMBER = O.PART_NUMBER) T

WHERE OK_NUM>0 OR FINISH_NUM>0;

C_ROW_2 C_2 %ROWTYPE;

BEGIN

OPEN C_2;

LOOP

FETCH C_2

INTO C_ROW_2;

EXIT WHEN C_2%NOTFOUND;

-- 此处判断下月1号是否已有库存记录，如果没有插入记录，如果有更新现有记录，

--判断依据 ORDER_ID + PART_NUMBER + OK_DATE(PLAN_D11+一个月) + STAGE_CODE

SELECT COUNT(*) INTO STOCKNUM FROM DUAL WHERE EXISTS (SELECT 1 FROM O_STOCK

WHERE ORDER_ID=C_ROW_2.ORDER_ID AND STAGE_CODE=C_ROW_2.STAGE_CODE

AND PART_NUMBER=C_ROW_2.PART_NUMBER AND OK_DATE= ADD_MONTHS(PLAN_D11,1) AND ORG_CODE=C_ROW_2.ORG_CODE);

IF(STOCKNUM=0) THEN

INSERT INTO O_STOCK(PART_NUMBER,STAGE_CODE,OK_NUM,OK_DATE,ORDER_ID)

VALUES(C_ROW_2.PART_NUMBER,C_ROW_2.STAGE_CODE,(C_ROW_2.OK_NUM+C_ROW_2.FINISH_NUM),ADD_MONTHS(PLAN_D11,1),C_ROW_2.ORG_CODE,C_ROW_2.ORDER_ID);

ELSE

UPDATE O_STOCK SET OK_NUM=OK_NUM+C_ROW_2.OK_NUM+C_ROW_2.FINISH_NUM

WHERE ORDER_ID=C_ROW_2.ORDER_ID AND STAGE_CODE=C_ROW_2.STAGE_CODE

AND PART_NUMBER=C_ROW_2.PART_NUMBER AND OK_DATE= ADD_MONTHS(PLAN_D11,1) AND ORG_CODE=C_ROW_2.ORG_CODE;

STOCKNUM :=0;

END IF;

UPDATE O_STOCK SET IF_CLOSED=1 WHERE ORDER_ID=C_ROW_2.ORDER_ID AND STAGE_CODE=C_ROW_2.STAGE_CODE

AND PART_NUMBER=C_ROW_2.PART_NUMBER AND OK_DATE=PLAN_D11 AND ORG_CODE= C_ROW_2.ORG_CODE;

END LOOP;

CLOSE C_2;

END;

END LOOP;

CLOSE C_1;

END;

COMMIT;

END;

总结

以上是编程之家为你收集整理的oracle存储过程举例全部内容，希望文章能够帮你解决oracle存储过程举例所遇到的程序开发问题。

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。