code plsql
unknown
plain_text
3 years ago
1.6 kB
9
Indexable
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;
Editor is loading...