一个函数的例子

CREATE OR REPLACE FUNCTION "FETCHNAME"         (v_cdno varchar2,
v_type varchar2,v_pmodid varchar2)
return varchar2
is
v_name varchar2(50);
begin

v_name:='';
if v_type = 'application' then
  select ap_appname into v_name from application where ap_appid=v_cdno;
  return v_name;
elsif v_type = 'module' then
  if v_cdno='0' then
   select mo_modname into v_name from module where mo_modid=v_pmodid;
  else
   select mo_modname into v_name from module where mo_modid=v_cdno;
  end if;
  return v_name;
elsif v_type = 'metafunction' then
  select me_metaname into v_name from metafunction where me_metaid=v_cdno;
  return v_name;
elsif v_type = 'company' then
  select cmpname into v_name from company where cmpcmpcd=v_cdno;
  return v_name;
  elsif v_type = 'TRD' then
  select trdname into v_name from trade where trdcd=v_cdno;
  return v_name;
elsif v_type='oprdept' then
  select de_deptName into v_name from department where de_deptid=v_cdno;
  return v_name;
elsif v_type ='EDU' then
  select cstnm into v_name from cnst where csttp ='EDU' and cstcd = v_cdno;
  return v_name;
elsif v_type ='SEX' then
  select cstnm into v_name from cnst where csttp ='SEX' and cstcd = v_cdno;
  return v_name;
elsif v_type ='STP' then
  select cstnm into v_name from cnst where csttp ='STP' and cstcd = v_cdno;
  return v_name;
elsif v_type ='SJB' then
  select cstnm into v_name from cnst where csttp ='SJB' and cstcd = v_cdno;
  return v_name;
elsif v_type ='STA' then
  select cstnm into v_name from cnst where csttp ='STA' and cstcd = v_cdno;
  return v_name;
elsif v_type ='EMP' then
  select cstnm into v_name from cnst where csttp ='EMP' and cstcd = v_cdno;
  return v_name;
elsif v_type = 'CMP' then
  select cstnm into v_name from cnst where  csttp='CMP'and cstcd=v_cdno;
  return v_name;
elsif v_type = 'rgn' then
  select cstnm into v_name from cnst where  csttp='RGN'and cstcd=v_cdno;
  return v_name;
elsif v_type = 'CET' then
  select cstnm into v_name from cnst where  csttp='CET'and cstcd=v_cdno;
  return v_name;
elsif v_type='DPT' then
  select de_deptName into v_name from department where de_deptid=v_cdno;
  return v_name;
elsif v_type = 'RGN' then
  select rgnname into v_name from region where rgncd=v_cdno;
  return v_name;
elsif v_type = 'HRR' then
  select cstnm into v_name from cnst where  csttp='RGN'and cstcd=v_cdno;
  return v_name;
elsif v_type = 'CMPNAME' then
  select cmpname into v_name from company where cmpcmpcd=v_cdno;
  return v_name;
elsif v_type = 'PSTNAME' then
  select pstname into v_name from post where pstcd=v_cdno;
  return v_name;
elsif v_type = 'OPR' then
  select us_username into v_name from userinfo where us_userid=v_cdno;
  return v_name;
elsif v_type = 'AGE' then
      if v_cdno='11' then
         v_name:='<=15';
      elsif v_cdno='12' then
         v_name:='15-20';
      elsif v_cdno='21' then
         v_name:='20-25';
      elsif v_cdno='22' then
         v_name:='25-30';
      elsif v_cdno='31' then
         v_name:='30-35';
      elsif v_cdno='32' then
         v_name:='35-40';
      elsif v_cdno='41' then
         v_name:='40-45';
      elsif v_cdno='42' then
         v_name:='45-50';
      elsif v_cdno='51' then
         v_name:='50-55';
      elsif v_cdno='62' then
         v_name:='55-60';
      elsif v_cdno='61' then
         v_name:='60-65';
      elsif v_cdno='62' then
         v_name:='>=65';
      else
         v_name:=' >=65   ';
      return v_name;
      end if;
else
    return '';
end if;
end fetchname;