SQL3

!ANCIEN TME SQL3

Aller vers http://www-bd.lip6.fr/wiki/site/enseignement/master/mlbda/tmesql3

Remise du TME SQL3

Directement à la fin de la dernière séance, voir RemiseDesTme.

Sujet

Voir l'énoncé : page 6 Exercice : Application CAO

Prérequis : votre environnement doit déjà être configuré (voir Connexion Oracle).

Ouvrir une fenêtre de terminal (xterm), pour exécuter les commandes suivantes :

L'archive sql3.tgz contient des scripts SQL*Plus utiles pour le TME :

Documentation

Lire attentivement la syntaxe SQL3 (voir poly ) et les réponses aux questions fréquentes ci-dessous.

La DocumentationOracle et les QuestionSurSql

Questions fréquentes sur SQL3

• mot réservés : les mots suivants sont des mots-clé du langage: table, type, or, cube, ...
• commentaire : les deux tirets (--) qui indiquent une ligne de commentaire doivent être positionnés en début de ligne.

• erreur de casse. Le compilateur SQL3 n'est pas sensible à la casse. Un attribut et son type ne peuvent pas être le même mot. Exemple d'erreur de casse :

  • create type X as object (adresse Adresse ); est faux car "adresse" (en minuscule) et "Adresse" (avec A majuscule) sont considérés comme le meme mot.
• nom d'attribut trop long : choisir un nom d'attribut plus court (longueur maxi: ...)
• creation d'une table pour stocker les objets. Ne pas compiler l'ordre de création de table.

• table imbriquée: les tables imbriquées sont déclarées dans l'ordre de création de la table principale. Exemple, la table T contient les objets X dont les attributs x1 et x2 sont ensemblistes.

  • create type Y as table of number(3), create type X as object ( x1 Y, x2 Y);
  • create table T of X nested table x1 store as T1, nested table x2 store as T2;

• erreur lors de la suppression d'un type : forcer la suppression d'un type utlisé dans la définition d'autre types

  • drop type Personne force;

• signature d'une méthode : type des paramètres. Ne pas indiquer la taille des types.

  • Ex: signature fausse: f(a in number(5), b in varchar2(30)), signature correcte: f(a in number, b in varchar2)
• insertion : La commande insert insère un seul tuple (ou un seul objet). Ne pas oublier les parenthèses après le mot clé values( ... ): Ne pas oublier le constructeur (=nom du type) pour insérer un objet.

• déréférencement: Contrairement à SQL, PL/SQL ne supporte pas les expressions de chemins avec traversée de références. Pour contourner ce manque, écrire une requête pour déréférencer explicitement chaque référence. Exemple pour obtenir le nom de la matière d'une pièce de base :

  • n varchar(30);
  • begin
  • incorrect: n := self.est_en.nom;
  • correct: select deref(self.est_en).nom into n from dual;

• Héritage : lors de la définition d'un type, mentionner not final lors de la création du type, ou not final cascade si on modifie un type existant qui est lui même un sous-type. Exemple :

  • create type Personne (....) not final;
  • create type Candidat under Personne (...) not final ;
  • OU en modifiant un type existant: alter type Candidat not final cascade;

• transtypage : utiliser la fonction "treat" pour forcer SQL à considérer un objet d'un sous-type dans une colection d'un super-type Exemple :

  • create type A as object (B number) not final; create type C under A (D number); create table TA of A; insert into TA values(C(1,2));
  • pour récupérer la valeur de l'attribut D de l'objet créé, utiliser : select treat(value(m) as C).D from TA m;

• Polymorphisme : une méthode définie dans un type parent peut être spécialisée dans un sous-type. La signature de la méthode commence par le mot overriding :

  • create type Personne ( member function F return number) not final;
  • create type Candidat under Personne ( overriding member function F return number);

• Méthode abstraite : possibilité de définir une méthode abstraite seulement dans un type qui n'est jamais instancié.

  • Pour cela, il faut définir d'une part un type not instantiable, et d'autre part une méthode elle même not instanciable
  • create type PieceBase ... ( .... ) not final not instantiable ;
  • signature de la méthode abstraite : not instantiable member function volume return Number;

• Affecter le résultat d'une requête à une variable ensembliste. En PL/SQL, il est possible de récupérer, dans une variable, l'ensemble des objets retournés par une requête SQL, grâce à l'instruction d'affectation bulk collect into placée juste avant la clause from de la requête.

  • listeNoms EnsNoms; -- listeNoms est une variable de type ensemble de chaînes de caractères
  • begin
  • SELECT m.nom bulk collect into listeNoms FROM LesMatieres m;

• Syntaxe des fonctions value() et deref()

  • La fonction value() prend en paramètre une variable déclarée dans la clause FROM. Le type retourné est identique à celui de la variable. Exemple:

    • value(m).nom

  • La fonction deref() prend en paramètre une expression de chemin dont le type est une référence. Le type retourné est un type objet. Exemples :

    • deref(p.est_en)
    • deref(value(m)).nom

• Syntaxe du return

  • return est suivi d'une expression PL/SQL, mais pas d'une requete SQL
  • ne pas écrire: return (select ... from ...)
  • il faut d'abord affecter le résultat de la requête à une variable v, puis faire return v;

Requêtes

Pour les étudiants dont la base CAO n'est pas correctement instanciée, vous pouvez tester vos requêtes sur la base suivante :

Pour cela, créer des synonymes vers les tables de l'utilisateur ens:

retour vers Les Travaux Dirigés, Les cours, Accueil