MediaTek V

[1 h]

Facture n'est pas une table objet ici, elle n'est pas référencée

Adresse (#num:integer, #rue:string, #cp:integer, #ville:string, #client => Client>)
Type TClient <num:integer, nom:string, prenom:string, ddn:date, tel:string, genre:{'m','f'}>
Client de TClient (#num) 
Type TProduit <num:integer, designation:string, prix:float, stock:integer>
Produit de TProduit (#num)
Type TFacture <num:integer, client =>o Client, produit =>o Produit, qte:integer>
Facture de TFacture (#num, #client, #produit)

Réalisez l'implémentation SQL3 sous Oracle.

Client

CREATE TYPE TClient AS OBJECT (
  num NUMBER(4),
  nom VARCHAR(20),
  prenom VARCHAR(20),
  ddn DATE,
  tel VARCHAR(10),
  genre CHAR(1)
);
/
CREATE TABLE Client OF TClient( 
  PRIMARY KEY (num),
  CHECK (genre IN ('m', 'f'))
); 
CREATE TABLE Adresse (
	num NUMBER(4),
	rue VARCHAR(255),
	cp NUMBER(5),
  ville VARCHAR(255),
  client NUMBER(4) REFERENCES Client (num),
  PRIMARY KEY (num, rue, cp, ville, client)
);

Produit

CREATE TYPE TProduit AS OBJECT ( 
  num NUMBER(10),
  designation VARCHAR(128), 
  prix NUMBER(5,2),
  stock NUMBER(4)
);
/
CREATE TABLE Produit of TProduit (PRIMARY KEY (num));

Facture

CREATE TYPE TFacture AS OBJECT ( 
  num NUMBER(10),
  client REF TClient,
  produit REF TProduit,
  qte NUMBER(4)
);
/
CREATE TABLE Facture OF TFacture ( 
  SCOPE FOR (client) IS Client,
  SCOPE FOR (produit) IS Produit
);

Sous Oracle on ne pourra créer de contrainte UNIQUE incluant un OID, donc ici on ne peut pas spécifier la contrainte (#num, #client, #produit).

Alimenter une base RO

Une fois la base créée, insérer des données pour l'alimenter.

On négligera la table Adresse pour la suite de l'exercice (aucun de nos clients n'aura d'adresse).

Initialiser les tables client et produit avec les données de votre choix (au moins deux clients et deux produits).

Effectuez l'insertion comme en relationnel.

INSERT INTO Client (num, nom, prenom, ddn, tel, genre) 
VALUES (1, 'Colomb', 'Christophe', to_date('01091451', 'DDMMYYYY'), '0812456589' , 'm');
INSERT INTO Client (num, nom, prenom, ddn, tel, genre) 
VALUES (2, 'Morin', 'Bernard', to_date('27051931', 'DDMMYYYY'), '0126483621' , 'm');
INSERT INTO Produit (num, designation, prix, stock)
VALUES (1, 'The Matrix', 23.5, 100) ;
INSERT INTO Produit (num, designation, prix, stock)
VALUES (2, 'The Hobbit', 49.3, 100) ;

Initialiser les factures avec les données de votre choix (deux factures de deux lignes chacune au moins).

Récupérer les OID pour insérer les références aux produits et clients au sein d'une procédure PL/SQL.

Insertion de références par OID (INSERT)

Blocs PL/SQL : Procédure, fonction, bloc anonyme

DECLARE
  oidclient1 REF TClient; 
  ...
BEGIN
  SELECT REF(c) INTO oidclient1
  FROM Client c
  WHERE c.num=...;
  ...
  INSERT INTO Facture (num, client, produit, qte) 
  VALUES (1,oidclient1, ..., 3);
  
  ...
 
END;
/

DECLARE
  oidclient1 REF TClient;
  oidproduit1 REF TProduit;
  oidclient2 REF TClient;
  oidproduit2 REF TProduit;
BEGIN
  SELECT REF(c) INTO oidclient1
  FROM Client c
  WHERE c.num=1;
  SELECT REF(c) INTO oidclient2
  FROM Client c
  WHERE c.num=2;
  SELECT REF(p) INTO oidproduit1
  FROM Produit p
  WHERE p.num=1;
  SELECT REF(p) INTO oidproduit2
  FROM Produit p
  WHERE p.num=2;
  INSERT INTO Facture (num, client, produit, qte) 
  VALUES (1, oidclient1, oidproduit1, 3);
  INSERT INTO Facture (num, client, produit, qte) 
  VALUES (1, oidclient1, oidproduit2, 2);
  INSERT INTO Facture (num, client, produit, qte) 
  VALUES (1, oidclient2, oidproduit2, 6);
  INSERT INTO Facture (num, client, produit, qte) 
  VALUES (1, oidclient2, oidproduit1, 1);
END;
/

Questions par navigation d'OID

Vous allez à présent expérimenter la manipulation des OID pour naviguer d'enregistrement en enregistrement, sans utiliser de jointure.

Écrivez une requête permettant d'afficher la liste des factures existantes, avec le nom et le prénom du client.

NUM CLIENT.NOM           CLIENT.PRENOM      
--- -------------------- --------------------
  1 Colomb               Christophe           
  1 Morin                Bernard        

COLUMN num FORMAT A3
SELECT f.num, f.client.nom, f.client.prenom
FROM Facture f
GROUP BY f.num, f.client.nom, f.client.prenom;

Écrivez une requête permettant d'afficher le montant total de chaque facture, en rappelant le numéro du client pour chaque facture.

NUM CLI TOTAL
--- --- -----
  1   1 169.1 
  1   2 319.3 

COLUMN CLI FORMAT A3
SELECT f.num, f.client.num AS cli, SUM(f.qte * f.produit.prix) AS total
FROM Facture f
GROUP BY f.num, f.client.num
ORDER BY f.num, f.client.num

Écrivez une requête permettant d'afficher pour chaque client (num et nom) les produits (num et désignation) qu'il a déjà acheté, avec la quantité correspondante.

CLIENT.NUM CLIENT.NOM           PRODUIT.NUM PRODUIT.DESIGNATION  QTE
---------- -------------------- ----------- -------------------- ---
         1 Colomb                         1 The Matrix             3 
         1 Colomb                         2 The Hobbit             2 
         2 Morin                          1 The Matrix             1 
         2 Morin                          2 The Hobbit             6 

COLUMN PRODUIT.NUM FORMAT A11
COLUMN PRODUIT.DESIGNATION FORMAT A20
SELECT f.client.num, f.client.nom, f.produit.num, f.produit.designation, SUM(f.qte) AS qte
FROM Facture f
GROUP BY f.client.num, f.client.nom, f.produit.num, f.produit.designation
ORDER BY f.client.num, f.client.nom, f.produit.num, f.produit.designation

AccueilExercices > Exercice : MediaTek V< PrécédentSuivant >