TME SQL3

Enoncé de l'exercice : Application CAO dans le TD3-4 Solution schéma CAO: Schéma CAO

Lire et préparer l'accès à Oracle avec SQLWorkbench

L'archive sql3.tgz contient des scripts utiles. Décompresser l'archive dans votre dossier pour le TME. Si nécessaire, l'archive est aussi disponible au format zip sql3.zip

Pour compiler un type ajouter les 2 lignes suivantes après CHAQUE instruction create type

/
SHOW errors

Pour afficher tous les types, les tables et autres objets que vous avez créés :

SELECT object_name, object_type, STATUS
FROM user_objects
ORDER BY object_type, object_name, STATUS;

Pour supprimer un type :

DROP TYPE UN_TYPE force;

Pour supprimer une table :

DROP TABLE UNE_TABLE;

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

La Documentation sur Oracle 10G et les FAQ pour les TME SQL et PL/SQL

Les mots suivants sont des mots-clé du langage: table, type, or, cube, …

Les deux tirets - - qui indiquent une ligne de commentaire doivent être positionnés en ==début== de ligne.

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.

Choisir un nom d'attribut plus court (longueur maxi: …)

Sert pour stocker les objets. Ne pas compiler l'ordre de création de table.

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; 

Forcer la suppression d'un type utlisé dans la définition d'autre types

• drop type Personne force;

Type des paramètres. Ne pas indiquer la taille des types. Exple :

• signature fausse: f(a in number(5), b in varchar2(30))
• signature correcte: f(a in number, b in varchar2)

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 (égal au nom du type) pour insérer un objet.

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 rencontrée dans un chemin commençant par self. Exemple pour obtenir le nom de la matière d'une pièce de base. Expression incorrecte:

  n VARCHAR(30);
  BEGIN
  expression incorrecte: n := self.est_en.nom;

Solution correcte

  n VARCHAR(30);
  BEGIN
  SELECT DEREF(self.est_en).nom INTO n 
  FROM dual;
  RETURN n;
  END;

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;

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;

Une méthode définie (sans être implémentée) dans un type parent doit être spécifiée dans chaque sous-type.

• Dans le type parent, la signature de la méthode commence par le mot not instantiable :
• Dans le sous-type, la signature de la méthode commence par le mot overriding :

•  CREATE TYPE Personne ( NOT instanciable member FUNCTION F RETURN NUMBER) NOT instanciable NOT final;
   CREATE TYPE Candidat UNDER Personne ( overriding member FUNCTION F RETURN NUMBER);

Remarque, un autre cas d'usage consiste à d'implémenter une méthode dans un type A ET dans un sous type B. Dans ce cas, ne pas préfixer la signature de la méthode du type A avec not instantiable .

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;

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;

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) ou deref(value(m)).nom

Le mot clé 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;

Afficher seulemnet les pièces de type Cube :

SELECT VALUE(p)
FROM LesPBase p
WHERE VALUE(p) IS OF (Cube);

L'ancienne documentation pour se connecter depuis la PPTI Connexion au serveur Oracle 11 et SQL3 avec le client sqlplus

retour vers MU4IN801 MLBDA : Modèles et Langages pour les Bases de Données Avancés