TP1 : PL/SQL
Bloc PL/SQL, Structures de contrôles et Curseurs
Première année
Soit le schéma relationnel suivant :
E_TITRE (#TITRE VARCHAR2(25));
E_SERVICE(#NO NUMBER(7), NOM VARCHAR2(50));
E_EMPLOYE(#NO NUMBER(7), NOM VARCHAR2(50), PRENOM VARCHAR2(25), DT_ENTREE
DATE, TITRE VARCHAR2(25), SERVICE_NO NUMBER(7),
COMMENTAIRE VARCHAR2(200), SALAIRE NUMBER(11,2), PCT_COMMISSION NUMBER(4,2));
E_CLIENT(#NO NUMBER(7), NOM VARCHAR2(50), PRENOM VARCHAR2(25), TELEPHONE
VARCHAR2(25), ADRESSE VARCHAR2(200), VILLE VARCHAR2(30),
PAYS VARCHAR2(30), CP_POSTAL VARCHAR2(5), COMMENTAIRE VARCHAR2(200));
E_PRODUIT(#NO NUMBER(7), NOM VARCHAR2(50), DESCRIPTION VARCHAR2(200),
PRIX_CONSEILLE NUMBER(11,2));
E_ENTREPOT(#NO NUMBER(7), ADRESSE VARCHAR2(200), VILLE VARCHAR2(30), PAYS
VARCHAR2(30), CP_POSTAL VARCHAR2(5), TELEPHONE VARCHAR2(25));
E_STOCK(#NO NUMBER(7), PRODUIT_NO NUMBER(7), ENTREPOT_NO NUMBER(7), QTE_STOCK
NUMBER(9), STOCK_SECURITE NUMBER(9), MAX_STOCK NUMBER(9),
DT_STOCK DATE);
E_COMMANDE(#NO NUMBER(7), CLIENT_NO NUMBER(7), DATE_COMMANDE DATE,
DATE_LIVRAISON DATE, EMPLOYE_NO NUMBER(7), TOTAL NUMBER(11,2));
E_LIGNE(#NO NUMBER(7), COMMANDE_NO NUMBER(7), PRODUIT_NO NUMBER(7), PRIX
NUMBER(11,2), QUANTITE NUMBER(9));
Sous SQL*Plus, exécuter la commande start creationBase.sql pour créer les tables de ce
schéma relationnel.
Exercice 1 (IF...THEN...ELSIF...ELSE…)
Créer une table E_Augmentation comprenant les champs suivants :
No Number(7), Augmentation Number(11,2), Date_Augmentation Date, Emp_No Number(7)
1- Ecrire un programme permettant :
- de saisir en entrée le nom d’un employé
- de mettre à jour le salaire de cet employé selon les conditions suivantes :
✓ si son année d’entrée dans la société est 1995, augmenter le salaire de 50%.
✓ si son année d’entrée dans la société est 1996, augmenter le salaire de 25%.
✓ si son année d’entrée dans la société est 1997, augmenter le salaire de 10%.
1/2
2- Modifier ce programme afin d’insérer dans la table E_Augmentation le montant
d’augmentation, la date d’augmentation, le numéro de l’employé, ainsi que le champ No qui
sert d’identifiant de ligne pour la table.
Exercice 2 (Curseur, Instructions OPEN, FETCH, CLOSE, %FOUND)
Ecrire un programme permettant :
- la mise à jour du salaire de tous les employés de la table E_Employe suivant les
conditions de l’exercice 1.
- l’insertion des modifications dans la table E_Augmentation.
Exercice 3 (Utilisation d’un curseur paramétré)
Créer une table E_Resultat :
E_Resultat (No Number(2), LB_Resultat Varchar2(60), VL_Resultat Number(11,2))
Ecrire un programme qui insère dans la table E_Resultat le nom de l’employé et son salaire
pour les employés dont le salaire vérifie les conditions suivantes :
- Si le salaire >3500, insérer dans la table E_Resultat les données suivantes :
✓ No --> <<No-programme>>
✓ LB_Resultat --> <<Variable_Nom_Employé>> a un salaire > 3500
✓ VL_Resultat --> <<Variable_Salaire_Employé>>
- Si le salaire >4500, insérer dans la table E_Resultat les données suivantes :
✓ No --> <<No-programme>>
✓ LB_Resultat --> <<Variable_Nom_Employé>> a un salaire > 4500
✓ VL_Resultat --> <<Variable_Salaire_Employé>>
Exercice 4 (Utilisation d’un curseur avec la boucle FOR…LOOP)
Ecrire un programme permettant de faire le total des commandes gérées par chaque employé.
Pour les employés qui ont géré des commandes, faire les insertions dans la table E_Resultat
selon le schéma suivant :
✓ No --> <<No-programme>>
✓ LB_Resultat --> <<Nom_Employé>> totalise
✓ VL_Resultat --> <<Total des commandes gérées>>
Exercice 5 (Curseur et clause CURRENT OF)
Ecrire un programme permettant :
- d’abaisser de 30% le prix conseillé des produits qui ne figurent sur aucune des
commandes.
- Insérer dans la table E_Resultat les produits concernés par la réduction :
✓ No --> <<No-programme>>
✓ LB_Resultat --> <<Variable_Numéro_Produit>> - <<Nom_Produit>> baisse
de 30%
✓ VL_Resultat --> <<Variable_Prix_Conseillé_avant_de_30%>>
2/2
