db2存储过程

CREATE PROCEDURE TSETC.SPLIT_ANDY(IN pro_date DATE)
    RESULT SETS 1
    LANGUAGE SQL
 P1:BEGIN
    ----声明临时表SPM_BAK用来存储总中心封账日期及封账后的交易MID
   DECLARE GLOBAL TEMPORARY TABLE SESSION.SPM_BAK
  (
  NEWSHIFT_DATE Date,
     MID VARCHAR(50)
   )
    WITH REPLACE
    NOT LOGGED;
    ----将该封账日期和该天的交易数据向临时表中写入 
    INSERT INTO SESSION.SPM_BAK
    SELECT NEWSHIFT_DATE,MID FROM ECDBA.SPM_FOR_SHIFT_DETAIL_BAK
    WHERE NEWSHIFT_DATE=pro_date;
      ----声明临时表STATUS用来存储总中心封账后的交易信息
   P2:BEGIN
    DECLARE GLOBAL TEMPORARY TABLE SESSION.STATUS
    (
    MID VARCHAR(50),
    PROCESS_RESULT INTEGER,
    NEWSHIFT_DATE DATE,
    PASSED_TYPE INTEGER,
    CASH DECIMAL(15,2),
   VEHCLASS INTEGER,
    CARD_NET_ID VARCHAR(10),
   PLAZAID INTEGER,
   WORK_MODE INTEGER,
   VEHSTATUS INTEGER
    )
     WITH REPLACE
     NOT LOGGED;
      ----将相关联的数据信息插入到该表中
     INSERT INTO SESSION.STATUS
     SELECT ST.MID,ST.PROCESS_RESULT,SESSION.SPM_BAK.NEWSHIFT_DATE,
     ST.PASSED_TYPE,ST.CASH,ST.VEHCLASS,ST.CARD_NET_ID,ST.PLAZAID,ST.WORK_MODE,ST.VEHSTATUS
     FROM SESSION.SPM_BAK
     INNER JOIN
     (
      SELECT MID,PROCESS_RESULT,NEWSHIFT_DATE,PASSED_TYPE,CASH,VEHCLASS,CARD_NET_ID,PLAZAID,WORK_MODE,VEHSTATUS
      FROM ECDBA.BLS_DATA_STATUS_N
      WHERE PROCESS_RESULT IN (0,2) AND WORK_MODE IN(0,100) AND ENTRY_EXIT=1
     )AS ST
     ON SESSION.SPM_BAK.MID=ST.MID;
     END P2;
     P3:BEGIN
     -----根据上述所有条件汇总出所有的拆分信息,然后插入到ECDBA.TBL_SPLIT_PRECINCT4_V3数据表中
    INSERT INTO ECDBA.TBL_SPLIT_PRECINCT4_V3
    SELECT A95.PRECINCT_ID,PASSED_TYPE,CASH,VEHCLASS,SUM(SDETAIL.SPLIT_REAL) AS FEE,
     A95.PRECINCT_NAME,COUNT(DISTINCT SDETAIL.MID) AS FLUX, NEWSHIFT_DATE,CARD_NET_ID
     FROM SESSION.STATUS
     INNER JOIN
     (
    SELECT MID,SPLIT_REAL,PLAZAID,SECTION_ID FROM ECDBA.SPLIT_CASH_DETAIL
     ) AS SDETAIL
    ON SESSION.STATUS.MID=SDETAIL.MID
    LEFT JOIN
    (
    SELECT SECTION_ID,PRECINCT_ID FROM DB2INST1.A60_MIN_SECTION
    UNION ALL
    SELECT SECTION_ID,PRECINCT_ID FROM DB2INST1.A60_MIN_SECTION_ANDY
    ) AS A60
    ON SDETAIL.SECTION_ID=A60.SECTION_ID
    LEFT JOIN
    (
    SELECT PRECINCT_ID,PRECINCT_NAME FROM DB2INST1.A95_ORGAN_PRECINCT
    UNION ALL
    SELECT PRECINCT_ID,PRECINCT_NAME FROM DB2INST1.A95_ORGAN_PRECINCT_ANDY
    ) AS A95
    ON A60.PRECINCT_ID=A95.PRECINCT_ID
    GROUP BY A95.PRECINCT_ID,A95.PRECINCT_NAME,PASSED_TYPE,CASH,VEHCLASS,CARD_NET_ID,NEWSHIFT_DATE;
     END P3;
    -----此处是用来处理不可拆分的数据信息
    P4:BEGIN
    INSERT INTO ECDBA.TBL_SPLIT_PRECINCT4_V3
          SELECT -1 AS PRECINCT_ID,STATUS4.PASSED_TYPE,STATUS4.CASH,STATUS4.VEHCLASS,SUM(STATUS4.CASH)/100 AS FEE,
    '未拆分' AS PRECINCT_NAME,COUNT(DISTINCT STATUS4.MID) AS FLUX,pro_date,STATUS4.CARD_NET_ID
          FROM
    (
          SELECT MID
          FROM ECDBA.SPRE_FOR_DATA
          WHERE SPLIT_FLAG=1 OR SPLIT_FLAG=3
          ) AS DATA
          JOIN
    SESSION.STATUS AS STATUS4
          ON DATA.MID=STATUS4.MID
          GROUP BY STATUS4.PASSED_TYPE,STATUS4.VEHCLASS,STATUS4.NEWSHIFT_DATE,STATUS4.CARD_NET_ID,STATUS4.CASH;
    END P4;
    P5:BEGIN
    ------首发公司和京承二期已拆分交易的车流量合计
    INSERT INTO ECDBA.TBL_SPLIT_FLUX_BY_COMPANY4_V3
          SELECT pro_date,STATUS5.PASSED_TYPE,STATUS5.VEHCLASS,STATUS5.VEHSTATUS,STATUS5.CARD_NET_ID,
          ORGAN1.VERIFYSUPER_ID AS Highway_ID,COUNT(DISTINCT STATUS5.MID) AS SUM_FLUX,STATUS5.CASH,STATUS5.WORK_MODE
          FROM
    SESSION.STATUS AS STATUS5
          JOIN ECDBA.SPLIT_CASH_DETAIL AS DETAIL
          ON DETAIL.MID=STATUS5.MID
          LEFT JOIN DB2INST1.A36_ORGAN AS ORGAN1
          ON ORGAN1.ORGAN_ID=STATUS5.PLAZAID
          GROUP BY STATUS5.NEWSHIFT_DATE,STATUS5.PASSED_TYPE,STATUS5.VEHCLASS,STATUS5.VEHSTATUS,STATUS5.CARD_NET_ID,
    ORGAN1.VERIFYSUPER_ID,STATUS5.CASH,STATUS5.WORK_MODE;
    END P5;
    -----过车流量总计，包括已拆分的和未拆分的,precinct_id=2表示所有过车流量
    P6:BEGIN
    INSERT INTO ECDBA.TBL_SPLIT_FLUX_BY_COMPANY4_V3
    SELECT pro_date,STATUS6.PASSED_TYPE,STATUS6.VEHCLASS,STATUS6.VEHSTATUS,STATUS6.CARD_NET_ID,
    2,COUNT(DISTINCT STATUS6.MID) AS SUM_FLUX,STATUS6.CASH,STATUS6.WORK_MODE 
    FROM
    SESSION.STATUS AS STATUS6
    LEFT JOIN DB2INST1.A36_ORGAN AS A36
    ON A36.ORGAN_ID=STATUS6.PLAZAID 
    GROUP BY STATUS6.NEWSHIFT_DATE,STATUS6.PASSED_TYPE,STATUS6.VEHCLASS,STATUS6.VEHSTATUS,STATUS6.CARD_NET_ID,
    A36.VERIFYSUPER_ID,STATUS6.WORK_MODE,STATUS6.CASH;
    END P6;
END P1;