臨時取數指令碼

--01建立暫存資料表 
CREATE TABLE TEMP_CONVEYANCE_ZQR( 
WAYBILL_NO VARCHAR2(20), 
SRC_ZONE_CODE VARCHAR2(20), 
CONVEYANCE_NAME VARCHAR2(50), 
SRC_CONVEYANCE_KEY VARCHAR2(100) 
); 
CREATE TABLE TEMP_TRANSFER_ROUTE( 
SRC_CONVEYANCE_KEY VARCHAR2(100), 
CONVEYANCE_NAME VARCHAR2(50), 
WAYBILL_NO VARCHAR2(20), 
D_OPT_TM   DATE, 
D_BATCH_CODE VARCHAR2(50), 
D_OPT_TYPE VARCHAR2(10), 
D_ZONE_CODE VARCHAR2(20) 
); 
CREATE TABLE TEMP_CONVEYANCE_ARRIVE( 
SRC_CONVEYANCE_KEY VARCHAR2(100), 
CONVEYANCE_NAME VARCHAR2(50), 
WAYBILL_NO VARCHAR2(20), 
D_OPT_TM   DATE, 
D_BATCH_CODE VARCHAR2(50), 
D_OPT_TYPE VARCHAR2(10) 
); 
CREATE TABLE TEMP_TRANSFER_ARRIVE( 
SRC_CONVEYANCE_KEY VARCHAR2(100), 
CONVEYANCE_NAME VARCHAR2(50), 
WAYBILL_NO VARCHAR2(20), 
D_OPT_TM   DATE, 
D_BATCH_CODE VARCHAR2(50), 
D_OPT_TYPE VARCHAR2(10) 
); 
CREATE TABLE TEMP_TRANSFER_DEPART( 
SRC_CONVEYANCE_KEY VARCHAR2(100), 
CONVEYANCE_NAME VARCHAR2(50), 
WAYBILL_NO VARCHAR2(20), 
D_OPT_TM   DATE, 
D_BATCH_CODE VARCHAR2(50), 
D_OPT_TYPE VARCHAR2(10) 
); 
CREATE TABLE TEMP_CONVEYANCE_PICKUP( 
SRC_CONVEYANCE_KEY VARCHAR2(100), 
CONVEYANCE_NAME VARCHAR2(50), 
WAYBILL_NO VARCHAR2(20), 
D_OPT_TM   DATE, 
D_BATCH_CODE VARCHAR2(50), 
D_OPT_TYPE VARCHAR2(10) 
); 
CREATE TABLE TEMP_RESULT_ZQR( 
PLAN_DATE DATE, 
CONVEYANCE_NAME VARCHAR2(50), 
EFFICIENCY_TYPE VARCHAR2(10), 
CONVEYANCE_TYPE VARCHAR2(10), 
SRC_AREA_CODE   VARCHAR2(10), 
SRC_ZONE_CODE   VARCHAR2(10), 
DEST_AREA_CODE  VARCHAR2(10), 
DEST_ZONE_CODE  VARCHAR2(10), 
ACTUAL_DEPART_TM DATE, 
DEPART_BATCH_CODE VARCHAR2(20), 
ARRIVE_BATCH_CODE VARCHAR2(20), 
ARRIVE_BATCH_TIME DATE, 
TRANSFER_DEPART_CODE VARCHAR2(20), 
TRANSFER_DEPART_TIME DATE, 
TRANSFER_ARRIVE_CODE VARCHAR2(20), 
TRANSFER_ARRIVE_TIME DATE, 
PICK_BATCH_CODE VARCHAR2(20), 
SRC_CONVEYANCE_KEY VARCHAR2(100), 
WAYBILL_NO VARCHAR2(20) 
); 
--01刪除表 
truncate table TEMP_CONVEYANCE_ZQR; 
truncate table TEMP_TRANSFER_ROUTE; 
truncate table TEMP_CONVEYANCE_ARRIVE; 
truncate table TEMP_TRANSFER_DEPART; 
truncate table TEMP_TRANSFER_ARRIVE; 
truncate table TEMP_CONVEYANCE_PICKUP; 
truncate table TEMP_RESULT_ZQR; 
--02查詢滿足條件的運單號 
INSERT INTO TEMP_CONVEYANCE_ZQR 
  (WAYBILL_NO, SRC_ZONE_CODE, CONVEYANCE_NAME, SRC_CONVEYANCE_KEY) 
  SELECT /*+ USE_HASH(S,D) FULL(D)*/ 
   D.BILL_NO, S.SRC_ZONE_CODE, S.CONVEYANCE_NAME, S.SRC_CONVEYANCE_KEY 
    FROM TT_CONVEYANCE_SHOW S, TT_CONVEYANCE_DETAIL_LOAD D 
   WHERE S.SRC_CONVEYANCE_KEY = D.SRC_CONVEYANCE_KEY 
     AND S.EFFICIENCY_TYPE != 3 
     AND S.PLAN_DATE = D.PLAN_DATE 
     AND S.PLAN_DATE >= DATE '2015-05-24' 
     AND S.PLAN_DATE < DATE '2015-05-29'; 
COMMIT; 
--03向中間表插入資料 
INSERT INTO TEMP_TRANSFER_ROUTE 
  (SRC_CONVEYANCE_KEY, 
   CONVEYANCE_NAME, 
   WAYBILL_NO, 
   D_OPT_TM, 
   D_BATCH_CODE, 
   D_OPT_TYPE, 
   D_ZONE_CODE) 
  SELECT /*+ PARALLEL(R,4)*/ 
   W.SRC_CONVEYANCE_KEY, 
   W.CONVEYANCE_NAME, 
   R.WAYBILL_NO, 
   R.D_OPT_TM, 
   R.D_BATCH_BATCH_CODE, 
   R.D_OPT_TYPE, 
   R.D_ZONE_CODE 
    FROM TT_WAYBILL_ROUTE_INFO PARTITION(SYS_P326898) R, 
         TEMP_CONVEYANCE_ZQR   W 
   WHERE R.WAYBILL_NO = W.WAYBILL_NO 
     AND R.D_OPT_TYPE IN ('301', '303', '311', '312', '50', '51') 
  UNION ALL 
  SELECT /*+ PARALLEL(R,4)*/ 
   W.SRC_CONVEYANCE_KEY, 
   W.CONVEYANCE_NAME, 
   R.WAYBILL_NO, 
   R.D_OPT_TM, 
   R.D_BATCH_BATCH_CODE, 
   R.D_OPT_TYPE, 
   R.D_ZONE_CODE 
    FROM TT_WAYBILL_ROUTE_INFO PARTITION(SYS_P328040) R, 
         TEMP_CONVEYANCE_ZQR   W 
   WHERE R.WAYBILL_NO = W.WAYBILL_NO 
     AND R.D_OPT_TYPE IN ('301', '303', '311', '312', '50', '51') 
  UNION ALL 
  SELECT /*+ PARALLEL(R,4)*/ 
   W.SRC_CONVEYANCE_KEY, 
   W.CONVEYANCE_NAME, 
   R.WAYBILL_NO,