Poster un commentaire ou une réponse
TP2 Exception
Zaineb :
nb posts:320
nb discussions:33
inscrit le :23-01-2015
Zaineb :
nb posts:320
nb discussions:33
inscrit le :23-01-2015
Correction:
--Exercice 1
create or replace procedure insertion_dept(v_dpt departments.department_id%type,
nom varchar2,manager number,location number)
IS
begin
Insert into departments values(v_dpt,nom,manager,location);
EXCeption
when dup_val_on_index
then
dbms_output.put_line('Clé dupliquée!');
rollback;
end;
begin
insertion_dept(80,'RH',100,1700);
end;
--> Clé dupliquée!
Exercice 2
DELETE FROM MESSAGES;
DECLARE
enameemployees.last_name%TYPE;
emp_sal employees.salary%TYPE := 6000;
BEGIN
SELECTlast_name
INTO ename
FROMemployees
WHEREsalary = emp_sal;
INSERT INTO messages (results)
VALUES (ename || ' - ' || emp_sal);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO messages (results)
VALUES ('No employee with a salary of '|| TO_CHAR(emp_sal));
WHEN too_many_rows THEN
INSERT INTO messages (results)
VALUES ('More than one employee with a salary of '||
TO_CHAR(emp_sal));
WHEN others THEN
INSERT INTO messages (results)
VALUES ('Some other error occurred.');
END;
/
SELECT * FROM messages;
!! Ne pas oubliez : le gestionnaire when others est facultatif et doit être mis en dernier.
--Exercice 3
DECLARE
v_eval_prime employees.commission_pct%type;
v_emp employees%rowtype;
CURSOR c_emp IS
SELECT last_name, job_id, commission_pct, salary
FROM employees
WHERE department_id = 30;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_emp.last_name, v_emp.job_id, v_emp.commission_pct, v_emp.salary;
EXIT WHEN c_emp%NOTFOUND;
v_eval_prime := v_emp.commission_pct + (v_emp.salary/v_emp.commission_pct);
dbms_output.put_line('Name = '||v_emp.last_name || ' Job = ' || v_emp.job_id ||
' Re-evaluation Prime = ' ||v_eval_prime);
END LOOP;
CLOSE c_emp;
EXCEPTION
WHEN ZERO_DIVIDE THEN
dbms_output.put_line(SQLERRM(SQLCODE)||' SALARIE SANS PRIME !!');
WHEN OTHERS THEN
dbms_output.put_line('Autres Erreurs');
END;
/
le SQlERRM et SQLCode sont des fonctions génériques qui permettent d'afficher le message et le code
de l'erreur Oracle.
Exercice 4
CREATE OR REPLACE FUNCTION factorielle (N IN NUMBER)
RETURN NUMBER IS
ex_negatif_number EXCEPTION;
BEGIN
IF (N=1 or n=1) THEN
RETURN 1;
ELSE
if n<0 then raise ex_negatif_number ;
else
RETURN N *factorielle(N-1);
END IF;
end if;
EXCEPTION
when ex_negatif_number then
raise_application_error(-20897,'entrez un nb positif!');
END;
// appel
declare
n number;
begin
n:= factorielle(-10);
dbms_output.put_line(n);
end;
--> le raise_application error permet de créer une exception personnalisée avec un message et un code erreur
Exercice 5
CREATE TABLE "SALGRADE"
("MINSAL" NUMBER,
"MAXSAL" NUMBER,
"JOBCLASS" VARCHAR2(30),
CONSTRAINT "SALGRADE_CON" FOREIGN KEY ("JOBCLASS")
REFERENCES "JOBS" ("JOB_ID") ON DELETE CASCADE ENABLE
)
CREATE or replace TRIGGER sal_check
BEFORE INSERT OR UPDATE OF salary, job_id ON employees
FOR EACH ROW
DECLARE
minsal NUMBER;
maxsal NUMBER;
sal_hors_rang EXCEPTION;
BEGIN
SELECT minsal,maxsal INTO minsal, maxsal FROM salgrade
WHERE jobclass= :new.job_id;
IF (:new.salary < minsal OR :new.salary > maxsal) THEN RAISE sal_hors_rang;
END IF;
EXCEPTION
WHEN sal_hors_rang THEN
raise_application_error(-20300, 'Salaire' || TO_CHAR
(:new.salary) || ' hors rang');
WHEN NO_DATA_FOUND THEN
raise_application_error(-20322, 'Invalide Job');
END;
Exercice 6
Create or replace trigger limite_salaire_moy
After insert or update of Salary on EMPLOYEES
Declare
moy number;
Depas_limite_sal_moy Exception;
BEGIN
select avg(Salary) into moy
from EMPLOYEES;
If moy > 4000 then raise Depas_limite_sal_moy;
End if;
Exception
When Depas_limite_sal_moy Then
Raise_application_error(-20300, 'La moyenne des salaires, '|| TO_CHAR
(moy) || ', dépasse la limite');
END;
