Correction TD 5 sur le PL-SQL
Amina Aboulmira
Curseurs
I. Exercice 1 :
Créer un bloc PL/SQL qui détermine les employés de plus haut salaire.
a) Créer pour cet exercice une nouvelle table pour stocker les employés et leurs salaires
SQL> CREATE TABLE meilleurs
2 (nom VARCHAR2(25),
3 salaire NUMBER(11,2));
b) Utiliser un paramètre pour prendre une valeur n en entrée pour identifier les n
meilleurs.
Ecrire une boucle FOR avec curseur pour récupérer le nom et salaire des n meilleurs
employés selon leur salaire dans la table EMPLOYES.
Enregistrer les noms et salaires dans la table MEILLEURS. On suppose qu’aucun employé
n’a le même salaire qu’un autre.
c) Tester le bloc avec différents cas tels que n=0 ou n supérieur au nombre total
d’employés (25). Vider la table MEILLEURS après chaque test.
ACCEPT p_n PROMPT 'Entrer une valeur numérique : '
DECLARE
CURSOR emp_cursor IS
SELECT nom, salaire
FROM employes
WHERE salaire IS NOT NULL
ORDER BY salaire DESC;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FOR i IN 1..&p_n
LOOP
FETCH emp_cursor INTO emp_record;
INSERT INTO meilleurs(nom, salaire)
VALUES (emp_record.nom,emp_record.salaire);
END LOOP;
CLOSE emp_cursor;
COMMIT;
END;
/
SELECT nom,TO_CHAR(salaire,'fm$9,999,999') salaire FROM meilleurs;
TRUNCATE TABLE meilleurs
II. Exercice 8 :
L’entreprise employant les personnes de la table EMPLOYEE est délocalisée des États-Unis
en France. Il est donc nécessaire de convertir leur salaire et leur commission en francs
(pour simplifier, on admettra 1 USD = 6,5 FF). Tous les employés voient également
augmenter leur salaire de 25 % après conversion.
a) Créer une nouvelle table vide EMP_FR de même structure que EMPLOYEE. On pourra, par
soucis de rapidité, recopier la table EMPLOYEE dans EMP_FR pour en obtenir la structure,
puis effacer tous les tuples de EMP_FR.
create table emp_fr as select * from emp;
delete from emp_fr;
b) Écrire un programme PL/SQL permettant de recopier tous les tuples de la table EMP dans la
table EMP_FR en effectuant au passage les opérations nécessaires sur le salaire et la
commission. Traiter le cas où la table EMP est vide comme une exception.
DECLARE
n NUMBER(2);
CURSOR employes IS
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp;
employe employes%ROWTYPE;
newsal emp.sal%TYPE;
newcomm emp.comm%TYPE;
empvide EXCEPTION;
BEGIN
-- Test table vide
SELECT COUNT(*) INTO n FROM emp;
IF n=0 THEN
RAISE empvide;
END IF;
-- Remplissage emp_fr
FOR employe IN employes LOOP
-- Calculs
newsal:=employe.sal*6;
newsal:=newsal*1.25;
IF employe.comm IS NOT NULL THEN
newcomm:=employe.comm*6;
ELSE
newcomm:=NULL;
END IF;
-- Insertion donnees
INSERT INTO emp_fr VALUES(employe.empno, employe.ename, employe.job, employe.mgr,
employe.hiredate, newsal, newcomm, employe.deptno);
END LOOP;
EXCEPTION
WHEN empvide THEN RAISE_APPLICATION_ERROR(-20501,'Pas d employe !');
END;
III. Exercice 9 :
Soit le schéma relationnel d’une agence bancaire régionale.
CLIENT (NUMCL, NOM, PRENOM, ADR, CP, VILLE, SALAIRE, CONJOINT) DETENTEUR (NUMCL,
NUMCP)
COMPTE (NUMCP, DATEOUVR, SOLDE)
Attributs soulignés : Clés primaires. Attributs en italiques: Clés étrangères.
NUMCL et CONJOINT sont définis sur le même domaine.
Écrire un trigger en insertion permettant de contrôler les contraintes suivantes :
- le département dans lequel habite le client doit être 01, 07, 26, 38, 42, 69, 73, ou 74 ;
- le nom du conjoint doit être le même que celui du client.
CREATE TRIGGER INS_CLIENT
BEFORE INSERT ON CLIENT
FOR EACH ROW
DECLARE
nom_conjoint CLIENT.NOM%TYPE ;
compteur CLIENT.NUMCL%TYPE ;
pb_dept EXCEPTION ;
pb_conjoint1 EXCEPTION ;
pb_conjoint2 EXCEPTION ;
BEGIN
-- Contrainte sur le département
IF TRUNC(:NEW.CP/1000) NOT IN (01, 07, 26, 38, 42, 69, 73, 74) THEN
RAISE pb_dept ;
END IF ;
-- Contrainte sur le nom du conjoint (+ test d’existence du conjoint)
IF NEW.CONJOINT IS NOT NULL THEN
SELECT COUNT(*), NOM
INTO compteur, nom_conjoint
FROM CLIENT
WHERE NUMCL = :NEW.CONJOINT
GROUP BY NOM ;
IF compteur = 0 THEN -- Pas de conjoint
RAISE pb_conjoint1 ;
END IF ;
IF nom_conjoint <> :NEW.NOM THEN
RAISE pb_conjoint2 ;
END IF ;
END IF ;
EXCEPTION
WHEN pb_dept THEN RAISE_APPLICATION_ERROR (-20501,
‘Insertion impossible : le client n’habite pas en région Rhône-Alpes !’) ;
WHEN pb_conjoint1 THEN RAISE_APPLICATION_ERROR (-20502,
‘Insertion impossible : le conjoint du client n’existe pas !’) ;
WHEN pb_conjoint2 THEN RAISE_APPLICATION_ERROR (-20503,
‘Insertion impossible : le nom du conjoint est différent de celui du client !’) ;
END ;
IV. Exercice 10 :
1. Soit une table quelconque TABL, dont la clé primaire CLENUM est numérique.
Définir un trigger en insertion permettant d’implémenter une numérotation automatique de la clé.
Le premier numéro doit être 1.
create or replace trigger cleauto
before insert on tabl
for each row
declare
begin
n integer;
newkey integer;
preums exception;
-- Recherche s'il existe des tuples dans la table
select count(*) into n from tabl;
if n=0 then
raise preums; -- Premiere insertion
end if;
-- Recherche la valeur de cle C la plus elevee
-- et affecte C+1 a la nouvelle cle
select max(clenum) into newkey from tabl;
:new.clenum := newkey + 1;
exception
-- Premier numero = 1
when preums then :new.clenum := 1;
end;
2. Écrivez une procédure PLSQL qui prends en paramètre un NUMBER (age limite) et qui affiche
pour chaque département le nombre des employés qui dépassent l'age limite. Utilisez un curseur
avec paramètre l'age limite.
CREATE OR REPLACE PROCEDURE moyenneAge(AgeLim IN NUMBER)
IS
CURSOR CS(Age_Limite NUMBER) IS
SELECT DEPARTEMENT AS DNOM, COUNT(*) AS NB
FROM EMPLOYE
WHERE AGE > Age_Limite
GROUP BY DEPARTEMENT ;
BEGIN
FOR DEPT IN CS(AgeLim) LOOP
DBMS__OUTPUT.PUT_LINE(DEPT.DNOM || ' ' || DEPT.NB)
END FOR
END
