Example of a function

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 = 'oppt' 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 = 'stt' 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;