create or replace procedure MKT_TO_HUAXIAO_ROWNUM(i_size in number,i_campaign_id in varchar2) isv_count number(16); --变量 每个地市总数v_str_sql varchar2(4000); v_all_count number(16) default 0; --所有地市当前月派单总数 v_difference_value number(16) default 0; --差值current_count number(16) default 0; --当前月派单总数beginfor r in (select group_idfrom mkt_sregioncodewhere group_id != '17'and group_id != '10042') loopv_str_sql := 'select count(1)from dbsalesadm.mk_task_info_' || r.group_id ||' where act_id =' || i_campaign_id ||' and create_Date betweentrunc(add_months(last_day(sysdate), -1) + 1, ''dd'') and trunc(last_day(sysdate), ''dd'')';EXECUTE IMMEDIATE v_str_sqlinto v_count;v_all_count := v_all_count + v_count;end loop;v_difference_value := i_size - v_all_count;if v_difference_value = 0 thenupdate contact_orderset status_cd = '99',remark = '该要每月数量小于6w' || v_difference_valuewhere trunc(create_Date, 'dd') = trunc(sysdate, 'dd')and mkt_campaign_id = i_campaign_id;elsif v_difference_value > 0 thenupdate contact_orderset status_cd = '99',remark = '该要每月数量小于6w' || v_difference_valuewhere trunc(create_Date, 'dd') = trunc(sysdate, 'dd')and mkt_campaign_id = i_campaign_id;elseselect count(1)into current_countfrom contact_orderwhere trunc(create_Date, 'dd') = trunc(sysdate, 'dd')and mkt_campaign_id = i_campaign_id;if current_count > v_difference_value thenupdate contact_orderset status_cd = '99',remark = '该要每月数量小于6w' || v_difference_valuewhere trunc(create_Date, 'dd') = trunc(sysdate, 'dd')and mkt_campaign_id = i_campaign_idand rownum <= current_count - v_difference_value;end if;end if;
end MKT_TO_HUAXIAO_ROWNUM;