存储过程常用语法

CREATE OR REPLACE PROCEDURE P_OMS_MATERIAL_ARRIVED(STARTTIME IN VARCHAR2,ENDTIME IN VARCHAR2,CURRENT_TIME DATE,DATATYPE IN NUMBER) IS
  /********************************************************************************************************
  PURPOSE:        物资供应链已到货月度，季度，年度数据，近12月
  AUTHOR:         ZERO
  DEPT:           -----------------
  PARAMETER       STARTTIME-开始，ENDTIME-结束，DATATYPE-1，当天2，本月3，本季度4，本年，5本12年
  CREATE DATE：   2019-11-8
  *********************************************************************************************************/
  --CURRENT_TIME DATE;                                       --当前时间
  --V_SUMMARY_TYPE  NUMBER;                                  --统计类型：1、总量统计；2、节点统计；3、区局统计
  V_CURENT_STATUE NUMBER;                                  --物资跟踪环节（1，需求计划申报 2，物流处置，3，生产运输，4，物资到货）
  V_BEGIN_END_TYPE NUMBER;                                 --开始结束时间类型（1，按开始时间  2按结束时间）
  V_REPLANSTANDARETIME NUMBER(8);                          --需求计划标准时常
  V_LOGISTICSDEALSTANDARETIME NUMBER(8);                   --物流处置标准时常
  V_TRANSPORTSTANDARETIME NUMBER(8);                       --生产运输标准时常
  V_MATERIALSARRIVALSTANDARETIME NUMBER(8);                --物资到货标准时常
  V_DEPT_CODE VARCHAR2(50);                                --单位编码
  V_DEPT_NAME VARCHAR2(50);                                --单位名称
  /**
  * 所有单位部门编码游标,00000000代表所有的集合
  */
  CURSOR CURSOR_DEPT IS (SELECT T.DEPT_CODE,T.DEPT_NAME FROM OMS_REQUIREMENT_DEPT T UNION ALL SELECT '00000000','深圳供电局有限公司' FROM DUAL);
