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, BUILDINGAREA, BUILDINGTYPE, CREATORGROUP, DATABEGIN, DATASOURCE, HOUSEWORTH, LASTUPDATEDATE, LASTUPDATOR, MEDIAS, UPDATED, UPDATERGROUP, BUILDINGLAYERS, RESPONSIBILITY, FIRMER, FIRMTIME, HOUSEAREA, LIVEAREA, BUSINESSAREA, OFFICERAREA, FACTORYAREA, STOREAREA, OTHERAREA, OWNAREA, UNKNOWNAREA, HOMEADDRESS2, TEMP_ID, TEMP_AREAID, CONSIGNOR, CERTIFICATIONMAN, DELMARK, UPDATE_KEY, OWNERTEL, PAPERTYPE, CARDNO, COMM_ID, ZY_RKSJ, REDFLAG, OLDCODE, QHAREAID, CONTROLLER, HOUSEDEUSE, AGENTCARDTYPE, AGENTCARDNO, TEMP_BAID, OLDBUILDINGID, OLDBUILDINGCODE, WLGYREMARKS, OLDBUILDINGNAME) SELECT * FROM QZYDJW_FW@CZW_LINK f where f.BUILDINGID in (select b.CODE from qzydjw_ly@CZW_LINK b where b.POLICE in ('440306900000', '440306780000', '440306790000', '440306800000', '440306810000', '440306830000', '440306820000', '440306840000', '440306850000', '440306860000', '440306870000')); commit; --创建房屋索引 execute immediate 'create index index_fw_id on mv_czw_fw(id)'; --导入人员数据 insert into mv_czw_ry (id, nationality, name, alias, cardtypeid, cardno, effectivedate, issuingorgan, sex, nationid, nativeplace, registertype, marryid, registeraddtypeid, edulevelid, politicsid, registeraddress, beliefid, photo, managetype, tradeid, jobid, techtitleid, bidefashion, domiciletype, company, companyaddress, companytel, cpolicestation, tel, mtel, email, intime1, addtime, registrar, remarks, creator, createtime, updatetime, updater, isvalid, leasereasonid, degree, bloodtypeid, escuageid, finishschool, refisteraddresspre, registerplace, speciality, leaserelationid, health, homeaddress, areaid, houseid, diedate, marrydate, rapostalcode, resideinaddress, resideinpostalcode, singlekid, residenttype, height, salary, birthday, updatelivecode, homeaddressnew, housecode, islogout, istransact, transacttime, transactor, isrewriteaddress, rewritetime, rewriteperson, temp_areaid, temp_id, temp_mingzu, letdutybook, dutyname, logoutor, logouttime, temp_photo, isinhabitation, isaddressnot, islessee, leavedate, checkindate, procreatedynamic, contraceptive, cardholders, voucher, vouchercodenum, voucherphone, outreason, newareaid, cardno2, isstat, update_key, rksj, readflag, zy_rksj, qhareaid, temp_hosueid, temp_fanghao, tmep_fh, temp_edit, temp_delete, rksbid) SELECT * from qzydjw_ry@CZW_LINK r where r.HOUSEID in (select f.ID from qzydjw_fw@CZW_LINK f where f.BUILDINGID in (select b.CODE from qzydjw_ly@CZW_LINK b where b.POLICE in ('440306900000', '440306780000', '440306790000', '440306800000', '440306810000', '440306830000', '440306820000', '440306840000', '440306850000', '440306860000', '440306870000'))); commit; --创建人员索引 execute immediate 'create index index_ry_houseid on mv_czw_ry(houseid)'; execute immediate 'create index index_ry_updatetime on mv_czw_ry(updatetime)'; execute immediate 'create index index_ry_cardno on mv_czw_ry(cardno)'; end; /* * 定时分析出租屋人员数据,包括出租屋中的现住人数、三个月以上未更新人员 * 六个月以上未更新人员、一年以上未更新人员、疑似一房多人、疑似一人多房、 * 本月注销的过程语句 * * 统计后的数据插入到czw_fw_tj结果表中。 */ PROCEDURE PRO_CZW_FW_TJ as begin --现居住人数 insert into CZW_FW_TJ (TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ) select l.houseid tjid, l.houseaddress tjmc, l.tjsl tjsl, '1' tjlx, to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj, SEQ_CZW_ID.NEXTVAL id, s.dm tjsq from (select f.houseid houseid, f.houseaddress houseaddress, f.tjsl tjsl, l.comm_id comm_id from (select r.houseid houseid, r.tjsl tjsl, f.buildingid buildingid, f.houseaddress houseaddress from (select houseid, count(1) tjsl from mv_czw_ry where islogout = 0 group by houseid) r, mv_czw_fw f where r.houseid = f.id) f, mv_czw_ly l where f.buildingid = l.code) l, czw_dm_sq s where l.comm_id = s.dm; commit; --三个月以上没更新 insert into CZW_FW_TJ (TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ) select l.houseid tjid, l.houseaddress tjmc, l.tjsl tjsl, '2' tjlx, to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj, SEQ_CZW_ID.NEXTVAL id, s.dm tjsq from (select f.houseid houseid, f.houseaddress houseaddress, f.tjsl tjsl, l.comm_id comm_id from (select r.houseid houseid, r.tjsl tjsl, f.buildingid buildingid, f.houseaddress houseaddress from (select houseid, count(1) tjsl from mv_czw_ry where islogout = 0 and updatetime between (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 180) and (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 90) group by houseid) r, mv_czw_fw f where r.houseid = f.id) f, mv_czw_ly l where f.buildingid = l.code) l, czw_dm_sq s where l.comm_id = s.dm; commit; --六个月以上没更新 insert into CZW_FW_TJ (TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ) select l.houseid tjid, l.houseaddress tjmc, l.tjsl tjsl, '3' tjlx, to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj, SEQ_CZW_ID.NEXTVAL id, s.dm tjsq from (select f.houseid houseid, f.houseaddress houseaddress, f.tjsl tjsl, l.comm_id comm_id from (select r.houseid houseid, r.tjsl tjsl, f.buildingid buildingid, f.houseaddress houseaddress from (select houseid, count(1) tjsl from mv_czw_ry where islogout = 0 and updatetime between (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 365) and (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 180) group by houseid) r, mv_czw_fw f where r.houseid = f.id) f, mv_czw_ly l where f.buildingid = l.code) l, czw_dm_sq s where l.comm_id = s.dm; commit; --一年以上没更新 insert into CZW_FW_TJ (TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ) select l.houseid tjid, l.houseaddress tjmc, l.tjsl tjsl, '4' tjlx, to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj, SEQ_CZW_ID.NEXTVAL id, s.dm tjsq from (select f.houseid houseid, f.houseaddress houseaddress, f.tjsl tjsl, l.comm_id comm_id from (select r.houseid houseid, r.tjsl tjsl, f.buildingid buildingid, f.houseaddress houseaddress from (select houseid, count(1) tjsl from mv_czw_ry where islogout = 0 and updatetime < (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 365) group by houseid) r, mv_czw_fw f where r.houseid = f.id) f, mv_czw_ly l where f.buildingid = l.code) l, czw_dm_sq s where l.comm_id = s.dm; commit; --疑似未注销(一人多房) insert into CZW_FW_TJ (TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ) select r.houseid fwid, f.houseaddress fwdz, r.tjsl tjsl, '5' tjlx, to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj, SEQ_CZW_ID.NEXTVAL id, s.dm tjsq from (select r.houseid, count(1) tjsl from (select cardno from (select cardno, houseid from mv_czw_ry where islogout = 0 group by cardno, houseid) r where (length(cardno) = 15 or length(cardno) = 18) group by cardno having count(1) > 1) tj, mv_czw_ry r where tj.cardno = r.cardno and islogout = 0 group by r.houseid) r, mv_czw_fw f, mv_czw_ly l, czw_dm_sq s where r.houseid = f.id and f.buildingid = l.code and l.comm_id = s.dm; commit; --疑似未注销(一房多人) insert into CZW_FW_TJ (TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ) select l.houseid tjid, l.houseaddress tjmc, l.tjsl tjsl, '6' tjlx, to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj, SEQ_CZW_ID.NEXTVAL id, s.dm tjsq from (select f.houseid houseid, f.houseaddress houseaddress, f.tjsl tjsl, l.comm_id comm_id from (select r.houseid, r.tjsl tjsl, f.buildingid buildingid, f.houseaddress houseaddress from (select houseid, count(1) tjsl from MV_CZW_RY where islogout = 0 group by houseid having count(1) > 1) r, MV_CZW_FW f where r.houseid = f.id) f, mv_czw_ly l where f.buildingid = l.code) l, czw_dm_sq s where l.comm_id = s.dm; commit; --本月注销 insert into CZW_FW_TJ (TJID, TJMC, TJSL, TJLX, TJSJ, ID, TJSQ) select l.houseid tjid, l.houseaddress tjmc, l.tjsl tjsl, '7' tjlx, to_char(trunc(sysdate), 'yyyy-mm-dd') tjsj, SEQ_CZW_ID.NEXTVAL id, s.dm tjsq from (select f.houseid houseid, f.houseaddress houseaddress, l.comm_id comm_id, f.tjsl from (select r.houseid houseid, f.buildingid buildingid, f.houseaddress houseaddress, r.tjsl from (select houseid, count(1) tjsl from mv_czw_ry where islogout = 1 and updatetime > (to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') - 30) group by houseid) r, mv_czw_fw f where r.houseid = f.id) f, mv_czw_ly l where f.buildingid = l.code) l, czw_dm_sq s where l.comm_id = s.dm; commit; --给CZW_FW_TJ表创建索引 execute immediate 'create index index_czw_fw_tj_tjsj on CZW_FW_TJ(tjsj)'; execute immediate 'create index index_czw_fw_tj_tjsj_tjsq on CZW_FW_TJ(tjsj,tjsq)'; end; /* * 定时将czw_fw_tj表,再让照 单位和统计类型分组,分析的结果插入CZW_FW_CZWRY_TJFX表 * 分统计类型tjlx 1:警局 2:社区 */ PROCEDURE PRO_CZW_FW_CZWRY_TJFX as begin --以警局单为位统计数据 insert into czw_fw_czwry_tjfx (dm, mc, xzrs, swgx, lwgx, ynwgx, yrdf, yfdr, byzx, tjsj, tjlx) select s.dm, s.mc, tj.xzrs, tj.swgx, tj.lwgx, tj.ynwgx, tj.yrdf, tj.yfdr, tj.byzx, to_char(trunc(sysdate), 'yyyy-mm-dd'), '1' from (select decode(pcs, null, '440306900000', pcs) pcs, sum(decode(t.tjlx, 1, t.tjsl, 0)) xzrs, sum(decode(t.tjlx, 2, t.tjsl, 0)) swgx, sum(decode(t.tjlx, 3, t.tjsl, 0)) lwgx, sum(decode(t.tjlx, 4, t.tjsl, 0)) ynwgx, sum(decode(t.tjlx, 5, t.tjsl, 0)) yrdf, sum(decode(t.tjlx, 6, t.tjsl, 0)) yfdr, sum(decode(t.tjlx, 7, t.tjsl, 0)) byzx from (select substr(tjsq, 0, 12) pcs, nvl(sum(tjsl), 0) tjsl, tjlx from czw_fw_tj where tjsj = to_char(trunc(sysdate), 'yyyy-mm-dd') group by substr(tjsq, 0, 12), tjlx) t group by pcs) tj, czw_dm_pcs s where s.dm = tj.pcs; --以社区为单位统计数据 insert into czw_fw_czwry_tjfx (dm, mc, xzrs, swgx, lwgx, ynwgx, yrdf, yfdr, byzx, tjsj, tjlx) select s.dm dm, s.mc mc, tj.xzrs, tj.swgx, tj.lwgx, tj.ynwgx, tj.yrdf, tj.yfdr, tj.byzx, to_char(trunc(sysdate), 'yyyy-mm-dd'), '2' from (select sq, sum(decode(t.tjlx, 1, t.tjsl, 0)) xzrs, sum(decode(t.tjlx, 2, t.tjsl, 0)) swgx, sum(decode(t.tjlx, 3, t.tjsl, 0)) lwgx, sum(decode(t.tjlx, 4, t.tjsl, 0)) ynwgx, sum(decode(t.tjlx, 5, t.tjsl, 0)) yrdf, sum(decode(t.tjlx, 6, t.tjsl, 0)) yfdr, sum(decode(t.tjlx, 7, t.tjsl, 0)) byzx from (select tjsq sq, nvl(sum(tjsl), 0) tjsl, tjlx from czw_fw_tj where tjsj = to_char(trunc(sysdate), 'yyyy-mm-dd') group by tjsq, tjlx) t group by sq) tj, czw_dm_sq s where s.dm = tj.sq; commit; --创建索引 execute immediate 'create index index_czw_fw_czwry_tjfx_time on czw_fw_czwry_tjfx(tjsj)'; end; /* * 出租屋数据统计分析全部存储过程 */ PROCEDURE PRO_CZW_ALL as begin PRO_QZLD_CZW_DELETE(); --删除索引,清空数据 PRO_QZLD_CZW_IMPORT(); --导入数据 PRO_CZW_FW_TJ(); --统计分析 PRO_CZW_FW_CZWRY_TJFX(); --将统计分析的数据再统计一次 end; end;
相关推荐
hibernate many-to-many级联保存,级联更新,级联删除
全国省-市-区县-街道-居委会级联关系,不保证完整,注意上传时间,使用sqlite保存,请自己手动json导出
多对多关联映射---教师与学生表的级联增删改查小例子测试,代码简洁易懂,有SQL文件和使用说明.
Face-Detection-with-Adaboost-master_haar-like级联人脸检测代码_haar人脸检测.z
Face-Detection-with-Adaboost-master_haar-like级联人脸检测代码_haar人脸检测_源
行业分类-物理装置-一种全互联AXI总线的级联方法.zip
内部为四级的级联下拉框,分别为省份,市,县及区和镇及街道四级下拉框;(适用任何语言的纯HTML)详细截图可访问:https://blog.csdn.net/weixin_43972758/article/details/86510419 进行查询。
行业分类-设备装置-具有回馈功能的级联型高压变频器功率单元负载平台.zip
中肋骨条藻-裸甲藻-双毛纺锤水蚤营养传递的级联效应.docx
电信设备-一种q-LDPC-LT级联喷泉码方案的Ka频段深空通信方法及系统.zip
行业资料-电子功用-用于两电机驱动的全整流级联多电平拓扑电路
音视频-编解码-低密度奇偶校验码构造并行级联与译码器设计的研究.pdf
行业分类-设备装置-一种磁性微位移平台式级联阶梯角反射镜激光干涉仪
电子-STM32F103ZET6用定时器级联方式输出特定数目的PWM.rar,单片机/嵌入式STM32-F0/F1/F2
行业分类-设备装置-一种磁性微位移平台式级联阶梯角反射镜激光干涉仪及标定方法和测量方法
Hibernate 一对多关联映射的测试,主要使用部门与员工的级联关系进行添加测试.
Dependency Opencv Keras(theano-backend " tf data order") Numpy
三相级联H桥的研究和应用,希望能够对大家有所帮助,能和大家多交流
三电平级联npc逆变器的空间矢量调制的matlab仿真策略