我的三个表
SERV_CRM_IN_200908,SERV_CRM_IN_200907,SERV_CRM_IN_200906
我要写一个函数,根据传入的的SERV_ID取得该ID所对应的ysje字段三个月的平均值,请问该如何解决?
每一个表的sum(ysje)然后在返回时三个表相加,然后再除以3
SERV_CRM_IN_200908,SERV_CRM_IN_200907,SERV_CRM_IN_200906
我要写一个函数,根据传入的的SERV_ID取得该ID所对应的ysje字段三个月的平均值,请问该如何解决?
每一个表的sum(ysje)然后在返回时三个表相加,然后再除以3
return number
is
v_out number := 0;
v_tmp number := 0;
begin
begin
select sum(ysje) into v_out from SERV_CRM_IN_200908 where SERV_ID = v_serv_id;
exception
when no_data_found then
v_out := 0;
end;
begin
select sum(ysje) into v_tmp from SERV_CRM_IN_200907 where SERV_ID = v_serv_id;
exception
when no_data_found then
v_tmp := 0;
end;
v_out := v_out + v_tmp;
v_tmp := 0;
begin
select sum(ysje) into v_tmp from SERV_CRM_IN_200906 where SERV_ID = v_serv_id;
exception
when no_data_found then
v_tmp := 0;
end;
v_out := (v_out + v_tmp) / 3;
return v_out;
exception
when others then
raise;
end fun;
create or replace function fun( v_serv_id in varchar2
, v_tbl_1 in varchar2
, v_tbl_2 in varchar2
, v_tbl_3 in varchar2)
return number
is
v_out number := 0;
v_tmp number := 0;
begin
begin
execute immediate 'select sum(ysje) from '||v_tbl_1||' where SERV_ID = '||v_serv_id
into v_out;
exception
when no_data_found then
v_out := 0;
end;
begin
execute immediate 'select sum(ysje) from '||v_tbl_2||' where SERV_ID = '||v_serv_id
into v_tmp;
exception
when no_data_found then
v_tmp := 0;
end;
v_out := v_out + v_tmp;
v_tmp := 0;
begin
execute immediate 'select sum(ysje) from '||v_tbl_3||' where SERV_ID = '||v_serv_id
into v_tmp;
exception
when no_data_found then
v_tmp := 0;
end;
v_out := (v_out + v_tmp) / 3;
return v_out;