1、包头

?

CREATE OR REPLACE PACKAGE PKG_DATA_CZWCOUNT as
  procedure PRO_QZLD_CZW_DELETE;            --清空统计需要的基础表数据，为导入新的数据做准备
  procedure PRO_QZLD_CZW_IMPORT;            --导入数据
  procedure PRO_CZW_FW_TJ;                  --定时分析数据统计
  procedure PRO_CZW_FW_CZWRY_TJFX;          --将统计分析的数据分析一次        
  procedure PRO_CZW_ALL;
end;

?

?

2、包体

CREATE OR REPLACE PACKAGE BODY PKG_DATA_CZWCOUNT is
  /*
   * 清空统计需要的基础表数据，为导入新的数据做准备
   */
  PROCEDURE PRO_QZLD_CZW_DELETE as
  begin
    --删除索引
    execute immediate 'drop index index_ry_houseid'; 
    execute immediate 'drop index index_ry_updatetime'; 
    execute immediate 'drop index index_ry_cardno';
      
    execute immediate 'drop index index_fw_id';
    
    execute immediate 'drop index index_czw_fw_tj_tjsj';
    execute immediate 'drop index index_czw_fw_tj_tjsj_tjsq';
    
    execute immediate 'drop index index_czw_fw_czwry_tjfx_time';
    
    --清空数据
    execute immediate 'truncate table MV_CZW_LY';            
    execute immediate 'truncate table MV_CZW_FW';
    execute immediate 'truncate table MV_CZW_RY';
  end;
    
  /*
   * 导入数据
   */
  PROCEDURE PRO_QZLD_CZW_IMPORT as
  begin
    --导入楼宇数据
    insert into mv_czw_ly
      (OBJECTID,
       ID,
       CODE,
       COMM_ID,
       SQNAME,
       POLICE,
       SSPCS,
       SUBSTATION,
       SUBSTATION_NAME,
       POINT_X,
       POINT_Y,
       SHAPE,
       ZY_RKSJ,
       REDFLAG)
      SELECT "QZYDJW_LY"."OBJECTID"        "OBJECTID",
             "QZYDJW_LY"."ID"              "ID",
             "QZYDJW_LY"."CODE"            "CODE",
             "QZYDJW_LY"."COMM_ID"         "COMM_ID",
             "QZYDJW_LY"."SQNAME"          "SQNAME",
             "QZYDJW_LY"."POLICE"          "POLICE",
             "QZYDJW_LY"."SSPCS"           "SSPCS",
             "QZYDJW_LY"."SUBSTATION"      "SUBSTATION",
             "QZYDJW_LY"."SUBSTATION_NAME" "SUBSTATION_NAME",
             "QZYDJW_LY"."POINT_X"         "POINT_X",
             "QZYDJW_LY"."POINT_Y"         "POINT_Y",
             "QZYDJW_LY"."SHAPE"           "SHAPE",
             "QZYDJW_LY"."ZY_RKSJ"         "ZY_RKSJ",
             "QZYDJW_LY"."REDFLAG"         "REDFLAG"
        FROM "QZYDJW_LY"@CZW_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM "QZYDJW_LY"
       WHERE "QZYDJW_LY"."POLICE" = '440306900000'
          OR "QZYDJW_LY"."POLICE" = '440306780000'
          OR "QZYDJW_LY"."POLICE" = '440306790000'
          OR "QZYDJW_LY"."POLICE" = '440306800000'
          OR "QZYDJW_LY"."POLICE" = '440306810000'
          OR "QZYDJW_LY"."POLICE" = '440306830000'
          OR "QZYDJW_LY"."POLICE" = '440306820000'
          OR "QZYDJW_LY"."POLICE" = '440306840000'
          OR "QZYDJW_LY"."POLICE" = '440306850000'
          OR "QZYDJW_LY"."POLICE" = '440306860000'
          OR "QZYDJW_LY"."POLICE" = '440306870000';
    commit;
  
    --导入房屋数据
    insert into MV_CZW_FW
      (ID,
       HOUSEUSAGE,
       BUILDINGID,
       BUILDINGNAME,
       HOUSEDESIGNUSEID,
       HOUSEPHOTO,
       CODE,
       HOUSESTAIR,
       NAME,
       HOUSECERTIFICATIONID,
       HOUSECERTIFICATION,
       CERTIFICATIONADDRESS,
       MANAGETYPE,
       HIDDENDANGERTYPE,
       BOOKERID,
       REGISTRAR,
       REGISTERTIME,
       USERID,
       USERNAME,
       HOUSEADDRESS,
       HOUSENAME,
       HOUSETYPEID,
       HOUSESTRUCTRUE,
       REMARKS,
       HOUSELINKMANID,
       HOUSELINKMAN,
       HOUSELINKMANADDRESS,
       HOUSELINKMANTEL,
       AREAID,
       AGENTID,
       AGENTNAME,
       AGENTADDRESS,
       AGENTTEL,
       AGENTSTARTDATE,
       AGENTENDDATE,
       CREATOR,
       CREATETIME,
       UPDATETIME,
       UPDATER,
       ISVALID,
       HOUSETENANCYAREA,
       OWNERID,
       OWNERNAME,
       OWNERADDRESS,
       BUILDINGADDRESS,