oracle 函数应用实践一

create or replace function f_get_fjkywtce(fcd in varchar, flag in number)
--非集客业务提成额（不参与考核分配）
return number
is
       prekpi number;

       p1 number;
       p2 number;
       p3 number;

begin
   if flag = 0  --住宅电话揽装

      then
      select sum(oc.commision) into p1  from o_canvass oc
                                           where oc.fixednet_cd = fcd group by oc.fixednet_cd;

      return p1;

   end if;

   if flag = 1 --住宅宽带新装提成

        then
        select sum(h.commision) into p2 from o_new_home_broad h
                                 where h.fixednet_cd = fcd group by h.fixednet_cd;

         return p2;

   end if;

   if flag = 2 --住宅宽带续费提成

      then
      select sum(rh.commision) into p3 from o_renew_home_broad rh
                                 where rh.fixednet_cd = fcd group by rh.fixednet_cd;

       return p3;

   end if;

   if flag = 3 --非集客业务提成小计

      then
      select sum(oc.commision) into p1  from o_canvass oc
                                 where oc.fixednet_cd = fcd group by oc.fixednet_cd;

      select sum(h.commision) into p2 from o_new_home_broad h
                                 where h.fixednet_cd = fcd group by h.fixednet_cd;


      select sum(rh.commision) into p3 from o_renew_home_broad rh
                                 where rh.fixednet_cd = fcd group by rh.fixednet_cd;

      prekpi := p1+p2+p3;

      return prekpi;

   end if;

end f_get_fjkywtce;