GLSI2 Elaboré par
Ingénierie BD
Mme Boutaleb Manel
ISTIC Borj Cedria
TP3 : Les curseurs
Définition :
En PL/SQL, un curseur est utilisé pour itérer sur un ensemble de lignes retournées par une
requête SELECT. Il existe deux types de curseurs en PL/SQL : curseurs implicites (gérés
automatiquement par Oracle) et curseurs explicites (déclarés par l'utilisateur pour un contrôle
plus fin).
Voici la syntaxe générale pour un curseur explicite en PL/SQL :
Syntaxe :
DECLARE
CURSOR <nom_du_curseur> IS
SELECT ... FROM ... ;
BEGIN
OPEN <nom_du_curseur> ;
Loop
Fetch <nom_du_curseur> into …
...
EXIT WHEN curseur_EMPLOYE%NOTFOUND;
End loop;
END;
Exemple complet avec l'utilisation d'un curseur explicite
DECLARE
-- Déclaration d'un curseur pour récupérer les employés avec un salaire supérieur à 5000
CURSOR emp_cursor IS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 5000;
-- Déclaration des variables pour stocker les valeurs récupérées par le curseur
1
v_emp_id employees.emp_id%TYPE;
v_emp_name employees.emp_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
-- Ouverture du curseur
OPEN emp_cursor;
-- Boucle pour parcourir les lignes du curseur
LOOP
-- Récupération des données dans les variables
FETCH emp_cursor INTO v_emp_id, v_emp_name, v_salary;
-- Condition pour sortir de la boucle lorsque le curseur n'a plus de lignes
EXIT WHEN emp_cursor%NOTFOUND;
-- Traitement des données récupérées
DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_emp_name || ', Salary: ' ||
v_salary);
END LOOP;
-- Fermeture du curseur
CLOSE emp_cursor;
END;
Exercice 1:
Nous considérons le schéma relationnel suivant modélisant les activités d’une boutique en ligne.
CREATE TABLE clients (
client_id NUMBER PRIMARY KEY,
nom VARCHAR2(50),
email VARCHAR2(100)
);
CREATE TABLE commandes (
commande_id NUMBER PRIMARY KEY,
client_id NUMBER,
montant NUMBER
);
INSERT INTO clients VALUES (1, 'Alice', '[email protected]');
2
INSERT INTO clients VALUES (2, 'Bob', '[email protected]');
INSERT INTO commandes VALUES (101, 1, 100);
INSERT INTO commandes VALUES (102, 1, 200);
INSERT INTO commandes VALUES (103, 2, 150);
INSERT INTO commandes VALUES (104, 2, 750);
1- Créez une fonction totale_commandes_client pour calculer le montant total des
commandes pour un client donné.
2- Créez une procédure qui utilise un curseur pour afficher le nom des clients et le montant
total de leurs commandes en utilisant la fonction précédente totale_commandes_client.
Exercice 2:
Nous considérons le schéma relationnel suivant modélisant les activités d’un hôtel.
Client (NumClient, Nom, Prenom, Nationalite)
Chambre (NumChambre , #IdType, Etage, Equipement, Poste_Tel)
Type ( IdType, Nom, Tarif)
Reservation (#NumClient, #NumChambre, DateDebut, DateFin, Nb_Pers)
CREATE TABLE Client (
NumClient NUMBER PRIMARY KEY,
Nom VARCHAR2(50),
Prenom VARCHAR2(50),
Nationalite VARCHAR2(50)
);
-- Créez la table "Chambre" pour stocker les informations sur les chambres.
CREATE TABLE Chambre (
NumChambre NUMBER PRIMARY KEY,
IdType NUMBER,
Etage NUMBER,
Equipement VARCHAR2(100),
Poste_Tel VARCHAR2(15)
);
-- Créez la table "Type" pour stocker les informations sur les types de chambres.
CREATE TABLE Type (
IdType NUMBER PRIMARY KEY,
Nom VARCHAR2(50),
Tarif NUMBER
);
-- Créez la table "Reservation" pour stocker les informations sur les réservations.
CREATE TABLE Reservation (
NumClient NUMBER,
NumChambre NUMBER,
DateDebut DATE,
DateFin DATE,
Nb_Pers NUMBER,
PRIMARY KEY (NumClient, NumChambre),
FOREIGN KEY (NumClient) REFERENCES Client(NumClient),
FOREIGN KEY (NumChambre) REFERENCES Chambre(NumChambre)
3
);
-- Insérez quelques données d'exemple dans les tables.
-- Clients
INSERT INTO Client VALUES (1, 'Doe', 'John', 'USA');
INSERT INTO Client VALUES (2, 'Smith', 'Alice', 'UK');
-- Ajout de quatre lignes d'insertion de données pour la table "Client"
INSERT INTO Client VALUES (3, 'Garcia', 'Maria', 'Spain');
INSERT INTO Client VALUES (4, 'Müller', 'Hans', 'Germany');
INSERT INTO Client VALUES (5, 'Chen', 'Wei', 'China');
INSERT INTO Client VALUES (6, 'Santos', 'Luiz', 'Brazil');
-- Types de chambres
INSERT INTO Type VALUES (1, 'Standard', 100);
INSERT INTO Type VALUES (2, 'Deluxe', 150);
-- Chambres
INSERT INTO Chambre VALUES (101, 1, 1, 'TV, Wi-Fi', '101');
INSERT INTO Chambre VALUES (102, 1, 1, 'TV, Wi-Fi', '102');
INSERT INTO Chambre VALUES (201, 2, 2, 'TV, Jacuzzi', '201');
-- Réservations
INSERT INTO Reservation VALUES (1, 101, TO_DATE('2021-11-15', 'YYYY-MM-DD'), TO_DATE('2021-11-20',
'YYYY-MM-DD'), 2);
INSERT INTO Reservation VALUES (2, 201, TO_DATE('2023-12-10', 'YYYY-MM-DD'), TO_DATE('2023-12-15',
'YYYY-MM-DD'), 1);
INSERT INTO Reservation VALUES (1, 101, TO_DATE('2020-12-10', 'YYYY-MM-DD'), TO_DATE('2020-12-15',
'YYYY-MM-DD'), 1);
1. Ecrire un bloc PL/SQL qui permet d’accorder la nationalité tunisienne aux clients du
numéro 1 au numéro 6.
2. Ecrire une fonction PL/SQL retournant le nombre des chambres qui n’ont pas été
réservés depuis 10 Juin 2022.
3. Ecrire une procédure qui affiche les noms des trois premiers clients qui ont effectué
le plus grand nombre des réservations
4. Ecrire le script PL/SQL permettant de :
a. Prendre en entrée une année (saisie utilisateur).
b. Fournir en sortie les noms et les nationalités des clients ayant fait une
réservation en cette année. Vous pouvez utiliser EXTRACT YEAR.
