devoir

exercice 1

CREATE OR REPLACE PROCEDURE raisesalary
IS
grade NUMBER;
salaire NUMBER;
nom varchar2(30);
cursor raisecur IS (SELECT e.ename,s.grade,e.sal FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal);
ligne raisecur % Rowtype;
BEGIN
OPEN raisecur;
loop
Fetch raisecur INTO ligne;
exit WHEN raisecur%NotFound;
grade:=ligne.grade;
salaire:=ligne.sal;
nom:=ligne.ename;
IF grade=1 OR grade=2 THEN
salaire:=salaire+salaire*10/100;
elsif grade=3 THEN
salaire:=salaire+salaire*15/100;
elsif grade=4 OR grade=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 raisecur;
END raisesalary;

/

exercice 2

CREATE OR REPLACE FUNCTION getmgrname(numero IN emp.empno%TYPE) RETURN varchar2
IS

mgrmgr NUMBER;
mgrnom varchar2(30);
BEGIN
SELECT mgr INTO mgrmgr FROM emp WHERE empno=numero;
IF mgrmgr IS NULL THEN
mgrnom:='AUCUN';
ELSE
mgrnom:=getempname(mgrmgr);
END IF;
DBMS_OUTPUT.put_line('l employe <<' || getempname(numero) || '>> a le manager <<' || mgrnom || '>>');
RETURN (mgrnom);
END getmgrname;
/

exercice 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;
/