EXAMPLE for COMBINATION OF PACKAGE WITH FUNCTION AND PROCEDURE

Example :

Create or Replace Package EMPPACK
IS
Function comm_cal(p_empno IN number)
Return number;
Procedure comm_upd(p_empno IN number);
End;

Create or Replace Package Body EMPPACK
IS
Function comm_cal(p_empno IN number)
Return number
IS
m_sal emp.sal%type;
m_comm emp.comm%type;
Begin
select sal,comm into m_sal,m_comm from emp where empno=p_empno;
If sal<2000 then m_comm:=m_sal*10/100;
else
m_comm:=m_sal*15/100;
end if;
Return m_comm;
Exception
When No_Data_Found then
dbms_output.put_line('sorry wrong empno entered try another one');
End comm_cal;

Procedure comm_upd(p_empno IN number)
IS
m_sal emp.sal%type;
m_comm emp.comm%type;
Begin
select sal,comm into m_sal,m_comm from emp where empno=p_empno;
If m_sal<2000 then m_comm:=m_sal*10/100;
else
m_comm:=m_sal*15/100;
End if;
Update emp set comm:=m_comm where empno=p_empno;
Exception
when No_Data_found then
dbms_output.put_line('sorry wrong empno entered try another one);
End comm_upd;
End