Oracle存储过程function语法及案例

最新推荐文章于 2024-08-29 21:19:57 发布

转载最新推荐文章于 2024-08-29 21:19:57 发布 · 118 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/liudi1992/p/3866429.html

文章标签：

#数据库

本文介绍了一个PL/SQL函数的创建及使用案例，该函数用于从数据库表sx03_gl_accasst中根据指定条件计算me或mb字段的总和，并返回计算结果。函数通过参数para确定计算哪个字段的总和。

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;