Exercice : Interroger une base RO : Utilisation des tables imbriquées [Relationnel objet]

Interroger une base RO : Utilisation des tables imbriquées

Dans ce troisième exercice, vous expérimenterez la manipulation des collections implémentées sous forme de tables imbriquées.

Question

Ecrivez une requête permettant de renvoyer le nombre de produits achetés par le client 1.

Indice

Il faut utiliser la pseudo-jointure TABLE() entre la table des factures et la table imbriquée des lignes de facture.

num. | Ligne | texte | copie |

CTRL+C pour copier, CTRL+V pour coller

SELECT ...
FROM Facture f, TABLE(f.Lignes_facture) lf
WHERE ...

SELECT ...
FROM Facture f, TABLE(f.Lignes_facture) lf
WHERE ...

Solution

num. | Ligne | texte | copie |

CTRL+C pour copier, CTRL+V pour coller

SELECT SUM(lf.qte) AS nombre
FROM Facture f, TABLE(f.Lignes_facture) lf
WHERE f.fkClient.num = 1;

SELECT SUM(lf.qte) AS nombre
FROM Facture f, TABLE(f.Lignes_facture) lf
WHERE f.fkClient.num = 1;

Question

Ecrivez une requête permettant de renvoyer les numéros et noms des clients ayant payé au moins une facture avec plus de 5 articles.

Indice

Faites un regroupement sur les numéros de client, noms des client et factures.

Solution

num. | Ligne | texte | copie |

CTRL+C pour copier, CTRL+V pour coller

SELECT DISTINCT f.fkClient.num AS numero, f.fkClient.nom AS nom
FROM Facture f, TABLE(f.Lignes_facture) lf
GROUP BY f.fkClient.num,f.fkClient.nom, f.num
HAVING SUM(lf.qte) > 5;

SELECT DISTINCT f.fkClient.num AS numero, f.fkClient.nom AS nom
FROM Facture f, TABLE(f.Lignes_facture) lf
GROUP BY f.fkClient.num,f.fkClient.nom, f.num
HAVING SUM(lf.qte) > 5;

Notons que La clause GROUP BY spécifie que l'on regroupe les enregistrements par numéro de client, nom de client et facture. Dans ce cas, à quoi sert le SELECT DISTINCT ?

Sans celui-ci on obtiendrait quelque chose comme le tableau ci-après (les enregistrements étant regroupés par client et par facture) :

Résultat sans le DISTINCT et en projetant f.num et lf.qte
f.fkClient.num	f.fkClient.nom	f.num	lf.qte
1	A	1	6
1	A	2	7
1	A	3	6
2	B	4	12
2	B	5	6
...	...	...

Comme on souhaite ne projeter que le numéro et le nom du client, toujours sans le DISTINCT, on obtiendrait :

Résultat sans le DISTINCT
f.fkClient.num	f.fkClient.nom
1	A
1	A
1	A
2	B
2	B
...	...

Le DISTINCT permet donc bien de supprimer les doublons :

Résultat avec le DISTINCT
f.fkClient.num	f.fkClient.nom
1	A
2	B
...	...

Question

Quelle aurait été une solution plus élégante à la question précédente ? Proposez une implémentation de cette solution en RO et une nouvelle requête utilisant cette solution.

Solution

Il aurait été plus judicieux d'implémenter une méthode quantite() dans la table des factures. On ajoute MEMBER FUNCTION quantite RETURN NUMBER en fin de déclaration de type.

Mise à jour du type typFacture

num. | Ligne | texte | copie |

CTRL+C pour copier, CTRL+V pour coller

ALTER TYPE typFacture ADD MEMBER FUNCTION quantite RETURN NUMBER CASCADE;

ALTER TYPE typFacture ADD MEMBER FUNCTION quantite RETURN NUMBER CASCADE;

Ajout du corps de la méthode quantite()

num. | Ligne | texte | copie |

CTRL+C pour copier, CTRL+V pour coller

CREATE OR REPLACE TYPE BODY typFacture AS 
  MEMBER FUNCTION total RETURN NUMBER 
  IS 
    vTotal NUMBER;
  BEGIN
    SELECT SUM(lf.qte*lf.fkproduit.prix) INTO vTotal
    FROM Facture f, TABLE(f.lignes_facture) lf
    WHERE f.num = SELF.num;
    RETURN vTotal;
  END total; 
  
  MEMBER FUNCTION quantite RETURN NUMBER IS 
    vQuantite NUMBER; 
  BEGIN 
    SELECT SUM(lf.qte) INTO vQuantite 
    FROM Facture f, TABLE(f.Lignes_facture) lf 
    WHERE f.num = self.num; 
    RETURN vQuantite; 
  END quantite; 
END;

CREATE OR REPLACE TYPE BODY typFacture AS 
  MEMBER FUNCTION total RETURN NUMBER 
  IS 
    vTotal NUMBER;
  BEGIN
    SELECT SUM(lf.qte*lf.fkproduit.prix) INTO vTotal
    FROM Facture f, TABLE(f.lignes_facture) lf
    WHERE f.num = SELF.num;
    RETURN vTotal;
  END total; 
  
  MEMBER FUNCTION quantite RETURN NUMBER IS 
    vQuantite NUMBER; 
  BEGIN 
    SELECT SUM(lf.qte) INTO vQuantite 
    FROM Facture f, TABLE(f.Lignes_facture) lf 
    WHERE f.num = self.num; 
    RETURN vQuantite; 
  END quantite; 
END;

La requête d'exploitation

num. | Ligne | texte | copie |

CTRL+C pour copier, CTRL+V pour coller

SELECT DISTINCT f.fkClient.num AS numero, f.fkClient.nom AS nom
FROM Facture f 
WHERE f.quantite() > 5;

SELECT DISTINCT f.fkClient.num AS numero, f.fkClient.nom AS nom
FROM Facture f 
WHERE f.quantite() > 5;

Agrandir

défilement haut

défilement bas

Introduction
>Théorie (8h)
>Application (4h)
v Pratique (6h)
- Introduction
- Exercice : Concevoir et implémenter une base RO
- Exercice : Alimenter une base RO
- Exercice : Interroger une base RO : Utilisation des OID
- Exercice : Interroger une base RO : Utilisation des méthodes
- Exercice : Interroger une base RO : Utilisation des tables imbriquées
- Conclusion
Test (1h)
>Complément (1h)

Accueil
Module
Outils

Rechercher

Ecouter