急急急！oracle报PLS-00103的错误，插入语句拿出来是对的，放到存储过程中就错了

注释掉的那部分后报标题的错误，不注释报好多错误！但是插入语句单独拿出来是对的！

create or replace procedure SPLifepr09SalesBase1(sp_computedate_in varchar2) is
iStatDate     DATE          :=to_date(sp_computedate_in, 'YYYY-MM-DD');--统计日期

begin
  --删除当天的数据
/*  delete from klbioffice.T09SalesBese where StatDate = iStartDate;*/
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'01')
                                            and s.statdate<=biofficeend(iStatDate,'01');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'02')
                                            and s.statdate<=biofficeend(iStatDate,'02');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'06')
                                            and s.statdate<=biofficeend(iStatDate,'06');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'08')
                                            and s.statdate<=biofficeend(iStatDate,'08');

end;
   --插入语句
  insert into T09SalesBase
    (
    BRANCH_ID,
    branch_name,
    Channel_Id, 
    TEAM_ID,
    team_name,
    LEADER_ID,
    LEADER_Name,
    STAT,
    IS_QUALICERT,
    Sales_id,
    SALES_NAME,
    PROBATION_DATE,
    DISMISS_DATE,
    StartDate,
    EndDate,
    StatDate,
    RANKNAME,
    RANK,
    SourceType,
    ContractType,
    SexType,
    Age,
    AgeType,
    NumSales,
    NumNew,
    NumLost,
    insert_time
    )
  
    select distinct b.branch_id brand_id,
            b.branch_name        branch_name,
            s.channel_id         Channel_Id,
            t.team_id            team_id ,
            t.team_name          team_name,
            s.leader_id          leader_id,
            /*(select nvl(( 
                       select ss.sales_name
                       from t02salesinfo ss
                       where trim(ss.sales_id) =trim(s.leader_id)),'' )    
             from dual) */     ''              
                                 LEADER_Name,
            s.stat               stat,
            s.Is_Qualicert       Is_Qualicert,
            s.sales_id           sales_id,
            s.sales_name         sales_name,
            s.Probation_Date     Probation_Date,
            s.dismiss_date       iDISMISS_DATE,
            biofficestart(iStatDate,s.channel_id)
                                 StartDate,
            biofficeend(iStatDate,s.channel_id)
                                 EndDate,
            iStatDate            StatDate,
            /*(select nvl((select tr.rankname
                from amisstand.t_rankdef tr
                where trim(tr.rankid) =  trim(s.rank)
                and   trim(tr.channeltype) = trim(s.channel_id) ),'')
                from dual) */       ''             
                                 RANKNAME,
            s.rank               rank,
            case when s.IS_SAME_VOCATION='01' then  '01'
                 else '02'
                 end              SourceType,
            case when (count(s.sales_id)>=1) then '01' else '02' end 
                                  ContractType,--???
            s.sex                 sextype,
            round(avg(trunc(months_between(iStatDate, s.birthday)/12)),2) 
                                  age,
            case when round(avg(trunc(months_between(iStatDate, s.birthday)/12)),2)<30
                 then '01'
                 else '02'
                 end              AgeType,
          /* (select case when ((s1.stat = '2' and s1.dismiss_date >= iStatDate) or
                          (s1.stat = '1'
                          and s1.dismiss_date is null
                          and s1.PROBATION_DATE <=iStatDate))
                 then  1
                 else  0
                 end
          from   t02salesinfo  s1
          where trim(s1.branch_id)= trim(b.branch_id)
          and trim(s1.channel_id)= trim(s.channel_id)
          and trim(s1.team_id) = trim(t.team_id)
          and trim(s1.sales_id)= trim(s.sales_id))  */ ''                 
                                 NumSales,
         /* (select case when (s1.PROBATION_DATE >= biofficestart(iStatDate,s.channel_id)and
                       s1.PROBATION_DATE<=iStatDate)
                 then  1
                 else  0
                 end
           from   t02salesinfo  s1
           where trim(s1.branch_id)= trim(b.branch_id)
           and trim(s1.channel_id)= trim(s.channel_id)
           and trim(s1.team_id) = trim(t.team_id)
           and trim(s1.sales_id)= trim(s.sales_id))*/ ''
                                 NumNew,
         /*  (select case when (s1.dismiss_date >= biofficestart(iStatDate,s.channel_id)and
                       s1.dismiss_date<=iStatDate)
                 then 1
                 else 0
                 end
           from   t02salesinfo  s1
           where trim(s1.branch_id)= trim(b.branch_id)
           and trim(s1.channel_id)= trim(s.channel_id)
           and trim(s1.team_id) = trim(t.team_id)
           and trim(s1.sales_id)= trim(s.sales_id))*/''
                                 NumLost,
           sysdate               insert_time
                                 
                                 
                                 
                                 
     from  t01branchinfo b,
           t01teaminfo   t,
           t02salesinfo  s
     where trim(b.branch_id) = trim(t.branch_id)
       and trim(t.branch_id) = trim(s.branch_id)
       and trim(t.channel_id) = trim(s.channel_id)
       and trim(t.team_id) = trim(s.team_id)
     group by
           s.channel_id,
           b.branch_id,
           b.branch_name,
           t.team_id,
           t.team_name,
           s.leader_id,
           s.Is_Qualicert,
           s.sales_id,
           s.sales_name,
           s.sex,
           s.rank,
           s.stat,
           s.Probation_Date,
           s.IS_SAME_VOCATION,
           s.dismiss_date
      ;
    
    


  --提交事务
  commit;
  end SPLifepr09SalesBase1;

