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.

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

Solution

1
SELECT SUM(lf.qte) AS nombre
2
FROM Facture f, TABLE(f.Lignes_facture) lf
3
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

1
SELECT DISTINCT f.fkClient.num AS numero, f.fkClient.nom AS nom
2
FROM Facture f, TABLE(f.Lignes_facture) lf
3
GROUP BY f.fkClient.num,f.fkClient.nom, f.num
4
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

1
ALTER TYPE typFacture ADD MEMBER FUNCTION quantite RETURN NUMBER CASCADE;

Ajout du corps de la méthode quantite()

1
CREATE OR REPLACE TYPE BODY typFacture AS
2
  MEMBER FUNCTION total RETURN NUMBER
3
  IS
4
    vTotal NUMBER;
5
  BEGIN
6
    SELECT SUM(lf.qte*lf.fkproduit.prix) INTO vTotal
7
    FROM Facture f, TABLE(f.lignes_facture) lf
8
    WHERE f.num = SELF.num;
9
    RETURN vTotal;
10
  END total;
11
12
  MEMBER FUNCTION quantite RETURN NUMBER IS
13
    vQuantite NUMBER;
14
  BEGIN
15
    SELECT SUM(lf.qte) INTO vQuantite
16
    FROM Facture f, TABLE(f.Lignes_facture) lf
17
    WHERE f.num = self.num;
18
    RETURN vQuantite;
19
  END quantite;
20
END;

La requête d'exploitation

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