抽取日志表
create table ETL_LOG_DRAGON_ALERT ( tablename VARCHAR2(50),etlbegintime DATE,etlendtime DATE,cq_count NUMBER,bd_count NUMBER,etlflag VARCHAR2(50),msg VARCHAR2(4000) ) ;
获取编号的方法,获取年月日+5位数字(从1开始)
eg:XF06-2016112400002
create or replace function fun_get_dragon_yjxxbh return varchar2 is v_xxbh varchar2(100); begin --获取信息编号的sequence序列号 --拼接最后8位数字 v_xxbh := '0000' || SEQ_dragon_YJXXBH.NEXTVAL; --根据区划和日期生成信息编号 v_xxbh := 'XF11-' || TO_CHAR(SYSDATE,'YYYYMMDD') || substr(v_xxbh,length(v_xxbh) - 4,5); return v_xxbh; end;
create sequence SEQ_DRAGON_YJXXBH minvalue 1 maxvalue 99999999999999999999 start with 1 increment by 1 cache 20;
待抽取比对的表
create table T_DRAGON_GJXX ( id VARCHAR2(10) not null,fsf_xm VARCHAR2(60) not null,fsf_gmsfhm VARCHAR2(18) not null,gjzt VARCHAR2(20),gjlb VARCHAR2(10) not null,csmc VARCHAR2(120),csszd_xzqh VARCHAR2(6) not null,cxxzd_xzqhmc VARCHAR2(120) not null,gjfw VARCHAR2(20),rksj VARCHAR2(14),qt VARCHAR2(200),fkid VARCHAR2(200),hdfssj VARCHAR2(14) ) ; comment on table T_DRAGON_GJXX is '巨龙推送轨迹信息表'; comment on column T_DRAGON_GJXX.id is '主键 SEQ_GJID'; comment on column T_DRAGON_GJXX.fsf_xm is '非正常上访人员姓名'; comment on column T_DRAGON_GJXX.fsf_gmsfhm is '非正常上访人员公民身份号码'; comment on column T_DRAGON_GJXX.gjzt is '轨迹主体 1或2代表身份证号 3代表车辆轨迹'; comment on column T_DRAGON_GJXX.gjlb is '轨迹类别'; comment on column T_DRAGON_GJXX.csmc is '场所名称'; comment on column T_DRAGON_GJXX.csszd_xzqh is '场所所在地行政区划--暂定(可为空)'; comment on column T_DRAGON_GJXX.cxxzd_xzqhmc is '场所所在地名称'; comment on column T_DRAGON_GJXX.gjfw is '轨迹范围'; comment on column T_DRAGON_GJXX.rksj is '入库时间 yyyymmddhh24miss'; comment on column T_DRAGON_GJXX.qt is '其他 '; comment on column T_DRAGON_GJXX.fkid is '回传ID(布控编号)'; comment on column T_DRAGON_GJXX.hdfssj is '活动发生时间';
比对结果表,是会话级别的表,表中数据在会话结束后就会被清空,在存储过程中使用时,用来暂存数据
create global temporary table TEMP_T_DRAGON_ALERT_JG ( yjbh VARCHAR2(100),yjdd VARCHAR2(100),yjsj VARCHAR2(100),bkbh VARCHAR2(100),zdrybh VARCHAR2(100),xm VARCHAR2(100),sfzh VARCHAR2(100),gjid NUMBER ) on commit delete rows; -- 这里的delete 表示,提交过后,数据就会被删除 comment on table TEMP_T_DRAGON_ALERT_JG is '巨龙推送预警信息临时结果表'; comment on column TEMP_T_DRAGON_ALERT_JG.yjbh is '预警编号'; comment on column TEMP_T_DRAGON_ALERT_JG.yjdd is '预警地点'; comment on column TEMP_T_DRAGON_ALERT_JG.yjsj is '预警时间'; comment on column TEMP_T_DRAGON_ALERT_JG.bkbh is '布控编号'; comment on column TEMP_T_DRAGON_ALERT_JG.zdrybh is '重点人员编号'; comment on column TEMP_T_DRAGON_ALERT_JG.xm is '姓名'; comment on column TEMP_T_DRAGON_ALERT_JG.sfzh is '身份证号'; comment on column TEMP_T_DRAGON_ALERT_JG.gjid is '巨龙推送轨迹信息主键id';
抽取数据临时表,这个表示会话级的,暂存原表的数据,按照增量的形式将数据抽取到这张表,然后使用这些新增的数据区比对,然后将比对的结果,放入上面的比对结果表中
create global temporary table TEMP_T_DRAGON_GJXX ( id VARCHAR2(10) not null,hdfssj VARCHAR2(14) ) on commit preserve rows; --这里的preserve 表示 提交后数据依然存在,但是会话结束,数据就会清空 comment on table TEMP_T_DRAGON_GJXX is '抽取轨迹信息表临时表'; comment on column TEMP_T_DRAGON_GJXX.id is '主键 SEQ_GJID'; comment on column TEMP_T_DRAGON_GJXX.fsf_xm is '非正常上访人员姓名'; comment on column TEMP_T_DRAGON_GJXX.fsf_gmsfhm is '非正常上访人员公民身份号码'; comment on column TEMP_T_DRAGON_GJXX.gjzt is '轨迹主体 1或2代表身份证号 3代表车辆轨迹'; comment on column TEMP_T_DRAGON_GJXX.gjlb is '轨迹类别'; comment on column TEMP_T_DRAGON_GJXX.csmc is '场所名称'; comment on column TEMP_T_DRAGON_GJXX.csszd_xzqh is '场所所在地行政区划--暂定(可为空)'; comment on column TEMP_T_DRAGON_GJXX.cxxzd_xzqhmc is '场所所在地名称'; comment on column TEMP_T_DRAGON_GJXX.gjfw is '轨迹范围'; comment on column TEMP_T_DRAGON_GJXX.rksj is '入库时间 yyyymmddhh24miss'; comment on column TEMP_T_DRAGON_GJXX.qt is '其他 '; comment on column TEMP_T_DRAGON_GJXX.fkid is '回传ID(布控编号)'; comment on column TEMP_T_DRAGON_GJXX.hdfssj is '活动发生时间';
存储过程 :
create or replace procedure PRC_Dragon_Alert is /********************************************************* 名称 PRC_Dragon_Alert 功能描述:信访数据数据 修改记录 版本号 编辑时间 编辑人 修改描述 1.0.0 2016-1-16 aoliu 创建存储过程 *********************************************************/ p_Table_Name varchar2(100); ---------业务表 etlflag varchar2(100); ---------时间戳 TEMPCOUNTNUM number; ---------数据总量 UPDATENUM number; ---------修改总量 MAXFLAG varchar2(100); ----------最大时间戳 ETLBEGINTIME date; ---------开始时间 ETLENDTIME date; ---------结束时间 STATUS_FAILURE varchar2(1000); ---------异常信息 begin ETLBEGINTIME := sysdate; p_Table_Name := 'T_DRAGON_GJXX'; -------------------------------------------------获取最大时间戳-------------------------- SELECT nvl(max(ETLFLAG),'19000101000000') INTO etlflag FROM etl_log_Dragon_Alert where TABLENAME = p_Table_Name; insert into TEMP_T_dragon_gjxx select * from T_dragon_gjxx where rksj > etlflag; TEMPCOUNTNUM := sql%ROWCOUNT; commit; --------------------------- 巨龙预警信息结果表------------------------------------------------ insert into temp_t_Dragon_Alert_jg (yjbh,yjdd,yjsj,bkbh,zdrybh,xm,sfzh,gjid) select fun_get_dragon_yjxxbh() yjbh,CSMC yjdd,rksj yjsj,fkid bkbh,t2.zdrybh,FSF_XM xm,FSF_GMSFHM sfzh,t1.id gjid from TEMP_T_dragon_gjxx t1,t_pvbdp_person_collection t2 where t1.fsf_gmsfhm = t2.sfzh and t2.sfzrr = '1' and t2.scbs = '0' ; UPDATENUM := sql%ROWCOUNT; ------------------插预计表--------------------- insert into t_pvbdp_alert (id,yjbh,yjlx,ksjjsj,jsjjsj,yjrs,jtgj,sxtzbh,yjjsr,yjjsrmc,yjjsRSSjgdm,clzt,sfgq) select sys_guid() id,'4' yjlx,'' ksjjsj,'' jsjjsj,'1' yjrs,'' jtgj,'' sxtzbh,'' yjjsr,'' yjjsrmc,'' yjjsRSSjgdm,'1' clzt,'0' sfgq from temp_t_Dragon_Alert_jg; --------------------插预计关联表--------------- insert into t_pvbdp_alert_related (id,sxtbh,gjbh,bdsj,yjlx) select sys_guid() id,'' sxtbh,GJID gjbh,yjsj bdsj,'4' yjlx from temp_t_Dragon_Alert_jg; SELECT nvl(max(RKSJ),'19001010010100') INTO MAXFLAG FROM temp_t_dragon_gjxx; ETLENDTIME := sysdate; ---------------------------正常记录日志--------------------------- INSERT INTO etl_log_dragon_alert (TABLENAME,ETLBEGINTIME,ETLENDTIME,CQ_COUNT,BD_COUNT,ETLFLAG,MSG) VALUES (p_Table_Name,TEMPCOUNTNUM,UPDATENUM,MAXFLAG,p_Table_Name || '抽取成功'); COMMIT; ---------------------------异常记录日志--------------------------- exception when others then STATUS_FAILURE := to_char(sqlcode) || ': ' || substr(sqlerrm,1,980); INSERT INTO etl_log_dragon_alert (TABLENAME,MSG) VALUES (p_Table_Name,etlflag,STATUS_FAILURE); end;原文链接:https://www.f2er.com/oracle/210824.html