code plsql

create or replace PROCEDURE test (p_deptno dept.deptno%type, p_augmentation NUMBER) AS
l_moy_avant emp.sal%type := 0;
l_moy_apres emp.sal%type := 0;
l_deptno NUMBER;

BEGIN
    BEGIN
        SELECT deptno into l_deptno
        from dept
        where deptno=p_deptno;
    EXCEPTION
        WHEN no_data_found THEN
             dbms_output.put_line('le departement' || p_deptno || 'nexiste pas');
            
    END;
--test logique fonctionnel 2pt
    IF (p_augmentation IS NULL) OR (p_deptno IS NULL) THEN
        dbms_output.put_line('aucun parametre ne peut etre NULL');
    ELSIF(p_augmentation > 3) THEN
 -- affichage des message 1pt
            dbms_output.put_line('laugmentation ne peut etre superieur a 3%');
        ELSE
            SELECT AVG(sal) into l_moy_avant
            FROM emp
            WHERE deptno=p_deptno;
--update avec avg 2pt
        UPDATE emp SET sal = sal+(1+(coalesce(p_augmentation, 0)/100))
        WHERE 
            deptno=p_deptno;
        SELECT AVG(sal) into l_moy_apres
        FROM emp
        WHERE deptno=p_deptno;
        dbms_output.put_line('Moyenne salaire avant : '  || round(l_moy_avant, 2));
        dbms_output.put_line('Moyenne salaire apres : '  || round(l_moy_apres, 2));
        COMMIT;
    END IF;
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('le department ' || p_deptno|| 'nexiste pas');
     
        WHEN OTHERS THEN
            dbms_output.put_line('Une erreur s''est produite');
            ROLLBACK;
            RAISE;
-- when other + raise 1pt
END;