oracle存储过程--动态sql执行

最新推荐文章于 2024-06-19 10:08:15 发布

YangTG5号

最新推荐文章于 2024-06-19 10:08:15 发布

阅读量564

点赞数

分类专栏： oracle 存储过程

本文链接：https://blog.csdn.net/java821643/article/details/103726138

版权

oracle 同时被 2 个专栏收录

4 篇文章

订阅专栏

存储过程

3 篇文章

订阅专栏

本文介绍了一个使用PL/SQL编写的存储过程，用于批量更新数据库中的联系订单状态，并计算分配给不同地区销售任务的总数量与目标数量之间的差异。通过执行SQL查询和动态SQL语句，此过程能够有效地处理大量数据，确保每月销售任务的正确性和完整性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create or replace procedure MKT_TO_HUAXIAO_ROWNUM(i_size        in number,
                                                  i_campaign_id in varchar2) is
                                                  
  

  v_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; --当前月派单总数

begin

  for r in (select group_id
              from mkt_sregioncode
             where group_id != '17'
               and group_id != '10042') loop
    v_str_sql := 'select count(1)
  from dbsalesadm.mk_task_info_' || r.group_id ||
                 ' where act_id =' || i_campaign_id ||
                 ' and create_Date between
       trunc(add_months(last_day(sysdate), -1) + 1, ''dd'') and trunc(last_day(sysdate), ''dd'')';
    EXECUTE IMMEDIATE v_str_sql
      into 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 then
    update contact_order
       set status_cd = '99',
           remark    = '该要每月数量小于6w' || v_difference_value
     where trunc(create_Date, 'dd') = trunc(sysdate, 'dd')
       and mkt_campaign_id = i_campaign_id;
  elsif v_difference_value > 0 then
    update contact_order
       set status_cd = '99',
           remark    = '该要每月数量小于6w' || v_difference_value
     where trunc(create_Date, 'dd') = trunc(sysdate, 'dd')
       and mkt_campaign_id = i_campaign_id;
  else
    select count(1)
      into current_count
      from contact_order
     where trunc(create_Date, 'dd') = trunc(sysdate, 'dd')
       and mkt_campaign_id = i_campaign_id;

    if current_count > v_difference_value then
      update contact_order
         set status_cd = '99',
             remark    = '该要每月数量小于6w' || v_difference_value
       where trunc(create_Date, 'dd') = trunc(sysdate, 'dd')
         and mkt_campaign_id = i_campaign_id
         and rownum <= current_count - v_difference_value;
    end if;

  end if;
end MKT_TO_HUAXIAO_ROWNUM;