oracle merge递归报错,奇怪的merge into报错

create or replace procedure add_invest_data_day(p_strday varchar2 default to_char(sysdate-1,'yyyymmdd'),

p_type varchar2,

pn_jobno number,

Pn_Day Number default to_number(to_char(sysdate,'yyyymmdd'))) Is

Start_time varchar2(30);

End_time varchar2(30);

lv_procname varchar2(32) := lower('add_invest_data_day');

ln_rowid rowid;

v_start varchar2(8);

ln_cnt number := 0;

err_info varchar2(200);

pn_code number :=0;

pv_info varchar2(200);

begin

if (p_type = '1') then

v_start := to_char(to_date(p_strday,'yyyymmdd') - 6,'yyyymmdd');

elsif (p_type = '2') then

v_start := to_char(to_date(p_strday,'yyyymmdd') - 30,'yyyymmdd');

end if;

Start_time := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');

P_CHANNEL_JOB_LOG_INFO(pv_procname => lv_procname, pn_stattime => Start_time, pv_info => '开始执行 add_invest_data_day ');

p_ymstat_run_check(pn_job => pn_jobno,

pn_statedate => Pn_Day,

po_code => pn_code,

po_info => pv_info);

if pn_code <> 0 then

P_CHANNEL_JOB_LOG_INFO(pv_procname => lv_procname, pn_stattime => Pn_Day, pv_info => '任务前置检查没通过'||pv_info);

return;

end if;

--任务统计开始，写初始进度日志

insert into t_job_log

(seqno, jobno, statdate, status, ctime, uptime)

values

(seq_job_log.nextval, pn_jobno, Pn_Day, 1, sysdate, sysdate)

returning rowid into ln_rowid;

commit;

insert into rpt_invest_data_day

(str_day,

day_type,

channel_id,

channel_name,

city,

county,

platform,

agent_id,

invest_users_all,

invest_amount_all,

invest_count,

invest_users_new,

invest_amount_new)

select p_strday str_day, p_type,

a.channel_id, a.channel_name, a.city, a.county, a.platform, c.agent_id,

count(distinct a.invest_id) invest_users_all,

sum(a.order_amount)/100 invest_amount_all,

count(order_id) invest_count,

count(distinct case when a.reg_day <= p_strday and a.reg_day >= v_start then a.invest_id else null end) invest_users_new,

sum(case when a.reg_day <= p_strday and a.reg_day >= v_start then a.order_amount else 0 end)/100 invest_amount_new

from agent.base_data_invest_info a

left join (SELECT b.user_name,a.agent_id FROM act.tb_user_agent_relat a,act.tb_user_info b WHERE a.user_id=b.user_id) c on a.user_name=c.user_name

where a.str_day <= p_strday and a.str_day >= v_start

group by a.channel_id, a.channel_name, a.city, a.county, a.platform, c.agent_id;

ln_cnt := sql%rowcount;

MERGE INTO RPT_INVEST_DATA_DAY M

USING (

SELECT BONUS_DAY,CHANNEL_ID,CHANNEL_NAME,CITY,COUNTY,PLATFORM,AGENT_ID,SML_USERS_ALL,SML_AMOUNT_ALL,BIG_USERS_ALL,BIG_AMOUNT_ALL FROM (

SELECT K.BONUS_DAY,

K.CHANNEL_ID,

K.CHANNEL_NAME,

K.CITY,

K.COUNTY,

K.PLATFORM,

D.AGENT_ID,

COUNT(DISTINCT CASE WHEN K.BIG_FLAG = 0 THEN K.INVEST_ID ELSE NULL END) SML_USERS_ALL,

SUM(CASE WHEN K.BIG_FLAG = 0 THEN K.BONUS_AMOUNT ELSE 0 END) / 100 SML_AMOUNT_ALL,

COUNT(DISTINCT CASE WHEN K.BIG_FLAG = 1 THEN K.INVEST_ID ELSE NULL END) BIG_USERS_ALL,

SUM(CASE WHEN K.BIG_FLAG = 1 THEN K.BONUS_AMOUNT ELSE 0 END) / 100 BIG_AMOUNT_ALL

FROM AGENT.BASE_DATA_INVEST_INFO K

LEFT JOIN (SELECT B.USER_NAME,A.AGENT_ID

FROM ACT.TB_USER_AGENT_RELAT A, ACT.TB_USER_INFO B

WHERE A.USER_ID = B.USER_ID) D

ON K.USER_NAME=D.USER_NAME

WHERE BONUS_DAY <= P_STRDAY

AND BONUS_DAY >= V_START

GROUP BY K.CHANNEL_ID,

K.CHANNEL_NAME,

K.CITY,

K.COUNTY,

K.PLATFORM,

D.AGENT_ID,

K.BONUS_DAY) )C

ON (M.CHANNEL_ID = C.CHANNEL_ID AND M.CHANNEL_NAME = C.CHANNEL_NAME AND M.CITY = C.CITY AND M.PLATFORM = C.PLATFORM AND M.COUNTY = C.COUNTY AND M.AGENT_ID = C.AGENT_ID AND M.STR_DAY = C.BONUS_DAY)

WHEN MATCHED THEN

UPDATE

SET M.SML_USERS_ALL  = C.SML_USERS_ALL,

M.SML_AMOUNT_ALL = C.SML_AMOUNT_ALL,

M.BIG_USERS_ALL  = C.BIG_USERS_ALL,

M.BIG_AMOUNT_ALL = C.BIG_AMOUNT_ALL

WHEN NOT MATCHED THEN

INSERT

(STR_DAY,CHANNEL_ID,CHANNEL_NAME,CITY,COUNTY,PLATFORM,AGENT_ID,SML_USERS_ALL,SML_AMOUNT_ALL,BIG_USERS_ALL,BIG_AMOUNT_ALL)

VALUES

(C.BONUS_DAY,C.CHANNEL_ID,C.CHANNEL_NAME,C.CITY,C.COUNTY,C.PLATFORM,C.AGENT_ID,C.SML_USERS_ALL,C.SML_AMOUNT_ALL,C.BIG_USERS_ALL,C.BIG_AMOUNT_ALL);

update rpt_invest_data_day a

set a.arpu = trunc(a.invest_amount_all/a.invest_users_all, 2)

where a.str_day = p_strday and a.invest_users_all > 0;

End_time := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');

P_CHANNEL_JOB_LOG_INFO(pv_procname => lv_procname, pn_stattime => End_time, pv_info => '完成 add_invest_data_day ,插入了: '|| ln_cnt ||'行');

commit;

--更新进度状态表 为已完成

update t_job_log set status = 2, uptime = sysdate where rowid = ln_rowid;

commit;

exception

when others then

--写错误日志

err_info := substr(sqlerrm, 0, 200);

End_time := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');

P_CHANNEL_JOB_ERROR_INFO(pv_procname => lv_procname, pn_stattime => End_time, pv_err => err_info);

raise;

end;