13 个解决方案

#1

哇塞 结帖率：103.03% 
莫非楼主帮人家结贴

#2

-- CSDN 的DBA最近闲工资少，瞎统计：结帖率：103.03%

#3

show error 看下具体都有些什么错，然后再改

#4

引用 2 楼 luoyoumou 的回复:

-- CSDN 的DBA最近闲工资少，瞎统计：结帖率：103.03%

罗哥，莫非你有内幕消息

#5

这是因为楼主有被删除的帖子。

#6

引用 5 楼 sunlaji008 的回复:

这是因为楼主有被删除的帖子。

莫非你帖子被河蟹过

#7

引用 6 楼 gelyon 的回复:

引用 5 楼 sunlaji008 的回复:
这是因为楼主有被删除的帖子。

莫非你帖子被河蟹过

嗯嗯

#8

引用 6 楼 gelyon 的回复:

引用 5 楼 sunlaji008 的回复:
这是因为楼主有被删除的帖子。

莫非你帖子被河蟹过

俺的结贴率也超过100%啦

#9

哈哈，问题解决！
begin
  --删除当天的数据
/*  delete from klbioffice.T09SalesBese where StatDate = iStartDate;*/
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'01')
                                            and s.statdate<=biofficeend(iStatDate,'01');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'02')
                                            and s.statdate<=biofficeend(iStatDate,'02');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'06')
                                            and s.statdate<=biofficeend(iStatDate,'06');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'08')
                                            and s.statdate<=biofficeend(iStatDate,'08');

--end;
删掉此end
感谢oracle群的朋友们！
也感谢来回帖的朋友！呵呵

#10

引用楼主 liuxilil 的回复:

注释掉的那部分后报标题的错误，不注释报好多错误！但是插入语句单独拿出来是对的！

SQL code
create or replace procedure SPLifepr09SalesBase1(sp_computedate_in varchar2) is
iStatDate     DATE          :=to_date(sp_computedate_in, 'YYYY-MM-……

show err 显示在哪行 太长

#11

begin
  --删除当天的数据
/*  delete from klbioffice.T09SalesBese where StatDate = iStartDate;*/
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'01')
                                            and s.statdate<=biofficeend(iStatDate,'01');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'02')
                                            and s.statdate<=biofficeend(iStatDate,'02');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'06')
                                            and s.statdate<=biofficeend(iStatDate,'06');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'08')
                                            and s.statdate<=biofficeend(iStatDate,'08');

end;--这个多了


begin
begin--或者在加个begin
  --删除当天的数据
/*  delete from klbioffice.T09SalesBese where StatDate = iStartDate;*/
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'01')
                                            and s.statdate<=biofficeend(iStatDate,'01');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'02')
                                            and s.statdate<=biofficeend(iStatDate,'02');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'06')
                                            and s.statdate<=biofficeend(iStatDate,'06');
  delete from T09SalesBase  s where s.statdate >=biofficestart(iStatDate,'08')
                                            and s.statdate<=biofficeend(iStatDate,'08');

end;

   --

#12

该回复于2010-11-23 14:30:54被版主删除

#13

该回复于2010-11-23 14:30:55被版主删除