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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!