Cacher is the code snippet organizer for pro developers

CREATE OR REPLACE PROCEDURE P_GK_FX_TJ_NEWS_LAST_HC IS
  --重点人员个人轨迹信息数据量统计 全量统计
  PROC_KSSJ  DATE; --存储过程开始时间
  V_SQL_STMT VARCHAR2(500);
BEGIN
  --变量赋值
  PROC_KSSJ := SYSDATE;
  BEGIN
     EXECUTE IMMEDIATE 'TRUNCATE TABLE GK_FX_TJ_NEWS_LAST';
    --网吧轨迹
    --插入新增统计重点人员编号
    EXECUTE IMMEDIATE 'TRUNCATE TABLE GK_TJCOUNT_ACT_HCADD';


    INSERT INTO GK_TJCOUNT_ACT_HCADD
      SELECT GLBH, COUNT(1),TO_DATE(TO_CHAR(T.SWSJ,'YYYY-MM-DD'),'YYYY-MM-DD') SJ  FROM GK_GJ_WBGJ T  GROUP BY T.GLBH ,TO_CHAR(T.SWSJ,'YYYY-MM-DD');
    COMMIT;

    INSERT INTO GK_FX_TJ_NEWS_LAST
      (GRBH,HDSJ)
      SELECT  B.GLBH,B.SJ
        FROM GK_TJCOUNT_ACT_HCADD B
       WHERE NOT EXISTS (SELECT 1 FROM GK_FX_TJ_NEWS_LAST S WHERE S.GRBH = B.GLBH AND S.HDSJ=B.SJ) ;
    COMMIT;

    UPDATE GK_FX_TJ_NEWS_LAST S
       SET S.WBGJ = NVL((SELECT NUMVALUES FROM GK_TJCOUNT_ACT_HCADD T WHERE T.GLBH = S.GRBH AND T.SJ=S.HDSJ),0);
    COMMIT;

    --旅馆轨迹
    --插入新增统计重点人员编号
    EXECUTE IMMEDIATE 'TRUNCATE TABLE GK_TJCOUNT_ACT_HCADD';

    INSERT INTO GK_TJCOUNT_ACT_HCADD
           SELECT GLBH, COUNT(1),TO_DATE(TO_CHAR(T.RZSJ,'YYYY-MM-DD'),'YYYY-MM-DD') SJ  FROM GK_GJ_LGGJ T  GROUP BY T.GLBH ,TO_CHAR(T.RZSJ,'YYYY-MM-DD');
    COMMIT;

    INSERT INTO GK_FX_TJ_NEWS_LAST
      (GRBH,HDSJ)
      SELECT B.GLBH,B.SJ
        FROM GK_TJCOUNT_ACT_HCADD B
       WHERE NOT EXISTS (SELECT 1 FROM GK_FX_TJ_NEWS_LAST S WHERE S.GRBH = B.GLBH AND S.HDSJ=B.SJ);
    COMMIT;

    UPDATE GK_FX_TJ_NEWS_LAST S
       SET S.LGGJ = NVL((SELECT NUMVALUES FROM GK_TJCOUNT_ACT_HCADD T WHERE T.GLBH = S.GRBH AND T.SJ=S.HDSJ),0);
    COMMIT;


    --民航信息
    --插入新增统计重点人员编号
    EXECUTE IMMEDIATE 'TRUNCATE TABLE GK_TJCOUNT_ACT_HCADD';

    INSERT INTO GK_TJCOUNT_ACT_HCADD
      SELECT GLBH, COUNT(1),TO_DATE(TO_CHAR(T.LGSJ,'YYYY-MM-DD'),'YYYY-MM-DD') SJ  FROM GK_GJ_MHXX T  GROUP BY T.GLBH ,TO_CHAR(T.LGSJ,'YYYY-MM-DD');
    COMMIT;

    INSERT INTO GK_FX_TJ_NEWS_LAST
      (GRBH,HDSJ)
      SELECT B.GLBH,B.SJ
        FROM GK_TJCOUNT_ACT_HCADD B
       WHERE NOT EXISTS (SELECT 1 FROM GK_FX_TJ_NEWS_LAST S WHERE S.GRBH = B.GLBH AND S.HDSJ=B.SJ);
    COMMIT;

    UPDATE GK_FX_TJ_NEWS_LAST S
       SET S.MHXX = NVL((SELECT NUMVALUES FROM GK_TJCOUNT_ACT_HCADD T WHERE T.GLBH = S.GRBH AND T.SJ=S.HDSJ),0);
    COMMIT;

    
    
    ---更新数据为空的
   --- UPDATE GK_FX_TJ_NEWS_LAST S
    --   SET S.WBGJ = NVL(S.WBGJ,0),
    --   S.LGGJ = NVL(S.LGGJ,0),
    ---   S.MHXX = NVL(S.MHXX,0);
  --   COMMIT;
    --成功时记录日志
    INSERT INTO GK_BD_LOG
      (BD_LOG_CODE, PROC_KSSJ, PROC_JSSJ, BD_JZSJC, CGBZ, PROC_NAME)
    VALUES
      (F_GETSEQ(30), PROC_KSSJ, SYSDATE, SYSDATE, 'Y', 'P_GK_FX_TJ_NEWS_LAST_HC');
    COMMIT;
  END;
EXCEPTION
  WHEN OTHERS THEN
    V_SQL_STMT := 'sqlcode:' || SQLCODE || '###sqlerrm:' || SQLERRM;
    ROLLBACK;
    --失败时记录日志
    INSERT INTO GK_BD_LOG
      (BD_LOG_CODE,
       PROC_KSSJ,
       PROC_JSSJ,
       BD_JZSJC,
       CGBZ,
       ERROR_INFO,
       PROC_NAME)
    VALUES
      (F_GETSEQ(30),
       PROC_KSSJ,
       SYSDATE,
       SYSDATE,
       'N',
       V_SQL_STMT,
       'P_GK_FX_TJ_NEWS_LAST_HC');
    COMMIT;
END P_GK_FX_TJ_NEWS_LAST_HC;