create or replace procedure sp_llb
is
v_start_dt date;
v_end_dt date;
begin
v_start_dt :=sysdate;
merge into LOAN_BORROW_INFO_LLB a
using (select * from LOAN_BORROW_INFO_TEST) b
on (a.LOAN_BORROW_ID = b.LOAN_BORROW_ID and
a.V2_PLATFORM_ID = b.V2_PLATFORM_ID and
a.V2_MERCHANT_ID = b.V2_MERCHANT_ID and
a.V2_APP_ID = b.V2_APP_ID and
a.V2_CLIENT_ID = b.V2_CLIENT_ID and
a.V2_PRODUCT_ID = b.V2_PRODUCT_ID and
a.USER_ID = b.USER_ID and
a.SERIAL_NO = b.SERIAL_NO and
a.STATE = b.STATE and
a.STATE_MSG = b.STATE_MSG and
a.LEND_SERIAL_NO = b.LEND_SERIAL_NO and
a.LEND_RETRY_COUNT = b.LEND_RETRY_COUNT and
a.LEND_RETRY_LAST_DATE = b.LEND_RETRY_LAST_DATE and
a.LEND_RETRY_NEXT_DATE = b.LEND_RETRY_NEXT_DATE and
a.LEND_MSG = b.LEND_MSG and
a.PHONE = b.PHONE and
a.USER_NAME = b.USER_NAME and
a.AADHAAR_NO = b.AADHAAR_NO and
a.ACCOUNT_BANK_ID = b.ACCOUNT_BANK_ID and
a.LEND_ACCOUNT_NO = b.LEND_ACCOUNT_NO and
a.LEND_IFSC_CODE = b.LEND_IFSC_CODE and
a.LEND_BANK_NAME = b.LEND_BANK_NAME and
a.REPAY_ACCOUNT_NO = b.REPAY_ACCOUNT_NO and
a.REPAY_IFSC_CODE = b.REPAY_IFSC_CODE and
a.CREATED_DATE = b.CREATED_DATE and
a.CONFIRM_DATE = b.CONFIRM_DATE and
a.IN_FORCE_DATE = b.IN_FORCE_DATE and
a.FIXED_DUE_DATE = b.FIXED_DUE_DATE and
a.DUE_DATE = b.DUE_DATE and
a.REPAYMENT_AMOUNT = b.REPAYMENT_AMOUNT and
a.ACTUAL_AMOUNT = b.ACTUAL_AMOUNT and
a.REDUCE_AMOUNT = b.REDUCE_AMOUNT and
a.LAST_ACTUAL_REPAYMENT_TYPE = b.LAST_ACTUAL_REPAYMENT_TYPE and
a.LAST_ACTUAL_REPAYMENT_AMOUNT = b.LAST_ACTUAL_REPAYMENT_AMOUNT and
a.ACTUAL_REPAYMENT_AMOUNT = b.ACTUAL_REPAYMENT_AMOUNT and
a.ACTUAL_REPAY_DATE = b.ACTUAL_REPAY_DATE and
a.ROLLOVER_AMOUNT_PAID = b.ROLLOVER_AMOUNT_PAID and
a.ROLLOVER_FLAG = b.ROLLOVER_FLAG and
a.ORIGINAL_LOAN_BORROW_ID = b.ORIGINAL_LOAN_BORROW_ID and
a.LAST_PART_AMOUNT = b.LAST_PART_AMOUNT and
a.PART_EXTEND = b.PART_EXTEND and
a.PART_EXTEND_TIMES = b.PART_EXTEND_TIMES and
a.PART_EXTEND_DATE = b.PART_EXTEND_DATE and
a.SETTLE_TYPE = b.SETTLE_TYPE and
a.PAYOUT_TYPE = b.PAYOUT_TYPE and
a.REPAY_TYPE = b.REPAY_TYPE and
a.PHOTO_KEY = b.PHOTO_KEY and
a.AGG_TYPE = b.AGG_TYPE and
a.USER_TYPE = b.USER_TYPE and
a.USER_TYPE_CLIENT = b.USER_TYPE_CLIENT and
a.USER_TYPE_PRODUCT = b.USER_TYPE_PRODUCT and
a.DERATING = b.DERATING and
a.REBORROW = b.REBORROW and
a.REBORROW_TIMES = b.REBORROW_TIMES and
a.ROLLOVER_TIMES = b.ROLLOVER_TIMES and
a.RISK_RETRY_LAST_DATE = b.RISK_RETRY_LAST_DATE and
a.RISK_RETRY_COUNT = b.RISK_RETRY_COUNT and
a.RISK_SERIAL_NO = b.RISK_SERIAL_NO and
a.RISK_DECISION_DATE = b.RISK_DECISION_DATE and
a.RISK_DECISION_RESULT = b.RISK_DECISION_RESULT and
a.OPERATOR = b.OPERATOR and
a.APPROVAL_DATE = b.APPROVAL_DATE and
a.APPROVAL_MSG = b.APPROVAL_MSG and
a.AMOUNT = b.AMOUNT and
a.PERIODS = b.PERIODS and
a.PERIOD_TENURE = b.PERIOD_TENURE and
a.PERIOD_UNIT = b.PERIOD_UNIT and
a.GST = b.GST and
a.INTEREST_FEE_RATE = b.INTEREST_FEE_RATE and
a.ASSESSMENT_FEE_RATE = b.ASSESSMENT_FEE_RATE and
a.EXTEND_FEE_RATE = b.EXTEND_FEE_RATE and
a.PENALTY_FEE_RATE = b.PENALTY_FEE_RATE and
a.PART_INTEREST_FEE_RATE = b.PART_INTEREST_FEE_RATE and
a.FEE_TYPE = b.FEE_TYPE and
a.RISK_MERCHANT_NO = b.RISK_MERCHANT_NO and
a.RISK_SCENE_CODE = b.RISK_SCENE_CODE and
a.RISK_PRODUCT_CODE = b.RISK_PRODUCT_CODE and
a.INFO_DEVICE_ID = b.INFO_DEVICE_ID and
a.REG_CHANNEL = b.REG_CHANNEL and
a.DOWN_CHANNEL = b.DOWN_CHANNEL and
a.MEDIA_SOURCE = b.MEDIA_SOURCE and
a.CREATE_LOCK_DATE = b.CREATE_LOCK_DATE and
a.LAST_MODIFIED_DATE = b.LAST_MODIFIED_DATE and
a.ARCHIVED = b.ARCHIVED and
a.COUNTRY_CODE = b.COUNTRY_CODE
)
when matched then update
set a.END_TIME=sysdate
when not matched then
insert (a.LOAN_BORROW_ID,
a.V2_PLATFORM_ID,
a.V2_MERCHANT_ID,
a.V2_APP_ID,
a.V2_CLIENT_ID,
a.V2_PRODUCT_ID,
a.USER_ID,
a.SERIAL_NO,
a.STATE,
a.STATE_MSG,
a.LEND_SERIAL_NO,
a.LEND_RETRY_COUNT,
a.LEND_RETRY_LAST_DATE,
a.LEND_RETRY_NEXT_DATE,
a.LEND_MSG,
a.PHONE,
a.USER_NAME,
a.AADHAAR_NO,
a.ACCOUNT_BANK_ID,
a.LEND_ACCOUNT_NO,
a.LEND_IFSC_CODE,
a.LEND_BANK_NAME,
a.REPAY_ACCOUNT_NO,
a.REPAY_IFSC_CODE,
a.CREATED_DATE,
a.CONFIRM_DATE,
a.IN_FORCE_DATE,
a.FIXED_DUE_DATE,
a.DUE_DATE,
a.REPAYMENT_AMOUNT,
a.ACTUAL_AMOUNT,
a.REDUCE_AMOUNT,
a.LAST_ACTUAL_REPAYMENT_TYPE,
a.LAST_ACTUAL_REPAYMENT_AMOUNT,
a.ACTUAL_REPAYMENT_AMOUNT,
a.ACTUAL_REPAY_DATE,
a.ROLLOVER_AMOUNT_PAID,
a.ROLLOVER_FLAG,
a.ORIGINAL_LOAN_BORROW_ID,
a.LAST_PART_AMOUNT,
a.PART_EXTEND,
a.PART_EXTEND_TIMES,
a.PART_EXTEND_DATE,
a.SETTLE_TYPE,
a.PAYOUT_TYPE,
a.REPAY_TYPE,
a.PHOTO_KEY,
a.AGG_TYPE,
a.USER_TYPE,
a.USER_TYPE_CLIENT,
a.USER_TYPE_PRODUCT,
a.DERATING,
a.REBORROW,
a.REBORROW_TIMES,
a.ROLLOVER_TIMES,
a.RISK_RETRY_LAST_DATE,
a.RISK_RETRY_COUNT,
a.RISK_SERIAL_NO,
a.RISK_DECISION_DATE,
a.RISK_DECISION_RESULT,
a.OPERATOR,
a.APPROVAL_DATE,
a.APPROVAL_MSG,
a.AMOUNT,
a.PERIODS,
a.PERIOD_TENURE,
a.PERIOD_UNIT,
a.GST,
a.INTEREST_FEE_RATE,
a.ASSESSMENT_FEE_RATE,
a.EXTEND_FEE_RATE,
a.PENALTY_FEE_RATE,
a.PART_INTEREST_FEE_RATE,
a.FEE_TYPE,
a.RISK_MERCHANT_NO,
a.RISK_SCENE_CODE,
a.RISK_PRODUCT_CODE,
a.INFO_DEVICE_ID,
a.REG_CHANNEL,
a.DOWN_CHANNEL,
a.MEDIA_SOURCE,
a.CREATE_LOCK_DATE,
a.LAST_MODIFIED_DATE,
a.ARCHIVED,
a.COUNTRY_CODE,
a.START_TIME,
a.END_TIME
)
values(b.LOAN_BORROW_ID,
b.V2_PLATFORM_ID,
b.V2_MERCHANT_ID,
b.V2_APP_ID,
b.V2_CLIENT_ID,
b.V2_PRODUCT_ID,
b.USER_ID,
b.SERIAL_NO,
b.STATE,
b.STATE_MSG,
b.LEND_SERIAL_NO,
b.LEND_RETRY_COUNT,
b.LEND_RETRY_LAST_DATE,
b.LEND_RETRY_NEXT_DATE,
b.LEND_MSG,
b.PHONE,
b.USER_NAME,
b.AADHAAR_NO,
b.ACCOUNT_BANK_ID,
b.LEND_ACCOUNT_NO,
b.LEND_IFSC_CODE,
b.LEND_BANK_NAME,
b.REPAY_ACCOUNT_NO,
b.REPAY_IFSC_CODE,
b.CREATED_DATE,
b.CONFIRM_DATE,
b.IN_FORCE_DATE,
b.FIXED_DUE_DATE,
b.DUE_DATE,
b.REPAYMENT_AMOUNT,
b.ACTUAL_AMOUNT,
b.REDUCE_AMOUNT,
b.LAST_ACTUAL_REPAYMENT_TYPE,
b.LAST_ACTUAL_REPAYMENT_AMOUNT,
b.ACTUAL_REPAYMENT_AMOUNT,
b.ACTUAL_REPAY_DATE,
b.ROLLOVER_AMOUNT_PAID,
b.ROLLOVER_FLAG,
b.ORIGINAL_LOAN_BORROW_ID,
b.LAST_PART_AMOUNT,
b.PART_EXTEND,
b.PART_EXTEND_TIMES,
b.PART_EXTEND_DATE,
b.SETTLE_TYPE,
b.PAYOUT_TYPE,
b.REPAY_TYPE,
b.PHOTO_KEY,
b.AGG_TYPE,
b.USER_TYPE,
b.USER_TYPE_CLIENT,
b.USER_TYPE_PRODUCT,
b.DERATING,
b.REBORROW,
b.REBORROW_TIMES,
b.ROLLOVER_TIMES,
b.RISK_RETRY_LAST_DATE,
b.RISK_RETRY_COUNT,
b.RISK_SERIAL_NO,
b.RISK_DECISION_DATE,
b.RISK_DECISION_RESULT,
b.OPERATOR,
b.APPROVAL_DATE,
b.APPROVAL_MSG,
b.AMOUNT,
b.PERIODS,
b.PERIOD_TENURE,
b.PERIOD_UNIT,
b.GST,
b.INTEREST_FEE_RATE,
b.ASSESSMENT_FEE_RATE,
b.EXTEND_FEE_RATE,
b.PENALTY_FEE_RATE,
b.PART_INTEREST_FEE_RATE,
b.FEE_TYPE,
b.RISK_MERCHANT_NO,
b.RISK_SCENE_CODE,
b.RISK_PRODUCT_CODE,
b.INFO_DEVICE_ID,
b.REG_CHANNEL,
b.DOWN_CHANNEL,
b.MEDIA_SOURCE,
b.CREATE_LOCK_DATE,
b.LAST_MODIFIED_DATE,
b.ARCHIVED,
b.COUNTRY_CODE,
b.IN_FORCE_DATE,
sysdate
);
v_end_dt :=sysdate;
INSERT INTO T_LOG VALUES
('sp_llb','LOAN_BORROW_INFO_LLB','success',v_start_dt,v_end_dt,'sp_llb成功');
exception when others then
ROLLBACK;
INSERT INTO T_LOG VALUES
('sp_llb','LOAN_BORROW_INFO_LLB','fail',v_start_dt,sysdate,'sp_llb失败'||sqlerrm);
commit;
RAISE;
end;业务需求要求如果同一个LOAN_BORROW_ID下所有字段数据都相同则只更改END_TIME，只要有一个字段的数据不同就需要插入新的数据，该怎么改？
扫一扫
182
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
