oracle 增量抽取数据存储过程

来源：互联网发布：昆山仁宝网络怎么样编辑：程序博客网时间：2024/05/22 02:09

抽取日志表

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;

上面方法所使用的序列SEQ_FRAGON_YJXXBH

create sequence SEQ_DRAGON_YJXXBHminvalue 1maxvalue 99999999999999999999start with 1increment by 1cache 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,  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))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,     yjdd,     yjrs,     jtgj,     sxtzbh,     yjsj,     yjjsr,     yjjsrmc,     yjjsrssjgdm,     clzt,     sfgq)    select sys_guid() id,           yjbh,           '4' yjlx,           '' ksjjsj,           '' jsjjsj,           yjdd,           '1' yjrs,           '' jtgj,           '' sxtzbh,           yjsj,           '' yjjsr,           '' yjjsrmc,           '' yjjsrssjgdm,           '1' clzt,           '0' sfgq      from temp_t_Dragon_Alert_jg;  --------------------插预计关联表---------------  insert into t_pvbdp_alert_related    (id, yjbh, bkbh, zdrybh, sxtbh, xm, sfzh, gjbh, bdsj, yjlx)    select sys_guid() id,           yjbh,           bkbh,           zdrybh,           '' sxtbh,           xm,           sfzh,           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,     ETLBEGINTIME,     ETLENDTIME,     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,       ETLBEGINTIME,       ETLENDTIME,       CQ_COUNT,       BD_COUNT,       ETLFLAG,       MSG)    VALUES      (p_Table_Name,       ETLBEGINTIME,       ETLENDTIME,       TEMPCOUNTNUM,       UPDATENUM,       etlflag,       STATUS_FAILURE);end;

0 0