oracle 增量抽取数据存储过程

抽取日志表

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_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;

oracle 增量抽取数据存储过程

猜你在找的Oracle相关文章