1.


CREATE OR REPLACE PROCEDURE Augmentsalary
IS
salaire NUMBER;
jobs varchar2(30);
cursor raisecur IS (SELECT ename,job,sal FROM emp);
ligne raisecur % Rowtype;
BEGIN
OPEN raisecur;
loop
Fetch raisecur INTO ligne;
exit WHEN raisecur%NotFound;
jobs:=ligne.job;
salaire:=ligne.sal;
IF jobs='ANALYST' OR jobs='MANAGER' THEN
salaire:=salaire+salaire*10/100;
elsif jobs='SALESMAN' THEN
salaire:=salaire+salaire*15/100;
elsif jobs='CLERK' OR jobs='PRESIDENT' THEN
salaire:=salaire+salaire*20/100;
END IF;
DBMS_OUTPUT.put_line('l employe <<' || ligne.ename || '>> a le nouveau salaire <<' || salaire || '>> , son ancient salaire est <<' || ligne.sal || '>> , son metier est <<' || ligne.job || '>>');
END loop;
close raisecur;
END Augmentsalary;





2.

CREATE OR REPLACE FUNCTION getmgrname(numero IN emp.empno%TYPE) RETURN varchar2
IS
mgrmgr NUMBER;
mgrnom varchar2(30);
empnom varchar2(30);
BEGIN
SELECT e.mgr,(SELECT ename FROM emp WHERE empno=e.mgr),e.ename INTO mgrmgr,mgrnom,empnom FROM emp e WHERE empno=numero;
IF mgrmgr IS NULL THEN
mgrnom:='AUCUN';
END IF;
DBMS_OUTPUT.put_line('l employe <<' || empnom || '>> a le manager <<' || mgrnom || '>>');
RETURN (mgrnom);
END getmgrname;

3.

CREATE OR REPLACE 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;

4.


ceate OR REPLACE FUNCTION getsalarymax(daterec IN DATE) RETURN NUMBER
IS
salaire NUMBER;
BEGIN
SELECT MAX(sal) INTO salaire FROM emp WHERE hiredate > daterec;
DBMS_OUTPUT.PUT_LINE('le salaire maximal est : ' ||salaire);
RETURN salaire;
END getsalarymax;


5.

cursor employecur RETURN emp%rowtype IS SELECT * FROM emp;