首页 > 学术百科

oracle拉链表怎么重跑,拉链表

oracle拉链表怎么重跑，拉链表

create or replace procedure test.SP_SDI_S_CRM_CUST_PE_BASE_INFO (sp_today string,sp_job_id string,ret out int) is

DECLAREstepINT;

errmsg STRING;BEGIN

BEGIN TRANSACTIONret := 0put_line(substr(current_date(), 1,10));

step := 1;

put_line(current_time() ||": STEP01: 重跑删除 test.SDI_S_CRM_CUST_PE_BASE_INFO" );DELETE FROM

test.SDI_S_CRM_CUST_PE_BASE_INFO WHERE job_seq_id >=sp_job_id;

put_line(current_time() ||": SETP01 重跑删除 test.SDI_S_CRM_CUST_PE_BASE_INFO 成功");

step := step + 1;

put_line(current_time() ||": STEP02: 重跑从历史表插⼊到 test.SDI_S_CRM_CUST_PE_BASE_INFO" );INSERT INTO

test.SDI_S_CRM_CUST_PE_BASE_INFO SELECT

cust_no,cert_type,cert_no,pecust_name,custname_short,cust_name_en,vip_cate,country,credit_level,is_freetax,btflbool,dimabool,sex eff_date,end_date,job_seq_id FROM test.HIS_S_CRM_CUST_PE_BASE_INFO WHERE new_job_seq_id=sp_job_id;

put_line(current_time() ||": SETP02 重跑从历史表插⼊到 test.SDI_S_CRM_CUST_PE_BASE_INFO 成功");

step := step + 1;

put_line(current_time() ||": STEP03: 重跑删除历史表原先数据 test.HIS_S_CRM_CUST_PE_BASE_INFO" );DELETE FROM

溧阳市文化小学

test.HIS_S_CRM_CUST_PE_BASE_INFO WHERE new_job_seq_id>=sp_job_id;

put_line(current_time() ||": SETP03 重跑删除历史表原先数据 test.HIS_S_CRM_CUST_PE_BASE_INFO 成功");

step := step + 1;--保存状态到HIS (全字段⽐较不相等)

put_line(current_time() ||": STEP04: 将数据备份到历史表 test.HIS_S_CRM_CUST_PE_BASE_INFO");INSERT

INTOtest.HIS_S_CRM_CUST_PE_BASE_INFOSELECTcust_no,cert_type,cert_no,pecust_name,custname_short,cust_name_en,vip_cate eff_date,end_date,job_seq_id,sp_job_idFROM test.SDI_S_CRM_CUST_PE_BASE_INFO N d_date='99990101' and

not EXISTS(SELECT 1

FROMtest.STG_S_CRM_CUST_PE_BASE_INFO MwhereM.cust_no=N.cust_no __type AND

<__no AND M.pecust_name=N.pecust_name AND M.custname_short=N.custname_short AND

M.cust_name_en=N.cust_name_en AND M.vip_cate=N.vip_cate untry dit_dit_level

AND M.is_freetax=N.is_freetax AND M.btflbool=N.btflbool AND M.dimabool=N.dimabool AND M.sex=N.sex =N.nty

=N.mrg AND M.btd=N.btd AND M.study_exp=N.study_exp AND M.degree=N.degree AND M.pay_acct=N.pay_acct

AND M.acct_wt_bk=N.acct_wt_bk ar_ar_income AND M.fmy_ppl=N.fmy_ppl AND

M.inhbt_stat=N.inhbt_stat AND M.cust_lev=N.cust_lev AND M.bank_emp_ind=N.bank_emp_ind AND氯化镁

M.bank_party_ind=N.bank_party_ind s AND M.xdzx_type=N.xdzx_type AND M.cust_qly=N.cust_qly AND

M.personal_insrc=N.personal_insrc AND M.insurance_dt=N.insurance_dt AND M.bad_record=N.bad_record AND

M.is_ltc_credit_cust=N.is_ltc_credit_cust AND M.native=N.native );

浙江大学校医院陈文备put_line(current_time() ||": SETP04 将数据备份到历史表 test.HIS_S_CRM_CUST_PE_BASE_INFO 成功");

step := step + 1;--新增数据直接insert

put_line(current_time() ||": STEP05: 插⼊今⽇新增数据 test.SDI_S_CRM_CUST_PE_BASE_INFO" );INSERT INTO万事无忧4

test.SDI_S_CRM_CUST_PE_BASE_INFO SELECT

cust_no,cert_type,cert_no,pecust_name,custname_short,cust_name_en,vip_cate,country,credit_level,is_freetax,btflbool,dimabool,sex sp_today,'99990101',sp_job_id FROM test.STG_S_CRM_CUST_PE_BASE_INFO A where not EXISTS (select 1 from

test.SDI_S_CRM_CUST_PE_BASE_INFO B d_date='99990101' and A.CUST_NO=B.CUST_NO )

