临时取数脚本

--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;