/*********************************************************************************/--存储过程名称： P_CONTRACT--作者： 倪士甲--时间： 2011-09-16--使用源表名称:--使用目标表名称：--参数说明： O_RUNSTATUS、O_MSG（传出参数）--功能： 仓库数据ETL处理--修改记录/*********************************************************************************/PROCEDURE P_CONTRACT(P_ETLDATE IN VARCHAR2, --日期参数O_RUNSTATUS OUT NUMBER, --执行结果O_MSG OUT VARCHAR2 --错误返回) AS --PROCEDURE NAME:P_CONTRACT --AUTHOR :倪士甲 2011-09-16 --UPDATE_AUTHOR : --SCRIPT FILE :P_CONTRACT.SQL --定义存储过程信息 V_PROC_NAME VARCHAR2(50) := 'P_CONTRACT'; V_START_TIMESTAMP TIMESTAMP; --加载开始时间 V_END_TIMESTAMP TIMESTAMP; --加载结束时间 V_RECORD_NUMBER INTEGER; --记录数 --定义错误代码，错误状态 V_SQLERRM VARCHAR2(1000); --异常信息 V_ERR_SQL VARCHAR2(1000); --出错位置BEGIN --0、AP_CONTRACT表LOAN_CLS字段值特殊处理 V_ERR_SQL:='1'; EXECUTE IMMEDIATE 'TRUNCATE TABLE YES_CONTRACT'; INSERT INTO YES_CONTRACT (ID, DATA_DT, AGMT_NO, CONTRACT_NO, LOAN_AMT, AGMT_HOLDER, CUSTOMER_NAME, LOAN_TYPE_CD, AGMT_STATUS_CD, CURR_CD, BALANCE, INTEREST1, INTEREST2, CLASSIFY5, LOAN_DIRC_CD, AGMT_START_DATE, AGMT_END_DATE, AGMT_BELONG_ORG_NO, MANAGER_NO, CORP_SCALE_GB_CD, LOAN_CARD_NO, ACCOUNT_BANK, ACCOUNT_NO, REPAY_PERIOD, FIN_PROM_SCOPE_A, FIN_PROM_SCOPE_B, FIN_PROM_SCOPE_C, CORP_REG_TYPE_CD, LOAN_KIND_CD, SMALL_CORP_FLAG, INDUSTRY_CUST_FLAG, MEAGER_PROFIT_CD, PROCESS_RATE, BASIS_RATE, PUNISH_RATE, OVERDUE_PUNISH_RATE, UPPER_RATE, OVERDUE_UPPER_RATE, RATE_TYPE_CD, TERM_TYPE_CD, SETTLE_RATE_METH_CD, INSURE_METH_TYPE_CD, AGMT_SIGN_DATE, LOAN_PROP_CD, LOAN_USE_TYPE, BANK_CONSORTIUM_FLAG, LOAN_REPAY_TYPE_CD, REPAY_ACCT, APPROVE_NO, LEND_TYPE_CD, RATE_ADJUST_METH_CD, ENTRUST_LOAN_FLAG, ENTRUST_NAME, CURR_RATE, BANK_CONSORTIUM_AGMT_AMT, LEAD_BANK_FLAG, CHARGE_RATE, CHARGE_AMT, LOAN_FORM_CD, ORIG_AGMT_NO, FARM_SYN_DEV_LOAN_FLAG, POVERTY_SUBSIDY_LOAN_FLAG, LEAD_CORP_FLAG, LEAD_CORP_LEVEL_CD, FARM_LOAN_FLAG, FARM_LOAN_TYPE_CD, LOAN_BIZ_TYPE_CD, UPDATEDATE, CHG_FLAG, YSTDY_BALANCE, LOAN_CLS) SELECT ID, DATA_DT, AGMT_NO, CONTRACT_NO, LOAN_AMT, AGMT_HOLDER, CUSTOMER_NAME, LOAN_TYPE_CD, AGMT_STATUS_CD, CURR_CD, BALANCE, INTEREST1, INTEREST2, CLASSIFY5, LOAN_DIRC_CD, AGMT_START_DATE, AGMT_END_DATE, AGMT_BELONG_ORG_NO, MANAGER_NO, CORP_SCALE_GB_CD, LOAN_CARD_NO, ACCOUNT_BANK, ACCOUNT_NO, REPAY_PERIOD, FIN_PROM_SCOPE_A, FIN_PROM_SCOPE_B, FIN_PROM_SCOPE_C, CORP_REG_TYPE_CD, LOAN_KIND_CD, SMALL_CORP_FLAG, INDUSTRY_CUST_FLAG, MEAGER_PROFIT_CD, PROCESS_RATE, BASIS_RATE, PUNISH_RATE, OVERDUE_PUNISH_RATE, UPPER_RATE, OVERDUE_UPPER_RATE, RATE_TYPE_CD, TERM_TYPE_CD, SETTLE_RATE_METH_CD, INSURE_METH_TYPE_CD, AGMT_SIGN_DATE, LOAN_PROP_CD, LOAN_USE_TYPE, BANK_CONSORTIUM_FLAG, LOAN_REPAY_TYPE_CD, REPAY_ACCT, APPROVE_NO, LEND_TYPE_CD, RATE_ADJUST_METH_CD, ENTRUST_LOAN_FLAG, ENTRUST_NAME, CURR_RATE, BANK_CONSORTIUM_AGMT_AMT, LEAD_BANK_FLAG, CHARGE_RATE, CHARGE_AMT, LOAN_FORM_CD, ORIG_AGMT_NO, FARM_SYN_DEV_LOAN_FLAG, POVERTY_SUBSIDY_LOAN_FLAG, LEAD_CORP_FLAG, LEAD_CORP_LEVEL_CD, FARM_LOAN_FLAG, FARM_LOAN_TYPE_CD, LOAN_BIZ_TYPE_CD, UPDATEDATE, CHG_FLAG, YSTDY_BALANCE, LOAN_CLS FROM AP_CONTRACT --WHERE LOAN_CLS IN ('A', 'S') ; --1、新增当前表存在而AP表不存在的数据 V_ERR_SQL:='2'; INSERT INTO AP_CONTRACT (ID, DATA_DT, AGMT_NO, CONTRACT_NO, LOAN_AMT, AGMT_HOLDER, CUSTOMER_NAME, LOAN_TYPE_CD, AGMT_STATUS_CD, CURR_CD, BALANCE, INTEREST1, INTEREST2, CLASSIFY5, LOAN_DIRC_CD, AGMT_START_DATE, AGMT_END_DATE, AGMT_BELONG_ORG_NO, MANAGER_NO, CORP_SCALE_GB_CD, LOAN_CARD_NO, ACCOUNT_BANK, ACCOUNT_NO, REPAY_PERIOD, FIN_PROM_SCOPE_A, FIN_PROM_SCOPE_B, FIN_PROM_SCOPE_C, CORP_REG_TYPE_CD, LOAN_KIND_CD, SMALL_CORP_FLAG, INDUSTRY_CUST_FLAG, MEAGER_PROFIT_CD, PROCESS_RATE, BASIS_RATE, PUNISH_RATE, OVERDUE_PUNISH_RATE, UPPER_RATE, OVERDUE_UPPER_RATE, RATE_TYPE_CD, TERM_TYPE_CD, SETTLE_RATE_METH_CD, INSURE_METH_TYPE_CD, AGMT_SIGN_DATE, LOAN_PROP_CD, LOAN_USE_TYPE, BANK_CONSORTIUM_FLAG, LOAN_REPAY_TYPE_CD, REPAY_ACCT, APPROVE_NO, LEND_TYPE_CD, RATE_ADJUST_METH_CD, ENTRUST_LOAN_FLAG, ENTRUST_NAME, CURR_RATE, BANK_CONSORTIUM_AGMT_AMT, LEAD_BANK_FLAG, CHARGE_RATE, CHARGE_AMT, LOAN_FORM_CD, ORIG_AGMT_NO, FARM_SYN_DEV_LOAN_FLAG, POVERTY_SUBSIDY_LOAN_FLAG, LEAD_CORP_FLAG, LEAD_CORP_LEVEL_CD, FARM_LOAN_FLAG, FARM_LOAN_TYPE_CD, LOAN_BIZ_TYPE_CD, UPDATEDATE, DATA_STATUS, UP_DATE, LOAN_CLS, CHG_FLAG, YSTDY_BALANCE) SELECT A1.ID, A1.DATA_DT, A1.AGMT_NO, A1.CONTRACT_NO, A1.LOAN_AMT, A1.AGMT_HOLDER, A1.CUSTOMER_NAME, A1.LOAN_TYPE_CD, A1.AGMT_STATUS_CD, A1.CURR_CD, A1.BALANCE, A1.INTEREST1, A1.INTEREST2, A1.CLASSIFY5, A1.LOAN_DIRC_CD, A1.AGMT_START_DATE, A1.AGMT_END_DATE, A1.AGMT_BELONG_ORG_NO, A1.MANAGER_NO, A1.CORP_SCALE_GB_CD, A1.LOAN_CARD_NO, A1.ACCOUNT_BANK, A1.ACCOUNT_NO, A1.REPAY_PERIOD, A1.FIN_PROM_SCOPE_A, A1.FIN_PROM_SCOPE_B, A1.FIN_PROM_SCOPE_C, A1.CORP_REG_TYPE_CD, A1.LOAN_KIND_CD, A1.SMALL_CORP_FLAG, A1.INDUSTRY_CUST_FLAG, A1.MEAGER_PROFIT_CD, A1.PROCESS_RATE, A1.BASIS_RATE, A1.PUNISH_RATE, A1.OVERDUE_PUNISH_RATE, A1.UPPER_RATE, A1.OVERDUE_UPPER_RATE, A1.RATE_TYPE_CD, A1.TERM_TYPE_CD, A1.SETTLE_RATE_METH_CD, A1.INSURE_METH_TYPE_CD, A1.AGMT_SIGN_DATE, A1.LOAN_PROP_CD, A1.LOAN_USE_TYPE, A1.BANK_CONSORTIUM_FLAG, A1.LOAN_REPAY_TYPE_CD, A1.REPAY_ACCT, A1.APPROVE_NO, A1.LEND_TYPE_CD, A1.RATE_ADJUST_METH_CD, A1.ENTRUST_LOAN_FLAG, A1.ENTRUST_NAME, A1.CURR_RATE, A1.BANK_CONSORTIUM_AGMT_AMT, A1.LEAD_BANK_FLAG, A1.CHARGE_RATE, A1.CHARGE_AMT, A1.LOAN_FORM_CD, A1.ORIG_AGMT_NO, A1.FARM_SYN_DEV_LOAN_FLAG, A1.POVERTY_SUBSIDY_LOAN_FLAG, A1.LEAD_CORP_FLAG, A1.LEAD_CORP_LEVEL_CD, A1.FARM_LOAN_FLAG, A1.FARM_LOAN_TYPE_CD, A1.LOAN_BIZ_TYPE_CD, A1.UPDATEDATE, '0', --新增 TO_CHAR(SYSDATE, 'YYYYMMDD'), 'N' , --新增数据将此字段置为N 'N', --因为是新增，昨日余额为空，将其设置为余额未变 '' --因为是新增，昨日余额为空 FROM CUR_CONTRACT A1 WHERE NOT EXISTS (SELECT 1 FROM YES_CONTRACT B1 WHERE A1.AGMT_NO = B1.AGMT_NO AND A1.AGMT_HOLDER = B1.AGMT_HOLDER); --2、更新当前表与AP表都有的记录 --(1)、删除两共有的AP表数据 V_ERR_SQL:='3'; DELETE FROM AP_CONTRACT A1 WHERE EXISTS (SELECT 1 FROM CUR_CONTRACT B1 INNER JOIN YES_CONTRACT B2 ON B1.AGMT_NO = B2.AGMT_NO AND B1.AGMT_HOLDER = B2.AGMT_HOLDER WHERE A1.AGMT_NO = B1.AGMT_NO AND A1.AGMT_HOLDER = B1.AGMT_HOLDER); --(2)、从当前表更新数据到AP表中 V_ERR_SQL:='4'; INSERT INTO AP_CONTRACT (ID, DATA_DT, AGMT_NO, CONTRACT_NO, LOAN_AMT, AGMT_HOLDER, CUSTOMER_NAME, LOAN_TYPE_CD, AGMT_STATUS_CD, CURR_CD, BALANCE, INTEREST1, INTEREST2, CLASSIFY5, LOAN_DIRC_CD, AGMT_START_DATE, AGMT_END_DATE, AGMT_BELONG_ORG_NO, MANAGER_NO, CORP_SCALE_GB_CD, LOAN_CARD_NO, ACCOUNT_BANK, ACCOUNT_NO, REPAY_PERIOD, FIN_PROM_SCOPE_A, FIN_PROM_SCOPE_B, FIN_PROM_SCOPE_C, CORP_REG_TYPE_CD, LOAN_KIND_CD, SMALL_CORP_FLAG, INDUSTRY_CUST_FLAG, MEAGER_PROFIT_CD, PROCESS_RATE, BASIS_RATE, PUNISH_RATE, OVERDUE_PUNISH_RATE, UPPER_RATE, OVERDUE_UPPER_RATE, RATE_TYPE_CD, TERM_TYPE_CD, SETTLE_RATE_METH_CD, INSURE_METH_TYPE_CD, AGMT_SIGN_DATE, LOAN_PROP_CD, LOAN_USE_TYPE, BANK_CONSORTIUM_FLAG, LOAN_REPAY_TYPE_CD, REPAY_ACCT, APPROVE_NO, LEND_TYPE_CD, RATE_ADJUST_METH_CD, ENTRUST_LOAN_FLAG, ENTRUST_NAME, CURR_RATE, BANK_CONSORTIUM_AGMT_AMT, LEAD_BANK_FLAG, CHARGE_RATE, CHARGE_AMT, LOAN_FORM_CD, ORIG_AGMT_NO, FARM_SYN_DEV_LOAN_FLAG, POVERTY_SUBSIDY_LOAN_FLAG, LEAD_CORP_FLAG, LEAD_CORP_LEVEL_CD, FARM_LOAN_FLAG, FARM_LOAN_TYPE_CD, LOAN_BIZ_TYPE_CD, UPDATEDATE, DATA_STATUS, UP_DATE, LOAN_CLS, CHG_FLAG, YSTDY_BALANCE) SELECT A1.ID, A1.DATA_DT, A1.AGMT_NO, A1.CONTRACT_NO, A1.LOAN_AMT, A1.AGMT_HOLDER, A1.CUSTOMER_NAME, A1.LOAN_TYPE_CD, A1.AGMT_STATUS_CD, A1.CURR_CD, A1.BALANCE, A1.INTEREST1, A1.INTEREST2, A1.CLASSIFY5, A1.LOAN_DIRC_CD, A1.AGMT_START_DATE, A1.AGMT_END_DATE, A1.AGMT_BELONG_ORG_NO, A1.MANAGER_NO, A1.CORP_SCALE_GB_CD, A1.LOAN_CARD_NO, A1.ACCOUNT_BANK, A1.ACCOUNT_NO, A1.REPAY_PERIOD, A1.FIN_PROM_SCOPE_A, A1.FIN_PROM_SCOPE_B, A1.FIN_PROM_SCOPE_C, A1.CORP_REG_TYPE_CD, A1.LOAN_KIND_CD, A1.SMALL_CORP_FLAG, A1.INDUSTRY_CUST_FLAG, A1.MEAGER_PROFIT_CD, A1.PROCESS_RATE, A1.BASIS_RATE, A1.PUNISH_RATE, A1.OVERDUE_PUNISH_RATE, A1.UPPER_RATE, A1.OVERDUE_UPPER_RATE, A1.RATE_TYPE_CD, A1.TERM_TYPE_CD, A1.SETTLE_RATE_METH_CD, A1.INSURE_METH_TYPE_CD, A1.AGMT_SIGN_DATE, A1.LOAN_PROP_CD, A1.LOAN_USE_TYPE, A1.BANK_CONSORTIUM_FLAG, A1.LOAN_REPAY_TYPE_CD, A1.REPAY_ACCT, A1.APPROVE_NO, A1.LEND_TYPE_CD, A1.RATE_ADJUST_METH_CD, A1.ENTRUST_LOAN_FLAG, A1.ENTRUST_NAME, A1.CURR_RATE, A1.BANK_CONSORTIUM_AGMT_AMT, A1.LEAD_BANK_FLAG, A1.CHARGE_RATE, A1.CHARGE_AMT, A1.LOAN_FORM_CD, A1.ORIG_AGMT_NO, A1.FARM_SYN_DEV_LOAN_FLAG, A1.POVERTY_SUBSIDY_LOAN_FLAG, A1.LEAD_CORP_FLAG, A1.LEAD_CORP_LEVEL_CD, A1.FARM_LOAN_FLAG, A1.FARM_LOAN_TYPE_CD, A1.LOAN_BIZ_TYPE_CD, A1.UPDATEDATE, '1', --更新的记录 TO_CHAR(SYSDATE, 'YYYYMMDD'), A2.LOAN_CLS, --更新数据将此字段沿用老的，不变 CASE WHEN A1.BALANCE=A2.BALANCE THEN 'N' WHEN A2.BALANCE IS NULL THEN 'N' ELSE 'Y' END, --与昨天相比，余额是否改变 A2.Balance --昨日余额 FROM CUR_CONTRACT A1 LEFT JOIN YES_CONTRACT A2 ON A1.AGMT_NO = A2.AGMT_NO AND A1.AGMT_HOLDER = A2.AGMT_HOLDER WHERE EXISTS (SELECT 1 FROM YES_CONTRACT B1 WHERE A1.AGMT_NO = B1.AGMT_NO AND A1.AGMT_HOLDER = B1.AGMT_HOLDER); --3、当前表不存在的数据在AP表中将状态置为2（对于昨日余额问题，不用在此作处理） V_ERR_SQL:='5'; UPDATE AP_CONTRACT A1 SET A1.DATA_STATUS = '2', A1.UP_DATE = TO_CHAR(SYSDATE, 'YYYYMMDD') WHERE NOT EXISTS (SELECT 1 FROM CUR_CONTRACT B2 WHERE A1.AGMT_NO = B2.AGMT_NO AND A1.AGMT_HOLDER = B2.AGMT_HOLDER); --正常处理 V_RECORD_NUMBER := SQL%ROWCOUNT; SELECT SYSDATE INTO V_END_TIMESTAMP FROM DUAL; INSERT INTO APS_ETL_LOG_DETAIL(START_TIMESTAMP,END_TIMESTAMP,PROC_NAME,ETL_RECORD_NUM,ETL_MEMO,P_ETLDATE) VALUES (V_START_TIMESTAMP,V_END_TIMESTAMP,V_PROC_NAME,V_RECORD_NUMBER,'成功',P_ETLDATE); COMMIT; --异常处理 EXCEPTION WHEN OTHERS THEN BEGIN ROLLBACK; V_SQLERRM := SQLERRM; INSERT INTO APS_ETL_LOG_DETAIL(START_TIMESTAMP,END_TIMESTAMP,PROC_NAME,ETL_RECORD_NUM,ETL_MEMO,ERR_MSG,ERR_SQL,P_ETLDATE) VALUES (V_START_TIMESTAMP,V_END_TIMESTAMP,V_PROC_NAME,0,'失败',V_SQLERRM,V_ERR_SQL,P_ETLDATE); O_RUNSTATUS := 1; O_MSG := 'PROGRAMMING ERROR HAPPENED'; COMMIT; END;END;/说明：此算法记录了全部数据新增、更新、删除的状态，并有相应其他操作；算法主要用到两个临时表cur_、yes_来实现；比之原先数据仓库常用的增量更新算法，记的更细；两种算法，各有优缺点。