put_line(current_time() ||": STEP05: 插⼊今⽇新增数据 test.SDI_S_CRM_CUST_PE_BASE_INFO 成功" );--当前批次做拉链

put_line(current_time()||": STEP06: 当前批次做拉链 test.SDI_S_CRM_CUST_PE_BASE_INFO");UPDATE

test.SDI_S_CRM_CUST_PE_BASE_INFO A SET END_DATE=sp_today,job_seq_id=sp_job_id WHERE END_DATE>=sp_today

AND not EXISTS(SELECT 1 FROM (SELECT M.CUST_NO FROMtest.SDI_S_CRM_CUST_PE_BASE_INFO Minner

jointest.STG_S_CRM_CUST_PE_BASE_INFO Non M.cust_no=N.cust_no __type AND

<__no AND M.pecust_name=N.pecust_name AND M.custname_short=N.custname_short AND

M.cust_name_en=N.cust_name_en AND M.vip_cate=N.vip_cate untry dit_dit_level

AND M.is_freetax=N.is_freetax AND M.btflbool=N.btflbool AND M.dimabool=N.dimabool AND M.sex=N.sex =N.nty

=N.mrg AND M.btd=N.btd AND M.study_exp=N.study_exp AND M.degree=N.degree AND M.pay_acct=N.pay_acct

AND M.acct_wt_bk=N.acct_wt_bk ar_ar_income AND M.fmy_ppl=N.fmy_ppl AND

M.inhbt_stat=N.inhbt_stat AND M.cust_lev=N.cust_lev AND M.bank_emp_ind=N.bank_emp_ind AND

M.bank_party_ind=N.bank_party_ind s AND M.xdzx_type=N.xdzx_type AND M.cust_qly=N.cust_qly AND

M.personal_insrc=N.personal_insrc AND M.insurance_dt=N.insurance_dt AND M.bad_record=N.bad_record AND

M.is_ltc_credit_cust=N.is_ltc_credit_cust AND M.native=N.native WHERE M.END_DATE='99990101') Bwhere

A.CUST_NO=

B.CUST_NO );

put_line(current_time()||": STEP06 当前批次做拉链 test.SDI_S_CRM_CUST_PE_BASE_INFO 成功");

step := step + 1;--当前批次拉链后新增

put_line(current_time()||": STEP07: 当前批次拉链后新增 test.SDI_S_CRM_CUST_PE_BASE_INFO");INSERT

INTOtest.SDI_S_CRM_CUST_PE_BASE_INFOSELECT

定陵地下宫殿发掘记

cust_no,cert_type,cert_no,pecust_name,custname_short,cust_name_en,vip_cate,country,credit_level,is_freetax,btflbool,dimabool,sex sp_today,'99990101', sp_job_id from test.STG_S_CRM_CUST_PE_BASE_INFO A WHERE not EXISTS (SELECT 1

FROMtest.SDI_S_CRM_CUST_PE_BASE_INFO d_date = sp_today and A.CUST_NO=B.CUST_NO );

put_line(current_time() ||": STEP07 当前批次拉链后新增 test.SDI_S_CRM_CUST_PE_BASE_INFO 成功");

step := step + 1;COMMIT;

EXCEPTIONWHEN OTHERS THENret := -1put_line(sqlerrm());

errmsg := CASE

WHEN step = 1 THEN": STEP01: FAILD 重跑删除 test.SDI_S_CRM_CUST_PE_BASE_INFO"WHEN step = 2 THEN": STEP02:

FAILD 重跑从历史表插⼊到 test.SDI_S_CRM_CUST_PE_BASE_INFO"WHEN step = 3 THEN": STEP03: FAILD 重跑删除历史表原先中国男子体操

数据 test.HIS_S_CRM_CUST_PE_BASE_INFO"WHEN step = 4 THEN": STEP04: FAILD 将数据备份到历史表

test.HIS_S_CRM_CUST_PE_BASE_INFO"WHEN step = 5 THEN": STEP05: FAILD 插⼊今⽇新增数据

test.SDI_S_CRM_CUST_PE_BASE_INFO"WHEN step = 6 THEN": STEP06: FAILD 当前批次做拉链

test.SDI_S_CRM_CUST_PE_BASE_INFO"WHEN step = 7 THEN": STEP07: FAILD 当前批次拉链后新增

test.SDI_S_CRM_CUST_PE_BASE_INFO"END;

put_line(current_time() ||errmsg);ROLLBACK;END;

本文发布于:2024-09-20 19:46:14，感谢您对本站的认可！

本文链接：https://www.17tex.com/xueshu/399670.html

上一篇：电气设备：锂电化大风口1：电动工具电池国产替代正当时

下一篇：自己打造Win7一键还原

标签：历史新增数据溧阳市状态

留言与评论（共有 0 条评论）