042 - [Oracle PL/SQL] Functions - user-defined functions

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

 

 

 

 

 

 

 

# create a function to calc tax on salary
# if salary <5000 then tax 10%  else 15%

create or replace function get_sal_tax
    (p_sal number)
    return number
is

begin

    if p_sal<5000 then
        return p_sal* (10/100);
    else
        return p_sal* (15/100);
    end if;

end;

 

 

 

select employee_id, first_name, salary, get_sal_tax(salary) as tax
from employees order by employee_id;
------------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME               SALARY        TAX
----------- -------------------- ---------- ----------
        100 Steven                    24100       3615
        101 Neena                     17000       2550
        102 Lex                       17000       2550
        103 Alexander                  9000       1350
        104 Bruce                      6000        900
        105 David                      4800        480
...
107 rows selected.

 

 

 

select employee_id, first_name, salary, get_sal_tax(p_sal=>salary) as tax
from employees order by employee_id;
------------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME               SALARY        TAX
----------- -------------------- ---------- ----------
        100 Steven                    24100       3615
        101 Neena                     17000       2550
        102 Lex                       17000       2550
        103 Alexander                  9000       1350
        104 Bruce                      6000        900
        105 David                      4800        480
...

 

 

 

select employee_id, first_name, salary, get_sal_tax(salary) as tax
from employees
where get_sal_tax(salary)>2000
order by get_sal_tax(salary);
------------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME               SALARY        TAX
----------- -------------------- ---------- ----------
        146 Karen                     13500       2025
        145 John                      14000       2100
        101 Neena                     17000       2550
        102 Lex                       17000       2550
        100 Steven                    24100       3615