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行出异常
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行出异常
改成
execute immediate 'select sum(ysje) from '||FISRT_MONTH||' where SERV_ID = '||v_serv_id into AVG_ARPU ;
(
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
);这个是插入语句
还有下面参数好像写错了,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 ;
再执行,看看有什么错误提示
否则begin..end;中没有任何语句会出错
改成
execute immediate 'select sum(ysje) from '||FISRT_MONTH||' where SERV_ID = '||v_serv_id into AVG_ARPU; 还有就是报的是什么错,对象不存在,还是什么其他的错。把你运行时的错误提示帖上来看看,