BEGIN
  /**
  * 当前时间
  */
  --CURRENT_TIME := TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd');
  --CURRENT_TIME := TO_DATE('2019-11-19', 'yyyy-mm-dd');
  /**
   * 设置标准时常，标准时常后续根据不同物资设置，次标准时常为最初的需求值，对前者不影响
  */
  SELECT ST.SUBPROCESS_STANDARE_TIME INTO V_REPLANSTANDARETIME FROM OMS_SUBPROCESS_STANDARE_TIME ST WHERE ST.SUBPROCESS_TYPE_CODE = 1;--需求计划申报
  SELECT ST.SUBPROCESS_STANDARE_TIME INTO V_LOGISTICSDEALSTANDARETIME FROM OMS_SUBPROCESS_STANDARE_TIME ST WHERE ST.SUBPROCESS_TYPE_CODE = 2;--物流处置
  SELECT ST.SUBPROCESS_STANDARE_TIME INTO V_TRANSPORTSTANDARETIME FROM OMS_SUBPROCESS_STANDARE_TIME ST WHERE ST.SUBPROCESS_TYPE_CODE = 3;--生产运输
  SELECT ST.SUBPROCESS_STANDARE_TIME INTO V_MATERIALSARRIVALSTANDARETIME FROM OMS_SUBPROCESS_STANDARE_TIME ST WHERE ST.SUBPROCESS_TYPE_CODE = 4;--物资到货
  
  /***
  ** 先进行数据补0，补全列表所需要的所有格式数据，然后再更新真实数据
  **/
  V_BEGIN_END_TYPE := 1;--循环按开始和按结束进行统计 V_BEGIN_END_TYPE：1-按开始 2-按结束
  WHILE V_BEGIN_END_TYPE <= 2 LOOP
    V_CURENT_STATUE := 0;--0节点是点击已到货未到货显示的列表数据（节点0只需要按当天的维度统计，下面会删除非当天0节点的数据）
    WHILE V_CURENT_STATUE  <= 4 LOOP--已到货的只会在第四个节点存在明细数据，遍历是为了补充前三个节点的数据
      /*
      ** 插入0数据-初始化数据
      */ 
      INSERT INTO OMS_REQ_STATUS_STATISTICS
      SELECT 
        SYS_GUID() ID, --唯一标识符ID
        DEPT_ID REQUIREMENT_DEPT_ID, --申购部门ID
        DEPT_CODE REQUIREMENT_DEPT_CODE, --申购部门编码
        DEPT_NAME REQUIREMENT_DEPT_NAME, --申购部门名称
        CURRENT_TIME MONITOR_DATE, --监控日期
        1 RECEIPT_STATUS, --是否已入库（该值必须有，默认0未到货 1已到货）
        0 REQUIRE_TOTAL, --需求条目总数
        0 NO_OVERTIME_NUM, --未超时条目数
        0 OVERTIME_NUM, --超时条目数
        0 AVERAGE_TIME, --平均时长
        CASE
          WHEN V_CURENT_STATUE = 1 THEN V_REPLANSTANDARETIME
          WHEN V_CURENT_STATUE = 2 THEN V_LOGISTICSDEALSTANDARETIME
          WHEN V_CURENT_STATUE = 3 THEN V_TRANSPORTSTANDARETIME
          WHEN V_CURENT_STATUE = 4 THEN V_MATERIALSARRIVALSTANDARETIME
        END STANDARD_TIME, --标准时常
        DATATYPE STATISTICS_TYPE, --统计时间段类型（1，当天2，本月3，本季度4，本年，5本12年）
        V_CURENT_STATUE CURENT_STATUE, --当前所处流程节点（1，需求计划申报 2，物流处置，3，生产运输，4，物资到货）
        'SuperAdmin' CREATOR_ID, --创建人ID
        'SuperAdmin' CREATOR_NAME, --创建人名称
        SYSDATE CREATE_DATE, --创建时间
        NULL MODIFIER_ID, --修改人ID
        NULL MODIFIER_NAME, --修改人名称
        NULL MODIFY_DATE, --修改时间
        V_BEGIN_END_TYPE BEGIN_END_TYPE, --开始结束时间类型（1，按开始时间  2按结束时间）
        3 SUMMARY_TYPE --统计类型：1、总量统计；2、节点统计；3、区局统计
      FROM (SELECT DEPT_CODE DEPT_ID, DEPT_CODE, DEPT_NAME
            FROM OMS_REQUIREMENT_DEPT
          UNION ALL
          SELECT '00000000' DEPT_ID,
                 '00000000' DEPT_CODE,
                 '深圳供电局有限公司' DEPT_NAME
            FROM DUAL);
    V_CURENT_STATUE := V_CURENT_STATUE + 1;
    END LOOP;
  --循环计数加一，1-按开始 2-按结束
  V_BEGIN_END_TYPE := V_BEGIN_END_TYPE + 1;
  END LOOP;
  COMMIT;
  
  /**
  ** 保留当天按结束时间统计的0节点已到货数据，其他0节点数据删除
  */
  DELETE FROM OMS_REQ_STATUS_STATISTICS WHERE SUMMARY_TYPE = 3 AND RECEIPT_STATUS = 1 AND MONITOR_DATE = CURRENT_TIME AND CURENT_STATUE = 0 AND BEGIN_END_TYPE <> 2 AND STATISTICS_TYPE <> 1;
  COMMIT;

  /**
  * 打开游标
  */
  OPEN CURSOR_DEPT;
    LOOP
      FETCH CURSOR_DEPT INTO V_DEPT_CODE,V_DEPT_NAME;
      EXIT WHEN CURSOR_DEPT%NOTFOUND;
      V_BEGIN_END_TYPE := 1;--循环按开始和按结束进行统计 V_BEGIN_END_TYPE：1-按开始 2-按结束
      
        /***************************************抽取各单位统计数据（页面底部列表）_开始****************************************/
        WHILE V_BEGIN_END_TYPE <= 2 LOOP
          V_CURENT_STATUE := 1;
          WHILE V_CURENT_STATUE  <= 4 LOOP--已到货的只会在第四个节点存在明细数据，遍历是为了补充前三个节点的数据
            /**
            ** 计算真实数据更新上面的0数据
            */
            UPDATE OMS_REQ_STATUS_STATISTICS T
            SET (REQUIRE_TOTAL,NO_OVERTIME_NUM,OVERTIME_NUM,AVERAGE_TIME,MODIFIER_ID,MODIFIER_NAME,MODIFY_DATE) = 
            (SELECT
               T1.REQUIRE_TOTAL, --需求条目总数
               T1.NO_OVERTIME_NUM, --未超时条目数
               T1.REQUIRE_TOTAL - T1.NO_OVERTIME_NUM OVERTIME_NUM, --超时条目数
               DECODE(T1.REQUIRE_TOTAL,0,0,T1.TOTALTIME/T1.REQUIRE_TOTAL) AVERAGE_TIME, --平均时长，总时长除以总条数
               T1.MODIFIER_ID, --修改人ID
               T1.MODIFIER_NAME, --修改人名称
               T1.MODIFY_DATE --修改时间
              FROM (SELECT COUNT(1) REQUIRE_TOTAL, --需求条目总数，需求采购统计所有环节，其他三个环节统计专项采购
                NVL(SUM(CASE
                     WHEN V_CURENT_STATUE = 1 AND NVL(TR.REPLAN_OVERTIME, 0) <= 0 THEN 1
                     WHEN V_CURENT_STATUE = 2 AND NVL(TR.LOGISTICS_DEAL_OVERTIME, 0) <= 0 THEN 1
                     WHEN V_CURENT_STATUE = 3 AND NVL(TR.TRANSPORT_OVERTIME, 0) <= 0 THEN 1
                     WHEN V_CURENT_STATUE = 4 AND NVL(TR.MATERIALS_ARRIVAL_OVERTIME, 0) <= 0 THEN 1
                     ELSE 0
                   END),0) NO_OVERTIME_NUM, --未超时条目数，时常小于等于零则未超时
                NVL(SUM(CASE
                     WHEN V_CURENT_STATUE = 1 THEN NVL(TR.REPLAN_CONSUMING_TIME, 0)
                     WHEN V_CURENT_STATUE = 2 THEN NVL(TR.LOGISTICS_CONSUMING_TIME, 0)
                     WHEN V_CURENT_STATUE = 3 THEN NVL(TR.TRANSPORT_CONSUMING_TIME, 0)
                     WHEN V_CURENT_STATUE = 4 THEN NVL(TR.MATERIALS_CONSUMING_TIME, 0) ELSE 0
                   END),0) TOTALTIME, -- 总时长
                'SuperAdmin' MODIFIER_ID, --修改人ID
                'SuperAdmin' MODIFIER_NAME, --修改人名称
                SYSDATE MODIFY_DATE --修改时间
              FROM OMS_REQUIREMENT_PROCESS_TRACE TR,
                OMS_MATERIAL_REQUIREMENT_INFO TI
              WHERE TR.OMS_MATERIAL_REQUIREMENTINFOID = TI.OMS_MATERIAL_REQUIREMENTINFOID(+)
               AND TI.REQUIREMENT_DEPT_CODE LIKE DECODE(V_DEPT_CODE,'00000000','09',V_DEPT_CODE) || '%'--编码前缀匹配叶子（子集）集合数据
               --AND LENGTH(TI.REQUIREMENT_DEPT_CODE) = DECODE(V_DEPT_CODE,'00000000',6,LENGTH(TI.REQUIREMENT_DEPT_CODE))--为了处理深圳供电局只往下统计一层
               AND TR.MONITOR_DATE = CURRENT_TIME --统计当天抽取的数据
               AND TI.CREATE_DATE = CURRENT_TIME
               AND TO_DATE(STARTTIME, 'yyyy-mm-dd') <= (CASE V_BEGIN_END_TYPE WHEN 1 THEN TRUNC(TR.REPLAN_BEGIN_TIME) ELSE TRUNC(TR.MATERIALS_ARRIVAL_END_TIME) END)
               AND TO_DATE(ENDTIME, 'yyyy-mm-dd') >= (CASE V_BEGIN_END_TYPE WHEN 1 THEN TRUNC(TR.REPLAN_BEGIN_TIME) ELSE TRUNC(TR.MATERIALS_ARRIVAL_END_TIME) END)
               AND TR.RECEIPT_STATUS = 0 --已到货
               AND TR.MATERIAL_ITEM_TYPE = 1 --已到货所有环节都统计专项采购的数据
               AND TR.CURENT_STATUE = 4) T1 )
              WHERE T.CURENT_STATUE = V_CURENT_STATUE
               AND T.REQUIREMENT_DEPT_CODE = V_DEPT_CODE
               AND T.MONITOR_DATE = CURRENT_TIME
               AND T.RECEIPT_STATUS = 1
               AND T.BEGIN_END_TYPE =V_BEGIN_END_TYPE
               AND T.SUMMARY_TYPE = 3
               AND T.STATISTICS_TYPE = DATATYPE;
               COMMIT;
            --循环计数加一，1，需求计划申报 2，物流处置，3，生产运输，4，物资到货
          V_CURENT_STATUE := V_CURENT_STATUE + 1;
          END LOOP;
            --循环计数加一，1-按开始 2-按结束
          V_BEGIN_END_TYPE := V_BEGIN_END_TYPE + 1;
        END LOOP;
        /*************************************************抽取各单位统计数据（页面底部列表）_结束****************************************/
        
        
        
        /*************************************************点击已到货节点为0统计（页面底部列表）_开始*************************************/
        IF DATATYPE = 1 THEN
          UPDATE OMS_REQ_STATUS_STATISTICS T
          SET (REQUIRE_TOTAL,NO_OVERTIME_NUM,OVERTIME_NUM,AVERAGE_TIME,STANDARD_TIME,MODIFIER_ID,MODIFIER_NAME,MODIFY_DATE) = 
          (SELECT
           T1.REQUIRE_TOTAL, --需求条目总数
           T1.NO_OVERTIME_NUM, --未超时条目数
           T1.REQUIRE_TOTAL - T1.NO_OVERTIME_NUM OVERTIME_NUM, --超时条目数
           DECODE(T1.REQUIRE_TOTAL,0,0,T1.TOTALTIME/T1.REQUIRE_TOTAL) AVERAGE_TIME, --平均时长，总时长除以总条数
           NULL STANDARD_TIME,--不需要标准
           T1.MODIFIER_ID, --修改人ID
           T1.MODIFIER_NAME, --修改人名称
           T1.MODIFY_DATE --修改时间
          FROM (
            SELECT COUNT(1) REQUIRE_TOTAL, --需求条目总数
                  NVL(SUM(CASE                 --未超时（物资从开始到结束总时长与四个节点标准时常相比较）正常情况下到货的物资不会存在没有结束时间
                    WHEN DECODE(TR.MATERIALS_ARRIVAL_END_TIME,
                      NULL,
                      ROUND(TO_NUMBER(CURRENT_TIME - TR.REPLAN_BEGIN_TIME), 2) -
                      (TR.REPLAN_STANDARETIME + TR.LOGISTICS_DEAL_STANDARETIME + TR.TRANSPORT_STANDARETIME + TR.MATERIALS_ARRIVAL_STANDARETIME),
                      ROUND(TO_NUMBER(TR.MATERIALS_ARRIVAL_END_TIME - TR.REPLAN_BEGIN_TIME), 2) -
                      (TR.REPLAN_STANDARETIME + TR.LOGISTICS_DEAL_STANDARETIME + TR.TRANSPORT_STANDARETIME + TR.MATERIALS_ARRIVAL_STANDARETIME)) <= 0 THEN 1
                    ELSE 0 END),0) NO_OVERTIME_NUM, --未超时条目数
                  NVL(SUM(NVL(DECODE(TR.MATERIALS_ARRIVAL_END_TIME, NULL,
                    ROUND(TO_NUMBER(CURRENT_TIME - TR.REPLAN_BEGIN_TIME), 2),
                    ROUND(TO_NUMBER(TR.MATERIALS_ARRIVAL_END_TIME - TR.REPLAN_BEGIN_TIME), 2)), 0)),0) TOTALTIME, --总时长
                  SYSDATE CREATE_DATE, --创建时间
                  'SuperAdmin' MODIFIER_ID, --修改人ID
                  'SuperAdmin' MODIFIER_NAME, --修改人名称
                  SYSDATE MODIFY_DATE --修改时间
             FROM OMS_REQUIREMENT_PROCESS_TRACE TR,
                  OMS_MATERIAL_REQUIREMENT_INFO TI
            WHERE TR.OMS_MATERIAL_REQUIREMENTINFOID =
                  TI.OMS_MATERIAL_REQUIREMENTINFOID(+)
              AND TR.RECEIPT_STATUS = 0 --已到货
              AND TR.MONITOR_DATE = CURRENT_TIME --统计当天抽取的数据
              AND TI.CREATE_DATE = CURRENT_TIME
              AND TRUNC(TR.MATERIALS_ARRIVAL_END_TIME) = TO_DATE(ENDTIME, 'yyyy-mm-dd')--统计当天到货
              AND TRUNC(TR.MATERIALS_ARRIVAL_END_TIME) = TO_DATE(STARTTIME, 'yyyy-mm-dd')--统计当天到货
              AND TI.REQUIREMENT_DEPT_CODE LIKE DECODE(V_DEPT_CODE, '00000000', '09', V_DEPT_CODE) || '%' --编码前缀匹配叶子（子集）集合数据
              --AND LENGTH(TI.REQUIREMENT_DEPT_CODE) = DECODE(V_DEPT_CODE, '00000000', 6, LENGTH(TI.REQUIREMENT_DEPT_CODE)) --为了处理深圳供电局只往下统计一层
          ) T1)
          WHERE T.CURENT_STATUE = 0
           AND T.REQUIREMENT_DEPT_CODE = V_DEPT_CODE
           AND T.MONITOR_DATE = CURRENT_TIME
           AND T.RECEIPT_STATUS = 1
           AND T.BEGIN_END_TYPE =2--按结束
           AND T.SUMMARY_TYPE = 3
           AND T.STATISTICS_TYPE = DATATYPE;--当天
           COMMIT;
        END IF;
        /*********************************************点击已到货节点为0统计（页面底部列表）_结束*******************************/
        
    END LOOP;--游标循环
  CLOSE CURSOR_DEPT;--游标结束
  
  
  /*********************************************抽取各单位四个节点统计数据（页面中部）_开始*********************************/
  INSERT INTO OMS_REQ_STATUS_STATISTICS
    SELECT SYS_GUID() ID, --唯一标识符ID
         REQUIREMENT_DEPT_ID, --申购部门ID
         REQUIREMENT_DEPT_CODE, --申购部门编码
         REQUIREMENT_DEPT_NAME, --申购部门名称
         MONITOR_DATE, --监控日期
         RECEIPT_STATUS, --是否已入库（该值必须有，默认0未到货 1已到货）
         SUM(NVL(REQUIRE_TOTAL,0)) REQUIRE_TOTAL, --需求条目总数
         SUM(NVL(NO_OVERTIME_NUM,0)) NO_OVERTIME_NUM, --未超时条目数
         SUM(NVL(OVERTIME_NUM,0)) OVERTIME_NUM, --超时条目数
         DECODE(SUM(NVL(REQUIRE_TOTAL,0)),0,0,SUM(NVL(AVERAGE_TIME,0)) / SUM(NVL(REQUIRE_TOTAL,0))) AVERAGE_TIME, --平均时长
         CASE
            WHEN CURENT_STATUE = 1 THEN V_REPLANSTANDARETIME
            WHEN CURENT_STATUE = 2 THEN V_LOGISTICSDEALSTANDARETIME
            WHEN CURENT_STATUE = 3 THEN V_TRANSPORTSTANDARETIME
            WHEN CURENT_STATUE = 4 THEN V_MATERIALSARRIVALSTANDARETIME
         END STANDARD_TIME, --标准时长
         STATISTICS_TYPE, --统计时间段类型（1，当天2，本月3，本季度4，本年，5本12年）
         CURENT_STATUE, --当前所处流程节点（1，需求计划申报 2，物流处置，3，生产运输，4，物资到货）
         'SuperAdmin' CREATOR_ID, --创建人ID
         'SuperAdmin' CREATOR_NAME, --创建人名称
         SYSDATE CREATE_DATE, --创建时间
         'SuperAdmin' MODIFIER_ID, --修改人ID
         'SuperAdmin' MODIFIER_NAME, --修改人名称
         SYSDATE MODIFY_DATE, --修改时间
         BEGIN_END_TYPE, --开始结束时间类型（1，按开始时间  2按结束时间）
         2 SUMMARY_TYPE --统计类型：1、总量统计；2、节点统计；3、区局统计
    FROM OMS_REQ_STATUS_STATISTICS
    WHERE MONITOR_DATE = CURRENT_TIME
     AND RECEIPT_STATUS = 1
     AND CURENT_STATUE IN (1,2,3,4)--排除节点为0数据
     AND STATISTICS_TYPE = DATATYPE
     AND SUMMARY_TYPE = 3
     GROUP BY MONITOR_DATE,
        CURENT_STATUE,
        SUMMARY_TYPE,
        REQUIREMENT_DEPT_CODE, 
        REQUIREMENT_DEPT_ID,
        REQUIREMENT_DEPT_NAME,
        RECEIPT_STATUS,
        BEGIN_END_TYPE,
        STATISTICS_TYPE;
   COMMIT;
  /*******************************************抽取各单位四个节点统计数据（页面中部）_结束***********************************/
  
  IF DATATYPE = 1 THEN
    /**
    ** 删除已到货当天按开始时间统计的数据，当天到货只有按结束的统计
    */
    DELETE FROM OMS_REQ_STATUS_STATISTICS T WHERE T.RECEIPT_STATUS = 1 AND T.MONITOR_DATE = CURRENT_TIME AND STATISTICS_TYPE = 1 AND BEGIN_END_TYPE <> 2;
    COMMIT;
    
    /**
    ** 最顶层的统计数据只保留当天的数据
    */
    INSERT INTO OMS_REQ_STATUS_STATISTICS
      SELECT SYS_GUID() ID, --唯一标识符ID
           REQUIREMENT_DEPT_ID, --申购部门ID
           REQUIREMENT_DEPT_CODE, --申购部门编码
           REQUIREMENT_DEPT_NAME, --申购部门名称
           MONITOR_DATE, --监控日期
           RECEIPT_STATUS, --是否已入库（该值必须有，默认0未到货 1已到货）
           SUM(NVL(REQUIRE_TOTAL,0)) REQUIRE_TOTAL, --需求条目总数
           SUM(NVL(NO_OVERTIME_NUM,0)) NO_OVERTIME_NUM, --未超时条目数
           SUM(NVL(OVERTIME_NUM,0)) OVERTIME_NUM, --超时条目数
           DECODE(SUM(NVL(REQUIRE_TOTAL,0)),0,0,SUM(NVL(AVERAGE_TIME,0)) / SUM(NVL(REQUIRE_TOTAL,0))) AVERAGE_TIME, --平均时长
           NULL STANDARD_TIME,--不需要标准
           STATISTICS_TYPE, --统计时间段类型（1，当天2，本月3，本季度4，本年，5本12年）
           CURENT_STATUE, --此值为0，顶部汇总节点为0
           'SuperAdmin' CREATOR_ID, --创建人ID
           'SuperAdmin' CREATOR_NAME, --创建人名称
           SYSDATE CREATE_DATE, --创建时间
           'SuperAdmin' MODIFIER_ID, --修改人ID
           'SuperAdmin' MODIFIER_NAME, --修改人名称
           SYSDATE MODIFY_DATE, --修改时间
           BEGIN_END_TYPE, --开始结束时间类型（1，按开始时间  2按结束时间）
           1 SUMMARY_TYPE --统计类型：1、总量统计；2、节点统计；3、区局统计
      FROM OMS_REQ_STATUS_STATISTICS
      WHERE MONITOR_DATE = CURRENT_TIME
       AND RECEIPT_STATUS = 1--已到货
       AND CURENT_STATUE = 0--最顶层数据只需要汇总节点为0的数据
       AND STATISTICS_TYPE = DATATYPE--只有当天的时候会执行这一段脚本
       AND SUMMARY_TYPE = 3--根据第三层数据进行汇总
       AND BEGIN_END_TYPE = 2--按结束
       GROUP BY MONITOR_DATE,
          CURENT_STATUE,
          SUMMARY_TYPE,
          REQUIREMENT_DEPT_CODE, 
          REQUIREMENT_DEPT_ID,
          REQUIREMENT_DEPT_NAME,
          RECEIPT_STATUS,
          BEGIN_END_TYPE,
          STATISTICS_TYPE;
    COMMIT;
  END IF;
  
END;