临时取数脚本

--01 删除表中的现有数据
TRUNCATE TABLE TEMP_WAYBILL_CYX;
--02将附件中的运单号拷贝到TEMP_WAYBILL_CYX 表中
--03 插入过度表值
DECLARE
  A DATE := DATE '2016-02-26';
  B DATE := DATE '2016-02-26';
  C DATE := DATE '2016-02-26';
  D DATE := DATE '2016-02-27';
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_SRCZONECODE_CYX';
  FOR Q IN 1 .. 15 LOOP
    INSERT INTO TEMP_SRCZONECODE_CYX
      (WAYBILL_NO, SRC_ZONE_CODE, DEST_ZONE_CODE)
      SELECT /*+ PARALLEL(T,5) */
       T.WAYBILL_NO, T.SRC_ZONE_CODE, T.DEST_ZONE_CODE
        FROM TT_WAYBILL_INFO T
       WHERE EXISTS
       (SELECT 1 FROM TEMP_WAYBILL_CYX WHERE WAYBILL_NO = T.WAYBILL_NO)
         AND T.REPORT_DT = A;
    COMMIT;
    A := A + 1;
  END LOOP;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_RESULT_CYX';
  FOR Q IN 1 .. 15 LOOP
    INSERT INTO TEMP_RESULT_CYX
      (WAYBILL_NO,
       SRC_ZONE_CODE,
       DEST_ZONE_CODE,
       ROUTE_INDEX,
       BATCH_TYPE,
       S_ZONE_CODE,
       S_BATCH_CODE,
       S_OPT_TM,
       S_DELIVERY_TM,
       D_ZONE_CODE,
       D_BATCH_CODE,
       D_OPT_TM,
       D_DELIVERY_TM,
       D_S_OPT,
       D_S_DIFF,
       D_VEHICLE_NO,
       VEHICLE_AREA,
       D_BAR_OPT_CODE,
       STAY_WHY_NAME,
       EXCEPTION_STATUS,
       DUTY_STATUS)
      SELECT /*+ INDEX(IPK_WAYBILL_ROUTE_INFO) parallel(DT,5)*/
             DT.WAYBILL_NO AS 运单号,
             C.SRC_ZONE_CODE AS 始发网点,
             C.DEST_ZONE_CODE AS 目的网点,
             DT.ROUTE_INDEX AS 路由序号,
             (CASE DT.D_BATCH_TYPE
               WHEN 1 THEN
                '收件班次'
               WHEN 2 THEN
                '收件仓库班次'
               WHEN 3 THEN
                '中转班次'
               WHEN 4 THEN
                '运输班次'
               WHEN 5 THEN
                '派件仓库班次'
               WHEN 6 THEN
                '派件班次'
               ELSE
                ''
             END) AS 班次类型,
             DT.S_ZONE_CODE AS 静态网点,
             DT.S_BATCH_CODE AS 静态班次,
             DT.S_OPT_TM AS 静态操作时间,
             DT.S_DELIVERY_TM AS 静态派送时效,
             DT.D_ZONE_CODE AS 实际网点,
             DT.D_BATCH_BATCH_CODE AS 实际班次,
             DT.D_OPT_TM AS 动态操作时间,
             DT.D_DELIVERY_TM AS 动态派送时效,
             NVL(ROUND(TO_NUMBER(DT.D_OPT_TM - DT.S_OPT_TM) * 1440), 0) || 'm' AS 时差,
             NVL(ROUND(TO_NUMBER(DT.S_DELIVERY_TM - DT.D_DELIVERY_TM) * 1440),
                 0) || 'm' AS 时效差值,
             DT.D_VEHICLE_NO AS 车标号,
             '',
             DT.D_BAR_OPT_CODE AS 操作员,
             S.STAY_WHY_NAME AS 异常原因,
             T.EXCEPTION_NAME AS 异常状态,
             (CASE DT.DUTY_STATUS
               WHEN '0' THEN
                ''
               WHEN '1' THEN
                '仓库发件晚点'
               WHEN '2' THEN
                '中转发件晚点'
               WHEN '3' THEN
                '运输到达晚点'
               WHEN '4' THEN
                '仓库出仓晚点'
               WHEN '5' THEN
                '延误派送'
               ELSE
                '其他'
             END) AS 定责状态
        FROM TT_WAYBILL_ROUTE_INFO DT
        LEFT JOIN ompweb.Tm_Bar_Stay_Why_Code S
          ON DT.EXCEPTION_REASON = S.STAY_WHY_CODE
        LEFT JOIN TEMP_EXCEPTION_STATUS T
          ON NVL(T.EXCEPTION_STATUS, 0) =
             SUBSTR(DT.EXCEPTION_STATUS,
                    1,
                    INSTR(DT.EXCEPTION_STATUS || ',', ',') - 1)
       INNER JOIN TEMP_SRCZONECODE_CYX C
          ON DT.WAYBILL_NO = C.WAYBILL_NO
       WHERE DT.REPORT_DT = B;
    COMMIT;
    B := B + 1;
  END LOOP;
 
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVE_PLATENUM';
  FOR Q IN 1 .. 15 LOOP
    INSERT INTO TEMP_CONVE_PLATENUM
      (VEHICLE_AREA, PLATE_NUM)
      SELECT T.VEHICLE_AREA, T.PLATE_NUM
        FROM TT_CONVEYANCE_SHOW T
       WHERE T.PLAN_DATE >= C
         AND T.PLAN_DATE < D;
    COMMIT;
    C := C + 1;
    D := D + 1;
  END LOOP;
 
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONVE_VEHICLEAREA';
  INSERT INTO TEMP_CONVE_VEHICLEAREA
    (VEHICLE_AREA, PLATE_NUM, RN)
    SELECT *
      FROM (SELECT T.VEHICLE_AREA,
                   T.PLATE_NUM,
                   ROW_NUMBER() OVER(PARTITION BY PLATE_NUM ORDER BY PLATE_NUM) RN
              FROM TEMP_CONVE_PLATENUM T)
     WHERE RN = 1;
  COMMIT;
