GLSI2 Elaboré par
Ingénierie BD
Mme Boutaleb Manel
ISTIC Borj Cedria
TP2 : Procédures et fonctions
Exercice 1:
Créer une procédure PL/SQL qui calcule la moyenne des notes d’un ensemble d’étudiants
stockées dans une table nommée notes (id_etudiant, nom_etudiant, note).
Exercice 2 :
1. Soit la table suivante :
CREATE TABLE FOURNISSEUR (CODE_FOURNISSEUR VARCHAR2(10) PRIMARY KEY,
NOM_FOURNISSEUR VARCHAR2(100), ADRESSE_FOURNISSEUR VARCHAR2(200), TYPE_FOURNISSEUR
VARCHAR2(50));
- Créer une procédure PL/SQL qui permet d’ajouter un nouveau fournisseur.
- En utilisant cette procédure, insérer trois fournisseurs ayant comme code F09, F10
et F11
2. Soit la table suivante :
CREATE TABLE EMBALLAGE ( ID_EMBALLAGE NUMBER PRIMARY KEY, CODE_FOURNISSEUR
VARCHAR2(10) REFERENCES FOURNISSEUR (CODE_FOURNISSEUR), NOM_EMBALLAGE
VARCHAR2(100), QUANTITE NUMBER, DATE_FABRICATION DATE);
-- Insérer les lignes suivantes dans la table EMBALLAGE
INSERT INTO EMBALLAGE (ID_EMBALLAGE, CODE_FOURNISSEUR, NOM_EMBALLAGE,
QUANTITE, DATE_FABRICATION)
VALUES (1, 'F09', 'Emballage 1', 100, TO_DATE('2023-10-15', 'YYYY-MM-DD'));
INSERT INTO EMBALLAGE (ID_EMBALLAGE, CODE_FOURNISSEUR, NOM_EMBALLAGE,
QUANTITE, DATE_FABRICATION)
VALUES (2, 'F09', 'Emballage 2', 50, TO_DATE('2023-10-15', 'YYYY-MM-DD'));
INSERT INTO EMBALLAGE (ID_EMBALLAGE, CODE_FOURNISSEUR, NOM_EMBALLAGE,
QUANTITE, DATE_FABRICATION)
VALUES (3, 'F10', 'Emballage 3', 75, TO_DATE('2023-10-15', 'YYYY-MM-DD'));
1
- Créer une fonction PL/SQL qui permet de retourner le nombre total des emballages
effectués par un fournisseur donné en paramètre (son code).
3. Soit la table suivante :
CREATE TABLE TF_NE ( TYPE_FOURNISSEUR VARCHAR2(50), NOMBRE_EMBALLAGE NUMBER
);
- Créer une procédure PL/SQL qui permet de calculer le total des emballages
effectues par un type fournisseur donné en paramètre, et insérer à chaque fois le
résultat dans la table TF_NE
Exercice 2 : Gestion d'une bibliothèque
Considérons une bibliothèque qui souhaite gérer ses livres, ses emprunteurs et ses emprunts.
Nous allons créer des tables pour stocker ces informations et écrire des procédures PL/SQL
pour effectuer des opérations de gestion.
1. Création de tables : Créez trois tables pour stocker les informations suivantes :
a. Livres (id_livre, titre, auteur, année_publication)
b. Emprunteurs (id_emprunteur, nom, prénom, date_inscription)
c. Emprunts (id_emprunt, id_livre, id_emprunteur, date_emprunt, date_retour)
-- Création de la table "Livres"
CREATE TABLE Livres ( id_livre NUMBER PRIMARY KEY, titre VARCHAR2(100), auteur VARCHAR2(50),
annee_publication NUMBER );
-- Création de la table "Emprunteurs"
CREATE TABLE Emprunteurs ( id_emprunteur NUMBER PRIMARY KEY, nom VARCHAR2(50), prénom
VARCHAR2(50), date_inscription DATE );
-- Création de la table "Emprunts"
CREATE TABLE Emprunts ( id_emprunt NUMBER PRIMARY KEY, id_livre NUMBER REFERENCES
Livres(id_livre), id_emprunteur NUMBER REFERENCES Emprunteurs(id_emprunteur), date_emprunt DATE,
date_retour DATE );
2. Insertion de données
-- Ligne 1
INSERT INTO Livres (id_livre, titre, auteur, annee_publication)
VALUES (1, 'Livre 1', 'Auteur 1', 2000);
2
-- Ligne 2
INSERT INTO Livres (id_livre, titre, auteur, annee_publication)
VALUES (2, 'Livre 2', 'Auteur 2', 2010);
-- Ligne 3
INSERT INTO Livres (id_livre, titre, auteur, annee_publication)
VALUES (3, 'Livre 3', 'Auteur 3', 2020);
-- Ligne 4
INSERT INTO Livres (id_livre, titre, auteur, annee_publication)
VALUES (4, 'Livre 4', 'Auteur 4', 2015);
-- Ligne 1
INSERT INTO Emprunteurs (id_emprunteur, nom, prénom, date_inscription)
VALUES (1, 'Nom1', 'Prénom1', TO_DATE('2023-10-19', 'YYYY-MM-DD'));
-- Ligne 2
INSERT INTO Emprunteurs (id_emprunteur, nom, prénom, date_inscription)
VALUES (2, 'Nom2', 'Prénom2', TO_DATE('2023-10-20', 'YYYY-MM-DD'));
-- Ligne 3
INSERT INTO Emprunteurs (id_emprunteur, nom, prénom, date_inscription)
VALUES (3, 'Nom3', 'Prénom3', TO_DATE('2023-10-21', 'YYYY-MM-DD'));
-- Ligne 4
INSERT INTO Emprunteurs (id_emprunteur, nom, prénom, date_inscription)
VALUES (4, 'Nom4', 'Prénom4', TO_DATE('2023-10-22', 'YYYY-MM-DD'));
-- Ligne 1
INSERT INTO Emprunts (id_emprunt, id_livre, id_emprunteur, date_emprunt, date_retour)
VALUES (1, 1, 1, TO_DATE('2023-10-19', 'YYYY-MM-DD'), TO_DATE('2023-11-19', 'YYYY-MM-DD'));
-- Ligne 2
INSERT INTO Emprunts (id_emprunt, id_livre, id_emprunteur, date_emprunt, date_retour)
VALUES (2, 2, 2, TO_DATE('2023-10-20', 'YYYY-MM-DD'), TO_DATE('2023-11-20', 'YYYY-MM-DD'));
-- Ligne 3
INSERT INTO Emprunts (id_emprunt, id_livre, id_emprunteur, date_emprunt, date_retour)
VALUES (3, 3, 3, TO_DATE('2023-10-21', 'YYYY-MM-DD'), TO_DATE('2023-11-21', 'YYYY-MM-DD'));
-- Ligne 4
INSERT INTO Emprunts (id_emprunt, id_livre, id_emprunteur, date_emprunt, date_retour)
VALUES (4, 4, 4, TO_DATE('2023-10-22', 'YYYY-MM-DD'), TO_DATE('2023-11-22', 'YYYY-MM-DD'));
3
3. Créez une procédure PL/SQL qui prend en entrée le nom d'un emprunteur et affiche
son nom complet (nom et prénom). Utilisez une variable pour stocker le nom complet.
4. Écrivez une procédure PL/SQL qui prend en entrée le titre d'un livre et la nouvelle
année de publication, puis met à jour l'année de publication du livre dans la table des
livres.
5. Écrivez une procédure PL/SQL qui prend en entrée le nom d'un emprunteur et
supprime tous les enregistrements d'emprunts associés à cet emprunteur.
