帮我看下这段代码有没有问题？

[hahahjx]

create or replace function F_ARPU_3MONTH( v_serv_id in number)
return number
is
AVG_ARPU number := 0;
AVG_ARPU_TMP number := 0;
FISRT_MONTH varchar2(30) :='tjbb10.real_srkh_kh_'||to_char(sysdate,'yyyymm');
SECOND_MONTH varchar2(30) :='tjbb10.real_srkh_kh_'||to_char(sysdate-1,'yyyymm');
THIRD_MONTH varchar2(30) :='tjbb10.real_srkh_kh_'||to_char(sysdate-2,'yyyymm');
begin
begin
execute immediate 'select sum(ysje) into AVG_ARPU from '||FISRT_MONTH||' where SERV_ID = '||v_serv_id;
exception
when no_data_found then
AVG_ARPU := 0;
end;
begin
execute immediate 'select sum(ysje) into AVG_ARPU from '||SECOND_MONTH||' where SERV_ID = '||v_serv_id;
exception
when no_data_found then
AVG_ARPU_TMP := 0;
end;
AVG_ARPU := AVG_ARPU + AVG_ARPU_TMP;
AVG_ARPU_TMP := 0;
begin
execute immediate 'select sum(ysje) into AVG_ARPU from '||THIRD_MONTH||' where SERV_ID = '||v_serv_id;
exception
when no_data_found then
AVG_ARPU_TMP := 0;
end;
AVG_ARPU := (AVG_ARPU + AVG_ARPU_TMP) / 3;
return AVG_ARPU;

end F_ARPU_3MONTH;


请大家帮我看看这段代码有没有问题，我在过程里面调用时老是11行出异常

[小灰狼W]

execute immediate 'select sum(ysje) into AVG_ARPU from '||FISRT_MONTH||' where SERV_ID = '||v_serv_id;
改成
execute immediate 'select sum(ysje) from '||FISRT_MONTH||' where SERV_ID = '||v_serv_id into AVG_ARPU ;

[小灰狼W]

下面的也一样，into 。。要写到外面来

[hahahjx]

那样也报错了

[hahahjx]

insert into crm_in_tel_tmp --插入到临时表中
(
DATA_DAY,
SERV_ID,
arpu_3month,
is_arrear,
is_17909_package,
e6_disc,
is_super_radiophone,
tel_addvalue,
load_date
)
values
(
to_number(to_char(sysdate-1,'yyyymmdd')),
tmp_serv_id,
F_ARPU_3MONTH(tmp_serv_id),
to_is_arrear(tmp_serv_id),
to_is_17909(tmp_serv_id),
to_is_e6(tmp_serv_id),
to_is_super_radiophone(tmp_serv_id),
to_tel_addvalue(tmp_serv_id),
sysdate
);

这个是插入语句

[小灰狼W]

还有别的错误，比如月份不能用sysdate-1这样减的只有1天
还有下面参数好像写错了，into的对象应该是AVG_ARPU_TMP吧，不过这都不是语法问题，是逻辑问题

create or replace function F_ARPU_3MONTH( v_serv_id in number) 
return number 
is 
  AVG_ARPU number := 0; 
  AVG_ARPU_TMP number := 0; 
  FISRT_MONTH varchar2(30) :='tjbb10.real_srkh_kh_'||to_char(sysdate,'yyyymm'); 
  SECOND_MONTH varchar2(30) :='tjbb10.real_srkh_kh_'||to_char(add_months(sysdate,-1),'yyyymm'); 
  THIRD_MONTH varchar2(30) :='tjbb10.real_srkh_kh_'||to_char(add_months(sysdate,-2),'yyyymm'); 
begin 
  begin 
      execute immediate 'select sum(ysje) from '||FISRT_MONTH||' where SERV_ID = '||v_serv_id into AVG_ARPU; 
  exception 
      when no_data_found then 
        AVG_ARPU := 0; 
  end; 
  begin 
      execute immediate 'select  sum(ysje) from '||SECOND_MONTH||' where SERV_ID = '||v_serv_id into AVG_ARPU_TMP ; 
  exception 
      when no_data_found then 
        AVG_ARPU_TMP := 0; 
  end; 
  AVG_ARPU := AVG_ARPU + AVG_ARPU_TMP; 
  begin 
      execute immediate 'select sum(ysje) from '||THIRD_MONTH||' where SERV_ID = '||v_serv_id  into AVG_ARPU_TMP; 
  exception 
      when no_data_found then 
        AVG_ARPU_TMP := 0; 
  end; 
  AVG_ARPU := (AVG_ARPU + AVG_ARPU_TMP) / 3; 
  return AVG_ARPU; 

end F_ARPU_3MONTH; 

先不要用insert，先改成过程找出错误以便调试：

declare
  v_serv_id number:=121; --赋个值测试用
  AVG_ARPU number := 0; 
  AVG_ARPU_TMP number := 0; 
  FISRT_MONTH varchar2(30) :='tjbb10.real_srkh_kh_'||to_char(sysdate,'yyyymm'); 
  SECOND_MONTH varchar2(30) :='tjbb10.real_srkh_kh_'||to_char(add_months(sysdate,-1),'yyyymm'); 
  THIRD_MONTH varchar2(30) :='tjbb10.real_srkh_kh_'||to_char(add_months(sysdate,-2),'yyyymm'); 
begin 
  begin 
     -- execute immediate 'select sum(ysje) from '||FISRT_MONTH||' where SERV_ID = '||v_serv_id into AVG_ARPU; 
     --先将字符串用output输出看看是否正确，没问题再执行execute..
   
  exception 
      when no_data_found then 
        AVG_ARPU := 0; 
  end; 
  begin 
     -- execute immediate 'select  sum(ysje) from '||SECOND_MONTH||' where SERV_ID = '||v_serv_id into AVG_ARPU_TMP ; 
     
  exception 
      when no_data_found then 
        AVG_ARPU_TMP := 0; 
  end; 
  AVG_ARPU := AVG_ARPU + AVG_ARPU_TMP; 
  begin 
      --execute immediate 'select sum(ysje) from '||THIRD_MONTH||' where SERV_ID = '||v_serv_id  into AVG_ARPU_TMP; 
  exception 
      when no_data_found then 
        AVG_ARPU_TMP := 0; 
  end; 
  dbms_output.put_line(AVG_ARPU := (AVG_ARPU + AVG_ARPU_TMP) / 3); 
 dbms_output.put_line( 'select sum(ysje) from '||FISRT_MONTH||' where SERV_ID = '||v_serv_id);
 dbms_output.put_line('select  sum(ysje) from '||SECOND_MONTH||' where SERV_ID = '||v_serv_id);
 dbms_output.put_line('select sum(ysje) from '||THIRD_MONTH||' where SERV_ID = '||v_serv_id);

end ; 

[小灰狼W]

看看output输出的字符串是否是你想要的
再执行，看看有什么错误提示

[小灰狼W]

。。execute语句注释掉运行的时候要补一句null;
否则begin..end;中没有任何语句会出错

[hahahjx]

谢谢，问题已经找到了

[inthirties]

execute immediate 'select sum(ysje) into AVG_ARPU from '||FISRT_MONTH||' where SERV_ID = '||v_serv_id;
改成
execute immediate 'select sum(ysje) from '||FISRT_MONTH||' where SERV_ID = '||v_serv_id into AVG_ARPU;

还有就是报的是什么错，

对象不存在，还是什么其他的错。

把你运行时的错误提示帖上来看看，