Function example

Simple Function
================

create or replace function f1(par1 number,par2 number)
return number is
v_res number;
begin
v_res := par1 + par2;
return(v_res);
end;
/

select f1(10,20) from dual;


=======================================
Simple example with employees datatype
=======================================

create or replace function f1(p_eno employees.employee_id%type)
return employees.salary%type
is
v_ann_sal employees.salary%type;
begin
select (salary*12) into v_ann_sal from employees where employee_id=p_eno;
return(v_ann_sal);
end;
/


Simple example of function with DML
===================================

create or replace function func1(p_eno employees.employee_id%type)
return boolean
is
begin
update test1 set salary=salary+100 where employee_id=100;
commit;
return true;
end;
/

SQL> select func1(100) from employees;
select func1(100) from employees
       *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

SQL> declare
    v_ret boolean;
    begin
    v_ret := func1(100);
    if v_ret then
    dbms_output.put_line('Salary updated');
    else
    dbms_output.put_line('Salary not updated');
    end if;
   end;
   /
Salary updated

PL/SQL procedure successfully completed.

SQL> select salary from test1 where employee_id=100;

    SALARY
----------
     24100