Oracle預存程序function文法及案例

標籤：style   blog   color   io   re   c   cti   div   

create or replace function F01_SX03_SUM(statdate varchar2,                                        code     varchar2,                                        para     varchar2) RETURN number IS  v_me number;  v_mb number;begin  if para = ‘me‘ then      select nvl(sum(nvl(me, 0)), 0) / 10000      into v_me      from sx03_gl_accass t     where t.ccode like (code || ‘%‘)       and t.iyeriod = substr(replace(statdate, ‘-‘, ‘‘), 1, 6)       and t.citem_id in           (select distinct (ts.citem_id)              from sx03_gl_accass ts             where ts.ccode = ‘4001‘               and ts.me <> 0               and ts.iyeriod = substr(replace(statdate, ‘-‘, ‘‘), 1, 6));    return v_me;    elsif para = ‘mb‘ then    select nvl(sum(nvl(mb, 0)), 0) / 10000      into v_mb      from sx03_gl_accass t     where t.ccode like (code || ‘%‘)       and t.iyeriod = substr(statdate, 1, 4) || ‘01‘       and t.citem_id in           (select distinct (ts.citem_id)              from sx03_gl_accass ts             where ts.ccode = ‘4001‘               and ts.mb <> 0               and ts.iyeriod = substr(statdate, 1, 4) || ‘01‘);    return v_mb;  end if;end;