CREATE or REPLACE package package_Majana
   PROCEDURE Salary_Augment ;
   FUNCTION getMgrName(numero IN emp.empno%TYPE) RETURN varchar2 ;
   FUNCTION getsalarymax(code IN emp.empno%TYPE) RETURN NUMBER;
   FUNCTION getsalarymax(f_date IN DATE) RETURN NUMBER;
   CURSOR employee return emp%rowtype is select * from emp;

END package_Majana;
/

CREATE OR REPLACE PACKAGE BODY package_Majana As
PROCEDURE Salary_Augment
IS
grad NUMBER;
salaire NUMBER;
nom varchar2(30);
cursor c IS (SELECT e.ename,s.grade,e.sal FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal);
ligne c % Rowtype;
BEGIN
OPEN c;
loop
Fetch c INTO ligne;
exit WHEN c%NotFound;
grad:=ligne.grade;
salaire:=ligne.sal;
nom:=ligne.ename;
IF grad=1 OR grad=2 THEN
salaire:=salaire+salaire*10/100;
elsif grad=3 THEN
salaire:=salaire+salaire*15/100;
elsif grad=4 OR grad=5 THEN
salaire:=salaire+salaire*20/100;
END IF;
DBMS_OUTPUT.put_line('l employe <<' || nom || '>> a le nouveau salaire <<' || salaire || '>> , son ancient salaire est <<' || ligne.sal || '>> , son grade est <<' || grade || '>>');
END loop;
close c;
END Salary_Augment;

FUNCTION getMgrName(numero IN emp.empno%TYPE) RETURN varchar2
IS

f_mgr NUMBER;
f_mgrNom varchar2(30);
f_empNom varchar2(30);
BEGIN
SELECT mgr,ename,(select ename from emp where empno=mgr) INTO f_mgr,f_empNom,f_mgrNom FROM emp WHERE empno=numero;
IF f_mgr IS NULL THEN
f_mgrNom:='AUCUN';
ELSE
select ename into f_mgrNom from emp where empno=f_mgr ;
END IF;
DBMS_OUTPUT.put_line('l employe <<' || f_empNom || '>> a le manager <<' || f_mgrNom || '>>');
RETURN (f_mgrNom);
END getMgrName;



FUNCTION getsalarymax(code IN emp.empno%TYPE) RETURN NUMBER
IS
salaire NUMBER;
BEGIN
 SELECT MAX(e.sal) INTO salaire FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal
 AND s.hisal AND s.grade IN (SELECT s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal
 AND s.hisal AND e.empno=code) GROUP BY grade;
DBMS_OUTPUT.PUT_LINE('le salaire maximal d un employe du meme grade que '|| code || ' est : ' ||salaire);
RETURN salaire;
 END getsalarymax;



FUNCTION getsalarymax(f_date IN DATE) RETURN NUMBER
IS
salaire NUMBER;
BEGIN
select max(sal) into salaire from emp where hiredate>f_date ;
DBMS_OUTPUT.PUT_LINE('le salaire maximal est :' ||salaire);
RETURN salaire;
 END getsalarymax;


end package_Majana;
/