存储过程死循环的问题

Question

我在单步执行的时候，数据都插进表里去啦，可存储过程还是在执行中，请问下，是什么原因造成的死循环啊CREATE OR REPLACE PROCEDURE pre_sumday_new  ( ilocalnetid IN NUMBER,
                                          ilocalnetname  IN VARCHAR2,
                                          ilocalnetdate in VARCHAR2
)isBEGIN
insert into  sumday (sitename,revisesum,revisequick,reviseboss,newsum,newquick,newboss,quicksum,bosssum,sumall,rate,regionname,regionid,sumdate)
select tab.sitename,tab1.a2, tab2.a2, COALESCE (tab1.a2,0)-COALESCE (tab2.a2,0),tab3.a2,tab4.a2,  COALESCE (tab3.a2,0)-COALESCE (tab4.a2,0),
       COALESCE (tab2.a2,0)+COALESCE (tab4.a2,0),COALESCE (tab1.a2,0)-COALESCE (tab2.a2,0)+COALESCE (tab3.a2,0)-COALESCE (tab4.a2,0),
       COALESCE (tab1.a2,0)+COALESCE (tab3.a2,0),
       case when COALESCE (tab1.a2,0)+COALESCE (tab3.a2,0)=0
            then 0
            when COALESCE (tab1.a2,0)+COALESCE (tab3.a2,0)<>0
            then (COALESCE (tab2.a2,0)+COALESCE (tab4.a2,0))/(COALESCE (tab1.a2,0)+COALESCE (tab3.a2,0))
            end case,
       ilocalnetname,ilocalnetid,to_date(ilocalnetdate,'yyyymmdd')
-- revisesum 改类合计
from (select distinct d.name as sitename
                          from ls65_crm2.cust_indent_t@to_a2svc_query a,
                               ls65_crm2.region_t@to_a2svc_query      b,
                               ls65_crm2.p_serv_t@to_a2svc_query      c,
                               ls65_crm2.site_t@to_a2svc_query        d,
                               ls65_crm2.sales_indent_t@to_a2svc_query  e
                         where a.site_id = d.ioid_id0
                           and substr(a.region_id, 1, 4) = b.ioid_id0
                           and substr(a.region_id, 1, 4) = ilocalnetid
                           and c.state in ('F0A', 'F0H')
                           and c.agreement_id = a.agreement_id
                           and a.indent_state not in ('500', '600')
                           and c.product_offer_id in
                               (select offer_id
                                  from ls65_crm2.product_offer_t@to_a2svc_query
                                 where (offer_name like '%E家%'  or offer_name like '%E9%'  or  offer_name like '%E8%' or  offer_name like '%E6%' or offer_name like '%e家%'  or offer_name like '%e9%'  or  offer_name like '%e8%' or  offer_name like '%e6%'
                                       )
                                   and region_id in ('10', ilocalnetid))
                             -- and a.sales_indent_id in (select sales_indent_id from ls65_crm2.itsupport_order_log_t@to_a2svc_query)
                           and to_char(a.create_date, 'yyyymmdd') =  ilocalnetdate
                    --           to_char(sysdate - 4, 'yyyymmdd')
                           and exists
                         (select 1
                                  from ls65_crm2.cust_indent_attr_t@to_a2svc_query b
                                 where b.server_offer_id in
                                       (select service_offer_id
                                          from ls65_crm2.service_offer_t@to_a2svc_query t1
                                         where (t1.service_offer_name like
                                               '%新装%' or t1.service_offer_name like
                                               '%主产品改商品%'))
                                   and b.agreement_id = a.agreement_id
                                 )and a.sales_indent_id=e.sales_indent_id  and e.channel_segment_id=1) tab

Answer 1

left  join (select distinct d.name as a1,
                          count(distinct a.SALES_INDENT_ID) as a2
            from ls65_crm2.cust_indent_t@to_a2svc_query a,
                 ls65_crm2.region_t@to_a2svc_query      b,
                 ls65_crm2.p_serv_t@to_a2svc_query      c,
                 ls65_crm2.site_t@to_a2svc_query        d,
                 ls65_crm2.sales_indent_t@to_a2svc_query  e
           where a.site_id = d.ioid_id0
             and substr(a.region_id, 1, 4) = b.ioid_id0
             and substr(a.region_id, 1, 4) = ilocalnetid
             and c.state in ('F0A', 'F0H')
             and c.agreement_id = a.agreement_id
             and a.indent_state not in ('500', '600')
             and c.product_offer_id in
                 (select offer_id
                    from ls65_crm2.product_offer_t@to_a2svc_query
                   where (offer_name like '%E家%'  or offer_name like '%E9%'  or  offer_name like '%E8%' or  offer_name like '%E6%' or offer_name like '%e家%'  or offer_name like '%e9%'  or  offer_name like '%e8%' or  offer_name like '%e6%' )
                     and region_id in ('10', ilocalnetid))
                --and a.sales_indent_id in (select sales_indent_id from ls65_crm2.itsupport_order_log_t@to_a2svc_query)
             and to_char(a.create_date, 'yyyymmdd') = ilocalnetdate
          --       to_char(sysdate -4, 'yyyymmdd')
             and exists
           (select 1
                    from ls65_crm2.cust_indent_attr_t@to_a2svc_query b
                   where b.server_offer_id in
                         (select service_offer_id
                            from ls65_crm2.service_offer_t@to_a2svc_query t1
                           where (t1.service_offer_name like '%主产品改商品%'))
                     and b.agreement_id = a.agreement_id)
                     and a.sales_indent_id=e.sales_indent_id  and e.channel_segment_id=1
           group by d.name) tab1
           on tab.sitename=tab1.a1

Answer 2

           commit;
END pre_sumday_new;

Answer 3

这样子的语句怎么可能会是死循环呢?
建议是有UPDATE或是INSERT的语句后,最好是加一句SQLCODE来看一下语句的更新或是插入有没有成功!

Answer 4

这个过程里只有一句insert into吧，单独执行insert的时候能成功吗

Answer 5

分步设置断点来测试!!就是每个insert into 的SQL中后面看一下SQL.CODE看一下是否有错误的,是不是有有些SELECT的语句选择出来的行数超过一行？

Answer 6

EXCEPTION 
    WHEN   OTHERS THEN 
         DBMS_OUTPUT.put_line('error: ' ||TO_CHAR (SQLCODE) ||'   ' ||SQLERRM); 
         RAISE; 增这个看有没有错误抛出？

Answer 7

好，我试试，我放在COMMIT;前看看

Answer 8

不存在循环，哪来的死循环
看看是否在程序段被多次调用
或是 sumday 表上有相关触发器