create or replace procedure P_SYNC_TA_PRODUCT_TEST(v_res OUT NUMBER, v_errorCode OUT NVARCHAR2, v_errorMsg OUT NVARCHAR2, d_dateStartDate IN NVARCHAR2, d_dateEndDate IN NVARCHAR2) IS v_count_updatePolling number(8) := 0; v_yieldRateId NVARCHAR2(36); v_productCode varchar2(36); v_yieldLevelCode NVARCHAR2(10); v_yieldRate NVARCHAR2(20); v_yieldRateStr NVARCHAR2(20); v_investmentIntervalS NVARCHAR2(20); v_investmentIntervalSStr NVARCHAR2(20); v_investmentIntervalD NVARCHAR2(20); v_investmentIntervalDStr NVARCHAR2(20); v_dueTime NVARCHAR2(20); v_dueTimeUnit NVARCHAR2(2); v_annualDays NVARCHAR2(5); v_confirmSql varchar2(600); v_confirmCommaSql varchar2(600); v_updateYieldRateSql varchar2(700);
CURSOR updateProductInfos IS select t.c_fundcode as fundCode, t.c_fundname as fundName, to_char(t.d_setupdate, 'yyyy-MM-dd') as setupDateStr, to_char(t.d_contractenddate, 'yyyy-MM-dd') as contractendDateStr from tfundinfo@HSTA t inner join t_polling_product tpp on tpp.c_product_code = t.c_fundcode and tpp.delete_flag = '0' where 1 = 1 and to_char(t.d_lastmodifydate, 'yyyy-MM-dd') >= to_char(to_date(d_dateStartDate, 'yyyy-MM-dd'), 'yyyy-MM-dd') and to_char(t.d_lastmodifydate, 'yyyy-MM-dd') <= to_char(to_date(d_dateEndDate, 'yyyy-MM-dd'), 'yyyy-MM-dd') and t.c_fundstatus <> '6' and (t.c_fundname <> tpp.c_product_name or to_char(t.d_setupdate, 'yyyy-MM-dd') <> to_char(tpp.c_found_date, 'yyyy-MM-dd') or to_char(t.d_contractenddate, 'yyyy-MM-dd') <> to_char(tpp.c_end_date, 'yyyy-MM-dd')); CURSOR margeProductYieldRates IS select t.c_fundcode AS fundCode, tp.c_profitclass AS profitClass, tp.f_profit*100 AS profit, tp.f_amountmin AS amountMin, tp.f_amountmax AS amountMax, tp.c_durationtime AS durationTime, case tp.c_durationtimeunit when '0' then 'Y' when '1' then 'M' when '2' then 'D' else '' end as durationTimeUnit, case tp.c_bonusfrequency when '0' then '04' when '1' then '04' when '2' then '04' when '3' then '04' when '4' then '04' when '5' then '04' when '6' then '02' when '7' then '03' else '' end as bonusFrequencyType, case tp.c_bonusfrequency when '2' then 'M' when '3' then 'Q' when '4' then 'H' when '5' then 'Y' else '' end as bonusFrequency, tp.l_incomeyeardays as incomeYearDays, NVL(to_char(t.d_setupdate, 'yyyy-MM-dd'), to_char(sysdate, 'yyyy-MM-dd')) AS yieldStartDateStr from tfundinfo@HSTA t inner join ttrustfundprofit@HSTA tp on tp.c_fundcode = t.c_fundcode where 1 = 1 and to_char(t.d_lastmodifydate, 'yyyy-MM-dd') >= to_char(to_date(d_dateStartDate, 'yyyy-MM-dd'), 'yyyy-MM-dd') and to_char(t.d_lastmodifydate, 'yyyy-MM-dd') <= to_char(to_date(d_dateEndDate, 'yyyy-MM-dd'), 'yyyy-MM-dd') and t.c_fundstatus <> '6' and t.c_fundcode in ('CA0FX6','CA0FUC'); begin DBMS_OUTPUT.ENABLE(buffer_size => null); DBMS_OUTPUT.put_line('----------------start------------------'); select count(*) into v_count_updatePolling from tfundinfo@HSTA t inner join t_polling_product tpp on tpp.c_product_code = t.c_fundcode where 1 = 1 and to_char(t.d_lastmodifydate, 'yyyy-MM-dd') >= to_char(to_date(d_dateStartDate, 'yyyy-MM-dd'), 'yyyy-MM-dd') and to_char(t.d_lastmodifydate, 'yyyy-MM-dd') <= to_char(to_date(d_dateEndDate, 'yyyy-MM-dd'), 'yyyy-MM-dd') and t.c_fundstatus <> '6'; if v_count_updatePolling > 0 then
for margeProductYieldRate IN margeProductYieldRates loop if margeProductYieldRate.Fundcode is not null then
select tpy.c_product_code, tpy.c_yield_level_code, tpy.c_yield_rate, tpy.c_investment_interval_s, tpy.c_investment_interval_d, tpy.c_due_time, tpy.c_due_time_unit, tpy.c_annual_days into v_productCode, v_yieldLevelCode, v_yieldRate, v_investmentIntervalS, v_investmentIntervalD, v_dueTime, v_dueTimeUnit, v_annualDays from t_product_yield_rate tpy where tpy.c_yield_level_code = margeProductYieldRate.Profitclass and tpy.c_product_code = margeProductYieldRate.Fundcode and tpy.delete_flag = '0'; IF v_productCode is not null then
dbms_output.put_line('开始拼接SQL,产品代码为:' || margeProductYieldRate.Fundcode||','||'受益级别为:'||margeProductYieldRate.Profitclass); v_yieldRateStr := to_char(v_yieldRate, 'fm99999999990.00'); v_investmentIntervalSStr := to_char(v_investmentIntervalS, 'fm99999999990.00'); v_investmentIntervalDStr := to_char(v_investmentIntervalD, 'fm99999999990.00'); dbms_output.put_line('1.收益率,family:' ||v_yieldRateStr||';'||'TA:'||to_char(margeProductYieldRate.Profit, 'fm99999999990.00')); if v_yieldRateStr is not null and margeProductYieldRate.Profit is not null then if v_yieldRateStr <> to_char(margeProductYieldRate.Profit, 'fm99999999990.00') then v_confirmSql := v_confirmSql||'t.c_yield_rate='||to_char(margeProductYieldRate.Profit, 'fm99999999990.00')||','; end if; elsif v_yieldRateStr is null and to_char(margeProductYieldRate.Profit, 'fm99999999990.00') is not null then v_confirmSql := v_confirmSql||'t.c_yield_rate='||to_char(margeProductYieldRate.Profit, 'fm99999999990.00')||','; elsif v_yieldRateStr is not null and to_char(margeProductYieldRate.Profit, 'fm99999999990.00') is null then v_confirmSql := v_confirmSql||'t.c_yield_rate=null,'; end if; dbms_output.put_line('2.投资上限,family:' || v_investmentIntervalSStr||';'||'TA:'||to_char(margeProductYieldRate.Amountmin, 'fm99999999990.00')); if v_investmentIntervalSStr is not null and to_char(margeProductYieldRate.Amountmin, 'fm99999999990.00') is not null then if v_investmentIntervalSStr <> to_char(margeProductYieldRate.Amountmin, 'fm99999999990.00') then v_confirmSql := v_confirmSql||'t.c_investment_interval_s='||to_char(margeProductYieldRate.Amountmin, 'fm99999999990.00')||','; end if; elsif v_investmentIntervalSStr is null and to_char(margeProductYieldRate.Amountmin, 'fm99999999990.00') is not null then v_confirmSql := v_confirmSql||'t.c_investment_interval_s='||to_char(margeProductYieldRate.Amountmin, 'fm99999999990.00')||','; elsif v_investmentIntervalSStr is not null and to_char(margeProductYieldRate.Amountmin, 'fm99999999990.00') is null then v_confirmSql := v_confirmSql||'t.c_investment_interval_s=null,'; end if; dbms_output.put_line('2.投资下限,family:' || v_investmentIntervalDStr||';'||'TA:'||to_char(margeProductYieldRate.Amountmax, 'fm99999999990.00')); if v_investmentIntervalDStr is not null and to_char(margeProductYieldRate.Amountmax, 'fm99999999990.00') is not null then if v_investmentIntervalDStr <> to_char(margeProductYieldRate.Amountmax, 'fm99999999990.00') then v_confirmSql := v_confirmSql||'t.c_investment_interval_d='||to_char(margeProductYieldRate.Amountmax, 'fm99999999990.00')||','; end if; elsif v_investmentIntervalDStr is null and to_char(margeProductYieldRate.Amountmax, 'fm99999999990.00') is not null then v_confirmSql := v_confirmSql||'t.c_investment_interval_d='||to_char(margeProductYieldRate.Amountmax, 'fm99999999990.00')||','; elsif v_investmentIntervalDStr is not null and to_char(margeProductYieldRate.Amountmax, 'fm99999999990.00') is null then v_confirmSql := v_confirmSql||'t.c_investment_interval_d=null,'; end if; dbms_output.put_line('3.投资期限,family:' || v_dueTime||';'||'TA:'||margeProductYieldRate.Durationtime); if v_dueTime is not null and margeProductYieldRate.Durationtime is not null then if v_dueTime <> margeProductYieldRate.Durationtime then v_confirmSql := v_confirmSql||'t.c_due_time='||margeProductYieldRate.Durationtime||','; end if; elsif v_dueTime is null and margeProductYieldRate.Durationtime is not null then v_confirmSql := v_confirmSql||'t.c_due_time='||margeProductYieldRate.Durationtime||','; elsif v_dueTime is not null and margeProductYieldRate.Durationtime is null then v_confirmSql := v_confirmSql||'t.c_due_time=null,'; end if; dbms_output.put_line('4.投资期限单位,family:' || v_dueTimeUnit||';'||'TA:'||margeProductYieldRate.Durationtimeunit); if v_dueTimeUnit is not null and margeProductYieldRate.Durationtimeunit is not null then if v_dueTimeUnit <> margeProductYieldRate.Durationtimeunit then v_confirmSql := v_confirmSql||'t.c_due_time_unit='||''''||margeProductYieldRate.Durationtimeunit||''''||','; end if; elsif v_dueTimeUnit is null and margeProductYieldRate.Durationtimeunit is not null then v_confirmSql := v_confirmSql||'t.c_due_time_unit='||''''||margeProductYieldRate.Durationtimeunit||''''||','; elsif v_dueTimeUnit is not null and margeProductYieldRate.Durationtimeunit is null then v_confirmSql := v_confirmSql||'t.c_due_time_unit=null,'; end if; dbms_output.put_line('5.年化计算天数,family:' || v_annualDays||';'||'TA:'||margeProductYieldRate.Incomeyeardays); if v_annualDays is not null and margeProductYieldRate.Incomeyeardays is not null then if v_annualDays <> margeProductYieldRate.Incomeyeardays then v_confirmSql := v_confirmSql||'t.c_annual_days'||''''||margeProductYieldRate.Incomeyeardays||''''||','; end if; elsif v_annualDays is null and margeProductYieldRate.Incomeyeardays is not null then v_confirmSql := v_confirmSql||'t.c_annual_days='||''''||margeProductYieldRate.Incomeyeardays||''''||','; elsif v_annualDays is not null and margeProductYieldRate.Incomeyeardays is null then v_confirmSql := v_confirmSql||'t.c_annual_days=null,'; end if; if v_confirmSql is not null then select substr(v_confirmSql,1,length(v_confirmSql)-1) into v_confirmCommaSql from dual; v_updateYieldRateSql := 'update t_product_yield_rate t set '||v_confirmCommaSql||' where t.c_product_code=:1 and t.c_yield_level_code=:2'; dbms_output.put_line('拼接的SQL为:' || v_updateYieldRateSql); execute immediate v_updateYieldRateSql USING margeProductYieldRate.Fundcode, margeProductYieldRate.Profitclass; v_confirmSql := ''; v_confirmCommaSql := ''; v_updateYieldRateSql := ''; end if; else select concat(to_char(sysdate, 'yyyyMMddHHmmss'), trunc(dbms_random.value(100000, 999999))) into v_yieldRateId from dual; dbms_output.put_line('存在新增的受益级别,产品代码为:'||margeProductYieldRate.Fundcode||',受益级别为:'||margeProductYieldRate.Profitclass); insert into T_PRODUCT_YIELD_RATE (T_PRODUCT_YIELD_RATE.C_YIELD_RATE_ID, T_PRODUCT_YIELD_RATE.C_PRODUCT_CODE, T_PRODUCT_YIELD_RATE.C_PRODUCT_MANAGER_CODE, T_PRODUCT_YIELD_RATE.C_INVESTMENT_INTERVAL_S, T_PRODUCT_YIELD_RATE.C_INVESTMENT_INTERVAL_D, T_PRODUCT_YIELD_RATE.C_YIELD_RATE, T_PRODUCT_YIELD_RATE.DELETE_FLAG, T_PRODUCT_YIELD_RATE.CREATE_USER_ID, T_PRODUCT_YIELD_RATE.CREATE_TIME, T_PRODUCT_YIELD_RATE.UPDATE_USER_ID, T_PRODUCT_YIELD_RATE.UPDATE_TIME, T_PRODUCT_YIELD_RATE.C_YIELD_START_DATE, T_PRODUCT_YIELD_RATE.C_YIELD_END_DATE, T_PRODUCT_YIELD_RATE.C_CHANGE_REASON, T_PRODUCT_YIELD_RATE.C_YIELD_LEVEL_CODE, T_PRODUCT_YIELD_RATE.C_ALLOCATION_TYPE, T_PRODUCT_YIELD_RATE.C_ALLOCATION_TYPE_SP, T_PRODUCT_YIELD_RATE.C_ALLOCATION_TYPE_MD, T_PRODUCT_YIELD_RATE.C_ANNUAL_DAYS, T_PRODUCT_YIELD_RATE.C_DUE_TIME, T_PRODUCT_YIELD_RATE.C_DUE_TIME_UNIT) values (v_yieldRateId, margeProductYieldRate.Fundcode, '0', margeProductYieldRate.Amountmin, margeProductYieldRate.Amountmax, margeProductYieldRate.Profit, '0', '00000000000000000000', sysdate, '00000000000000000000', sysdate, to_date(margeProductYieldRate.Yieldstartdatestr, 'yyyy-MM-dd'), to_date('9999-12-31', 'yyyy-MM-dd'), '', margeProductYieldRate.Profitclass, margeProductYieldRate.Bonusfrequencytype, margeProductYieldRate.Bonusfrequency, '', margeProductYieldRate.Incomeyeardays, margeProductYieldRate.Durationtime, margeProductYieldRate.Durationtimeunit); end if; end if; end loop; end if; v_res := 0; v_errorCode := SQLCODE; v_errorMsg := 'P_SYNC_TA_PRODUCT_TEST' || ':' || TO_CHAR(SQLERRM); DBMS_OUTPUT.put_line('----------------end------------------'); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('----------------error------------------'); ROLLBACK; v_res := -1; v_errorCode := SQLCODE; v_errorMsg := 'P_SYNC_TA_PRODUCT_TEST' || ':' || TO_CHAR(SQLERRM); end P_SYNC_TA_PRODUCT_TEST;
|