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关系的级联保存、级联更新和级联删除。 一、Many-to-Many关系基础 在数据库设计中,Many-to-Many关系意味着两个实体之间存在多对多的关系。例如,学生可以选修多门...
全国省-市-区县-街道-居委会级联关系,不保证完整,注意上传时间,使用sqlite保存,请自己手动json导出
内部为四级的级联下拉框,分别为省份,市,县及区和镇及街道四级下拉框;(适用任何语言的纯HTML)详细截图可访问:https://blog.csdn.net/weixin_43972758/article/details/86510419 进行查询。
标题中的"Face-Detection-with-Adaboost-master_haar-like级联人脸检测代码_haar人脸检测_源"指的是一个使用Adaboost算法和Haar特征的级联分类器进行人脸检测的项目源代码。这个项目可能包含了一系列的程序文件,...
标题中的“Face-Detection-with-Adaboost-master_haar-like级联人脸检测代码_haar人脸检测.z”指的是一项利用Adaboost算法和Haar特征的级联分类器进行人脸检测的技术。Adaboost是一种机器学习算法,常用于特征选择和...
本教程将深入探讨Spring和Hibernate结合使用时的一对多关联映射,特别是部门与员工表的级联添加。 在数据库设计中,一对多关联是一个常见的关系类型,例如一个部门可以有多名员工,但每个员工只能属于一个部门。在...
"cascade H-bridge"或“级联H桥”则表示这是一个由多个H桥串联组成的电路结构,这样的设计可以提供更高的电压输出或增强系统性能。 在描述中,“三相级联H桥的研究和应用”意味着这个压缩包可能包含了关于三相电源...
本篇将深入探讨如何在Spring和Hibernate中实现多对多关联映射,并通过一个具体的教师与学生表的级联增删改查实例来阐述这一过程。 首先,多对多关联是数据库设计中的一种关系类型,表示一个实体可以与多个其他实体...
《电信设备-一种q-LDPC-LT级联喷泉码方案的Ka频段深空通信方法及系统》 在现代通信技术中,深空通信是极具挑战性的领域,尤其是在Ka频段,由于高频率带来的巨大带宽优势,使得深空探测任务的数据传输速率大幅提升。...
本知识点将详细解析标题所提及的“电子功用-用于两电机驱动的全整流级联多电平拓扑电路”。 首先,我们要理解“全整流”这一概念。全整流是指电路能够将交流电源的正负半周期都转化为直流电的过程,通常由六个或更...
三电平级联npc逆变器的空间矢量调制的matlab仿真策略
《具有回馈功能的级联型高压变频器功率单元负载平台》 在现代工业领域,电力电子技术的应用日益广泛,特别是在设备装置中,高压变频器作为一种高效能、高精度的电机驱动装置,已经成为电力传动系统的核心部分。本文...
标题中的"全互联AXI总线的级联方法"涉及到的是高级可扩展接口(Advanced eXtensible Interface, AXI)总线的扩展技术。AXI总线是由ARM公司设计的一种开放标准接口,广泛应用于SoC(System on Chip)设计,用于在不同...
在复杂分层网络结构模型基础上,提出了城际路网的级联失效可靠性仿真模型,对中国内地城际路网的可靠性进行了实例仿真研究。仿真数据表明:稳定配流状态下,长三角、环渤海、京津唐、珠三角地区OD负载聚集程度最高。...
《磁性微位移平台式级联阶梯角反射镜激光干涉仪》 在现代精密光学测量领域,激光干涉仪作为一种高精度、高灵敏度的测量工具,被广泛应用于各种精密定位、长度、速度以及微小位移的检测。本文将详细解析一种基于磁性...
标题中的“esayui-全国城市省市区三级级联”是指一个使用EasyUI和jQuery库开发的前端组件,主要用于实现中国地区(省、市、区)的三级联动选择功能。EasyUI是一个基于jQuery的轻量级JavaScript框架,它提供了一系列...
在这个项目中,我们主要讨论如何利用STM32F103ZET6的定时器级联功能来输出特定数目的PWM(脉宽调制)信号。PWM是一种非常实用的数字模拟转换技术,常用于电机控制、电源管理、LED亮度调节等应用。 在STM32系列芯片...
该压缩包文件主要涉及的是光学测量技术领域,具体是一种磁性微位移平台式级联阶梯角反射镜激光干涉仪,以及与之相关的标定和测量方法。在深入讨论这一主题之前,我们先来理解一下核心概念。 1. **激光干涉仪**:...
在"uniapp选择器,包含一级,二级级联,三级级联uniapp-picker-master.zip"这个压缩包中,包含了实现这种级联选择的示例代码。 uni-app是一个多端统一的开发框架,它可以将同一份代码编译到iOS、Android、H5、小程序等...