END;

--05 修改车辆管理区
MERGE INTO TEMP_RESULT_CYX T
  USING TEMP_CONVE_VEHICLEAREA R
  ON (T.D_VEHICLE_NO = R.PLATE_NUM)
  WHEN MATCHED THEN
    UPDATE SET T.VEHICLE_AREA = R.VEHICLE_AREA;
  COMMIT;

--06 查询结果给用户
SELECT WAYBILL_NO       AS 运单号,
       SRC_ZONE_CODE    AS 始发网点,
       DEST_ZONE_CODE   AS 目的网点,
       ROUTE_INDEX      AS 路由序号,
       BATCH_TYPE       AS 班次类型,
       S_ZONE_CODE      AS 静态网点,
       S_BATCH_CODE     AS 静态班次,
       S_OPT_TM         AS 静态操作时间,
       S_DELIVERY_TM    AS 静态派送时效,
       D_ZONE_CODE      AS 实际网点,
       D_BATCH_CODE     AS 实际班次,
       D_OPT_TM         AS 动态操作时间,
       D_DELIVERY_TM    AS 动态派送时效,
       D_S_OPT          AS 时差,
       D_S_DIFF         AS 时效差值,
       D_VEHICLE_NO     AS 车标号,
       VEHICLE_AREA     AS 车辆管理方,
       D_BAR_OPT_CODE   AS 操作员,
       STAY_WHY_NAME    AS 异常原因,
       EXCEPTION_STATUS AS 异常状态,
       DUTY_STATUS      AS 定责状态
  FROM TEMP_RESULT_CYX
ORDER BY WAYBILL_NO, ROUTE_INDEX;