Les objets du SGBD Relationnel ORACLE sont  les suivants :

-    les Tables,

-    les Vues,

-    les Index,

-    les Séquences,

-    les Synonymes,

-    les Clusters.

Seuls les objets TABLES, VUES, INDEX, SYNONYMES et les séquences seront vus dans ce cours.

LE RENOMMAGE :

-    alias d'attributs et 

-    alias des tables 

Exemple : 

SQL> SELECT p.pl#  num_pilote 

           FROM pilote p;

       NUM_PILOTE

     --------------------          1

                2

                3

                4

                5

                6

                8

                9

               10

               11

               12

               13

               14

               15

               16

               17

       16 ligne(s) sélectionnée(s).

Exemple :

Ecrire de 3 manières différentes une projection sur toutes les colonnes de la table PILOTE.

SQL> SELECT * FROM pilote;

SQL> SELECT a.* FROM pilote a;

SQL > SELECT pilote.* from pilote;

Même résultat dans tous les cas : 

PL# PLNOM        DNAISS   ADR                  TEL                SAL

--- ------------ -------- -------------------- ------------ ---------

1  Miranda 16/08/52 Sophia Antipolis     93548254         18009

2  St-exupéry 16/10/32 Lyon                 91548254         12300

3  Armstrong 11/03/30 Wapakoneta           96548254         24500

4  Tintin 01/08/29 Bruxelles            93548254         21100

5  Gagarine 12/08/34 Klouchino            93548454         22100

6  Baudry 31/08/59 Toulouse             93548444         21000

8   Bush 28/02/24 Milton               44556254         22000

9   Ruskoi 16/08/30 Moscou               73548254         22000

10  Mathé 12/08/38 Paris                23548254         15000

11  Yen 19/09/42 Munich               13548254         29000

12  Icare 17/12/62 Ithaques             73548211       17000,6

13  Mopolo 04/11/55 Nice                 93958211       17000,6

14  Chretien 04/11/45                      73223322       15000,6

15  Vernes 04/11/35 Paris                               17000,6

16  Tournesol 04/11/29 Bruxelles                           15000,6

17  Concorde 04/08/66 Paris                               21000,6

16 ligne(s) sélectionnée(s).

LA REDISPOSITION DES COLONNES (des attributs)

Exemple : 

SQL> desc pilote;

 Nom              Non renseigné  NULL?    Type

 ------------------------------- -------- ----

 PL#                             NOT NULL NUMBER(4)

 PLNOM                           NOT NULL VARCHAR2(12)

 DNAISS                          NOT NULL DATE

 ADR                                      VARCHAR2(20)

 TEL                                      VARCHAR2(12)

 SAL NOT NULL NUMBER(7,2)

SQL> SELECT pl#, sal, tel,plnom

           FROM pilote;

PL#       SAL TEL          PLNOM

--- --------- ------------ ------------

1     18009 93548254     Miranda

2     12300 91548254     St-exupéry

3     24500 96548254     Armstrong

4     21100 93548254     Tintin

5     22100 93548454     Gagarine

6     21000 93548444     Baudry

8    22000 44556254     Bush

9    22000 73548254     Ruskoi

10     15000 23548254     Mathé

11     29000 13548254     Yen

12     17000,6 73548211     Icare

13     17000,6 93958211     Mopolo

14     15000,6 73223322     Chretien

15     17000,6              Vernes

16     15000,6              Tournesol

17     21000,6              Concorde

LES CONSTANTES

On peut répéter une constante pour chaque ligne ramenée.

Les constantes sont de type numérique ou alphanumérique

(entre ' ').

Exemple : 

SQL> SELECT plnom NOM , 'gagne' GAIN , 

                          sal SALAIRE

        FROM pilote;

NOM          GAIN    SALAIRE

------------ ----- ---------

Miranda      gagne     18009

St-exupéry   gagne     12300

Armstrong    gagne     24500

Tintin       gagne     21100

Gagarine     gagne     22100

Baudry       gagne     21000

Bush         gagne     22000

Ruskoi       gagne     22000

Mathé        gagne     15000

Yen          gagne     29000

Icare        gagne   17000,6

Mopolo       gagne   17000,6

Chretien     gagne   15000,6

Vernes       gagne   17000,6

Tournesol    gagne   15000,6

Concorde     gagne   21000,6

LES CALCULS HORIZONTAUX

Le calcul horizontal fait intervenir une ou plusieurs colonnes d'une même table dans un tuple.

Exemple : 

SQL> SELECT pl#, sal*12  "SALAIRE ANNUEL"

           FROM pilote;

PL# SALAIRE ANNUEL

--- ---------------

1       216108

2       147600

3       294000

4       253200

5       265200

6       252000

8         264000

9         264000

10      180000

11      348000

12      204007,2

13      204007,2

14      180007,2

15      204007,2

16      180007,2

17      252007,2

16 ligne(s) sélectionnée(s).

LES CALCULS VERTICAUX

Les calculs verticaux font intervenir les valeurs d'une colonne sur l'ensemble ou un sous-ensemble des tuples ramenés par une requête.

Remarque : 

l'alias d'une colonne ou d'une expression sera de 30 caractères max. et sera entre "" si l'alias contient des séparateurs.

Exemple : 

SQL> SELECT avtype TYPE,

                          SUM(cap) "CAPACITE TOTALE"

           FROM avion

                                                              GROUP BY avtype; 

TYPE       CAPACITE TOTALE

---------- ---------------

A300                  1300

A320                   320

B707                   400

B727                   250

Caravelle              300

Concorde               650

6 ligne(s) sélectionnée(s).

Le mot clé DISTINCT dans la clause SELECT :

        - réalise un tri sur les colonnes et           - élimine les doublons.

Exemple : 

SQL> SELECT DISTINCT avtype FROM avion;

         AVTYPE

         ----------

         A300

         A320

         B707

         B727

          Caravelle           Concorde

         6 ligne(s) sélectionnée(s).

Il est possible de faire un DISTINCT de plusieurs colonnes. Exemple : 

SQL> SELECT DISTINCT avtype,cap FROM avion;

     AVTYPE           CAP

     ---------- ---------

     A300             300

     A300             400

     A320             320

     B707             400

     B727             250

     Caravelle        300

     Concorde         300

     Concorde         350

SELECT

FROM

WHERE [NOT] prédicat1           [AND|OR] 

                 [NOT] prédicat2

La clause WHERE permet d’effectuer un filtrage de tuples.

C'est à dire sélectionner un sous-ensemble de lignes dans les tables. 

Seules les lignes vérifiant la clause WHERE seront retournées.

Prédicat : 

nom de colonne                                         nom de colonne constante                    OPERATEUR      constante

expression                                                    expression

-        Les opérateurs logiques (AND, OR) peuvent être utilisés dans le cas de prédicats multiples.

-        L'opérateur NOT inverse le sens du prédicat.

-        Pas de limite dans le nombre de prédicats.

Exemple 6 :         

Noms et adresses des pilotes

SQL> SELECT plnom || ’---->’ || adr

           FROM pilote;

PLNOM||’---->’||ADR

-------------------------------------

Miranda     ---->Sophia Antipolis

St-exupéry  ---->Lyon

Armstrong   ---->Wapakoneta

Tintin      ---->Bruxelles

Gagarine    ---->Klouchino

Baudry      ---->Toulouse

Bush        ---->Milton

Ruskoi      ---->Moscou

Mathé       ---->Paris

Yen         ---->Munich

Icare       ---->Ithaques

Mopolo      ---->Nice

Chretien    ---->

Vernes      ---->Paris

Tournesol ---->Bruxelles Concorde    ---->Paris scott       ---->Nice Conficius   ---->Pekin

18 ligne(s) sélectionnée(s).

Fonctions numériques 

-    ABS(n) : valeur absolue de n

-    SIGN(n) : signe de n (-1 ou 0 ou +1)

-    CEIL(n) : plus petit entier >= n

-    FLOOR(n) : plus grand entier <= n

-    MOD(m,n) : reste de la division de m par n

-    POWER(m,n) : m élevé à la puissance n

-    SQRT(n) : racine carrée de n (message d'erreur si n < 0)

-    ROUND(n,[m]) : arrondi de n à 10-m

Ex :       ROUND(125.2)  = 125

             ROUND(1600,-3) = 2000

             ROUND(1100,- 3)      = 1000

               ROUND(345.343,2) = 345.34

               ROUND(345.347,2) = 345.35

-    TRUNC(n,[m]) : n tronqué à 10-m

Ex :         TRUNC(2500,-3) = 2000          TRUNC(2400,-3) = 2000

             TRUNC(345.343,2)    = 345.34

             TRUNC(345.347,2)    = 345.34

Fonctions caractères

-        LENGTH(chaîne) : longueur de la chaîne

-        UPPER(chaîne) : toutes les lettres de la chaîne en majuscules

-        LOWER(chaîne) : toutes les lettres de la chaîne en minuscules

-        INITCAP(chaîne) : première lettre de chaque mot de la chaîne en majuscules, les autres en minuscules)

-        LPAD(chaîne,lg,[chaîne]) : compléter à gauche par une chaîne de caractères sur une longueur donnée. Exemple : 

    LPAD('DUPOND',10,'*# ') = '*#*#DUPOND'

-        RPAD(chaîne,lg,[chaîne]) : compléter à droite par une chaîne de caractères sur une longueur donnée. Exemple :  

   RPAD('DUPOND',10,'* ') = 'DUPOND****'

Remarque : LPAD et RPAD peuvent tronquer une chaîne si lg < longueur totale de la chaîne.

-        LTRIM(chaîne[,caractères]) : suppression à gauche de caractères dans la chaîne. Exemple :

                                                                                              LTRIM('DUPOND','DU ') = 'POND'

-        RTRIM(chaîne[,caractères]) : suppression à droite de caractères dans la chaîne. Exemple :

                                                                                                       RTRIM('DUPOND','UD ') = 'DUPON'

-        SUBSTR(chaîne,position[,longueur]) : extraction d'une chaîne à partir d'une position donnée et sur une longueur donnée Exemple :

                                                                                           SUBSTR('DUPOND',2,3) = 'UPO'

-        INSTR(chaîne, sous_chaîne,[,position[,n]]) : recherche de la position de la n ième occurence d'une chaîne de caractères dans une autre chaîne de caractères à partir d'une position données. Exemple : 

                                                                                        INSTR('DUPOND','D',1,2) = 6

-        REPLACE(chaîne,car[,chaîne]) : remplace un ensemble de caractères

Exemples : 

                                                                                                           REPLACE('TUTU','U', 'OU') = 'TOUTOU'

                                                                                        REPLACE('TATA','T') = 'AA'

-        TRANSLATE(chaîne,car_source,car_cible) : trancodage de certains caractères par d'autres caractères dans une chaîne de caractères. Exemples : 

TRANSLATE(plnom,'AM','12') : 

-        le A est remplacé par 1         - le M est remplacé par 2

                                                                             dans les noms des pilotes

-        SOUNDEX(chaîne) : (voir 2.7.2)

-        ASCII(chaîne) : donne la correspondance ASCII du premier caractère de la chaîne.

Exemple : ASCII('ADFGRSE') = 65

-        CHR(n) : caractère correspondant à la valeur de n en ASCII.

Exemple : CHR(65) = 'A'

Fonctions date :

-        LAST_DAY(date) : dernier jour du mois d’une date donnée

-        NEXT_DAY(date, jour) : date du prochain jour à partir d'une date donnée.

-        ADD_MONTHS(date,n) : ajoute n mois à une date donnée.

-        MONTHS_BETWEEN(date1,date2) : nombre de mois entre 2 dates.

-        ROUND(date[,'precision']) : arrondi d'une date en fonction de la précision

Exemples : SYSDATE = '12-JUL-96'

                                                                                               ROUND(sysdate,'MM') = '01-JUL-96'

                                                                                                            ROUND(sysdate + 4 ,'MM') = '01-AUG-96'

                                                                                               ROUND(sysdate,'YY') = '01-JAN-97'

-        TRUNC(date[,'precision']) : truncature d'une date en fonction de la précision Exemples : 

                                                                                               TRUNC(sysdate,'MM') = '01-JUL-96'

                                                                                                          TRUNC(sysdate + 4 ,'MM') = '01-JUL-96'

                                                                                              TRUNC(sysdate,'YY') = '01-JAN-96'

Fonctions de conversion de types :

-        TO_NUMBER(chaîne) : conversion d'une chaîne de caractères en nombre

Exemple : TO_NUMBER('567') = 567

-        TO_CHAR(val[,'format']) : conversion d'une expression (date ou numérique) en chaîne de caractères selon un format de présentation.

-        TO_DATE(chaîne[,'format']) : conversion d'une chaîne en date selon un format.

Quelques formats numériques :

                                                                 9      Affichage de cette valeur si elle est différente de 0

                                                                 0     Affichage de zéros à gauche pour une valeur à zéro

                                                               $        Affichage de la valeur préfixée par le signe '$ '

                                                   ,    Affichage de ',' à l'endroit indiqué

                                                             .   Affichage du point décima à l'endroit indiqué

                                                                                                           Exemple : TO_CHAR(1234,'0999999') = 0001234

Quelques formats de conversion de date :                           

                                                TO_CHAR(date[,’format’])

FORMAT étant la combinaison de codes suivants:

                                           YYYY       Année

                                                      YY       2 derniers chiffres de l'année

                             MM                      numéro du mois

                                                      DD       numéro du jour dans le mois

                                              HH      heure sur 12 heures

                              HH24                     heure sur 24 heures

 MI  minutes  SS secondes        

Exemple : 

SELECT 

        TO_CHAR(SYSDATE,'DD MM YYYY HH24 : MI') 

FROM dual;

==> 01 07 1996 10 : 24

Pour avoir les dates en lettres utiliser les formats suivants : 

        YEAR          année en toutes lettres

       MONTH       mois en toutes lettres

        MON            nom du mois sur 3 lettres

        DAY             nom du jour

        DY nom du jour sur 3 lettres          SP  nombre en toutes lettres      

Exemple :  

TO_CHAR(SYSDATE,' « LE » DD MONTH YYYY

« A » HH24 : MI')

       ==> LE 26 SEPTEMBER 1996 A 16 : 30

Fonctions diverses :

NVL(expr,valeur) 

==>  Si expr IS NULL  Alors valeur  Sinon expr  Finsi

Exemple : 

SQL> SELECT   NVL(sal,0)

                                                  FROM pilote;

DECODE(expression, valeur1, result1,

                                                                 [, valeur2, result2] [,defaut]

==> Si expression = valeur1 Alors result1

Sinon Si expression = valeur2 Alors result2             Sinon defaut Finsi Finsi

Remarque : result1, result2, defaut peuvent être de types différents.

Exemple :

                                                                                                             Select plnom, decode(tel, null, 'Pas de tél.', 'Tél : '|| tel)

"Info Tél" From Pilote;

PLNOM        Info Tel

Miranda      Tel : 93548254

St-exupery   Tel : 91548254

…

Vernes       Pas de tel.

Tournesol    Pas de tel.

…

Exemple

Select Sysdate, user From Pilote;

SYSDATE   USER

--------- --------

08-JUN-99 SYSTEM

08-JUN-99 SYSTEM

08-JUN-99 SYSTEM

08-JUN-99 SYSTEM

08-JUN-99 SYSTEM

08-JUN-99 SYSTEM

08-JUN-99 SYSTEM

08-JUN-99 SYSTEM …

16 rows selected.

La valeur de Sysdate et User est repétée autant de fois qu'il ya de lignes dans la table. 

Exemple

La table DUAL

La table DUAL possède une seule colonne DUMMY et une seule ligne avec pour valeur X. Cette table sert à sélectionner des constantes, des pseudo colonnes ou des expressions en une seule ligne.

Exemple : 

SQL> select SYSDATE, USER FROM ; 

SYSDATE   USER

--------- ---------

08-JUN-99 SYSTEM

Principe : 

lorsque dans un prédicat un des 2 arguments n'est pas connu, on utilise les sous-interrogations.

        SELECT

       FROM

       WHERE variable Op  ?

Le ? n'étant pas connu, il sera le résultat d'une sous-requête.   

Règle d'exécution : 

c’est la sous-requête de niveau le plus bas qui est évaluée en premier, puis la requête de niveau immédiatement supérieur,

CAS 1 : sous-interrogation ramenant une valeur  On utilise les opérateurs =, >,

CAS 2 : sous-interrogation ramenant plusieurs valeurs  On utilise les ALL, IN, ANY, SOME.

Remarque : 

une sous-interrogation peut ramener plusieurs colonnes. (on teste l'égalité ou l'inégalité).

Exemple lié au cas 1: 

 Donner la liste des Pilote dont le salaire est supérieur à celui du pilote N°1.

Select plnom, sal from pilote

Where sal > (Select sal From Pilote Where pl# =1);

PLNOM SAL

               ------------             ---------

Armstrong         24500 Tintin           21100 Gagarine   22100

               Baudry                21000

               Bush                    22000

Ruskoi                    22000 Yen                29000

               Concorde             1000.6

8 rows selected.

L'opérateur EXISTS : 

la sous-interrogation ramène VRAI s'il existe au moins une ligne en réponse à la sous-interrogation, FAUX sinon. 

L'interrogation principale s'exécute si VRAI.

Syntaxe :      

               SELECT  

               FROM

               WHERE [NOT] EXISTS (SELECT )

Exemple :

 Donner la liste des Pilotes qui gagnent plus que tous les pilotes habitant Paris. 

       Select plnom, sal, adr From Pilote p1

       WHERE Not Exists(Select From Pilote p2

              Where = 'Paris' and <);

PLNOM              SAL ADR

Armstrong         24500 Wapakoneta

Tintin                 21100 Bruxelles

Gagarine          22100 Klouchino

Bush                  22000 Milton

Ruskoi      22000 Moscou Yen                      29000 Munich 6 rows selected.

Exemple : 

Donner la liste des vols des Pilotes en service qui gagnent plus entre 20000 et 22000.

Select distinct vol#, pilote# From Vol v

Where pilote# in 

(Select p.pl# From Pilote p

                      Where v.# And

                             p.Sal>20000 and p.Sal<22000);

     VOL#   PILOTE#

--------- ---------

      120         4

      130         4

Exemple :

"Quels sont les vols en correspondance (direct ou indirecte) au départ de Paris ?"

Note : NICE ne doit pas être une escale de départ.

SQL>column vol_en_corres format A30;

SQL> Select LPAD(' ',2*(LEVEL),'  ')

       || vol# vol_en_corres, vd, va

    From vol

                       START WITH vd='Paris'

                      CONNECT BY PRIOR va=vd AND vd != 'Nice';

Vol_en_Corres          VD VA

125                           Paris                 Nice

  135                           Paris                 Toulouse

    130                         Toulouse            Beauvais

      290                       Beauvais            Marseille

  150                           Paris                 Nantes

  156                           Paris                 Lyon

    140                         Lyon                  Nice

    153                         Lyon                  Nice

    236                         Lyon                  Toulouse

      130                       Toulouse            Beauvais

        290 Beauvais         Marseille   270                           Paris New york 12 rows selected.

Etapes de traitement

1.   Calcul des enregistrements racines

                                                                                                                      Ce sont les lignes qui satisfont le prédicat de la clause

                                            START WITH

2.   Calcul des lignes filles pour chaque Enregistrement Racine

   Chaque ligne fille doit satisfaire la condition de la clause CONNECT BY. 

3.   Calcul récursif des lignes filles pour chaque ligne fille de l'étape au dessus. Répéter autant que nécessaire la logique de l'étape 2

4.   En cas de présence d'une clause Where, les lignes ne vérifiant pas la  clause ne participe pas à la construction de la hiérarchie

5.   Restitution de l'arborescence

Détection des boucles

ORACLE détecte les boucles éventuelles dans le CONNECT BY. L'ordre est interrompu et un message d'erreur est envoyé à l'utilisateur.

Exemple

Select LPAD(' ',2*(LEVEL),'  ')

       || vol# vol_en_corres, vd, va

    From vol

                       START WITH vd='Paris'

         CONNECT BY PRIOR va=vd; ERROR:

ORA-01436: CONNECT BY loop in user data

La Pseudo Colonne LEVEL

Cette colonne permet de contrôler le Niveau d'une arborescence et d'éviter des boucles.

Exemple

Select LPAD(' ',2*(LEVEL),'  ')

       || vol# vol_en_corres, vd, va

    From vol

                       START WITH vd='Paris'

                      CONNECT BY PRIOR va=vd and Level<3;

VOL_EN_CORRES VD                   VA 125               Paris                Nice

    100                                           Nice Paris

    110                                           Nice Toulouse

    120                                           Nice Paris

    111                                           Nice Paris

    200                                           Nice Toulouse

    210                                           Nice Nantes

    240                                           Nice Paris

    280                                           Nice Mulhouse

  135                                             Paris Toulouse

    130                                           Toulouse Beauvais

…

  17 rows selected.

Contraintes :       

-        la colonne de partitionnement doit figurer dans la clause SELECT. 

-        un    seul GROUP BY est   autorisé   par requête. 

-        pas de GROUP BY dans une sousrequête.

Exemple

Quel est la capacité moyenne des avions par ville et par type.

Select loc, avtype, AVG(cap) "Cap Moyenne"

From avion

Group by loc, avtype;

LOC                  AVTYPE Cap Moyenne

Nice                  A300                300

Nice                  CONCORDE   300

Paris                 A300                350

Paris                 A320                320

Paris                 B707                400

Paris                 CARAVELLE   300

Toulouse B727                250 Toulouse           CONCORDE           350 8 rows selected.

INSERT INTO 

<nom_user.nom_table | nom_user.nom_vue>

              [ (nom_colonnes[,nom_colonnes]) ]

VALUES (valeurs[,valeurs]) | sous_requête ;

Insertion par valeur                  Insertion par requête

Remarque :

si toutes les valeurs des colonnes de la table sont inséréees, il est inutile de préciser les colonnes. Si seules quelques valeurs sont insérées, préciser les colonnes.

Exemples :

SQL> insert into  pilote(pl#,plnom,dnaiss,sal) values(2, ’St-exupéry', '16/10/32', 12300.0);

SQL> insert into avion 

           values(7, 'Mercure', 300, 'Paris', 'En service');       

SQL> insert into vol2             select * from vol

           where vd='Paris';

Comparaison varchar2 / char

*   Comparaison 1:

Oracle ajoute des blancs au char le plus petit pour que les 2 chaînes aient la même longueur.

Oracle compare ensuite car. par car. Dès qu'il y a une différence il conclut lequel est plus grand.

==> utilisée si les 2 valeurs à comparer sont de type

CHAR

*   Comparaison 2 :

Oracle compare car. par car. Dès qu'il y a une différence il conclut lequel est plus grand. Si on a atteint la fin du 1er varchar sans rencontrer de différence avec le deuxième, c'est le plus long (le 2ème) qui est considéré comme le plus grand.

==> utilisée dès qu'une des 2 valeurs est de type varchar2 dans l'expression.

COMP1                                     COMP2                

’ab’ > ’aa’                                 ’ab’ > ’aa’

’ab’ > ’a ’                                 ’ab’ > ’a ’

’ab’ > ’a’                                   ’ab’ > ’a’

’ab’ = ’ab’                                      ’ab’ = ’ab’ 'a ' = 'a'                                          'a ' > 'a'

Généralités

1.    une table est l'unité de base pour le stockage des données dans une base oracle

2.    Deux types de tables subsistent sous Oracle 8. Ce sont les tables dites objets et les tables relationnelles classiques. Nous étudions ici uniquement les tables relationnelles classiques

3.    Une table peut être créée à partir d'une requête

Syntaxe

CREATE TABLE <user>.<nom_table>

        [(

               colonne         typeDeDonnée     [DEFAULT expr]

                                    [contrainteAuNiveauColonne],

              … 

               [contrainteAuNiveauTable]

        )]

| as subquery};

Syntaxe (suite)

Exemple 1 : Création d'une table sans contraintes d'intégrités.

create table pilote(

                pl#  number(4)      ,                  plnom varchar2(12) ,                  dnaiss     date         ,                  adr varchar2(20) ,                  tel    varchar2(12),                   sal number(7,4) 

        );

Exemple 2 : Création dune table à partir d'une requête

Create table Pilote2

       As Select * from Pilote;

La table Pilote2 sera peuplée des lignes de la table Pilote.

Généralités

1.    Les contraintes d'intégrité permettent de vérifier l'intégrité des données au niveau du schéma

2.    La gestion des contraintes au niveau du schéma allège d'autant le code des applicatifs

3.    Les contraintes d'intégrité nous évitent de supprimer malencontreusement les données dépendantes

(intégrité de référence)

4.    Les principaux types de contraintes valables sous Oracle sont :

-     NOT NULL : refus de valeurs nulles pour une colonne

-     UNIQUE : indique que les valeurs d’une ou plusieurs colonnes doivent être unique

-     PRIMARY KEY : identificateur unique d'une ligne c'est aussi la contrainte d'intégrité d'entité

-     FOREIGN KEY : permet de gérer l'intégrité de référence

-     CHECK : permet de gérer l'intégité de domaines

Généralités (suite)

5.    il est conseillé de nommer ses contraintes sinon Oracle affecte un nom par défaut ayant le format suivant SYS_Cn. Exemple SYS_C0001;

6.    les contraintes peuvent être définies au niveau colonne ou au niveau table.

Définition d'une contrainte au niveau Colonne

                                                                                              . une contrainte est définie au niveau colonne

        lorsqu'elle n'implique pas des informations d'autres          colonnes

                                        . Syntaxe

                                                                                                          colonne [CONSTRAINT nomContrainte] 

typeContrainte ;

Définition d'une contrainte au niveau table

        . une contrainte est définie au niveau table lorsqu'elle         implique plus d’une colonne

        . lorsqu'elle est définie après la création d'une table          grâce à la commande Alter Table

                                        . Syntaxe

                                        ,

                                                                                           [CONSTRAINT nomContrainte]  

                                                                       typeContrainte (colonne, … ), …

Note :

                                                                                                       NomContrainte : nom de la contrainte

                                                                                                        TypeContrainte : type de la contrainte

La contrainte NOT NULL

               Cette contrainte empêche les valeurs nulles sur une colonne

Exemple

                                                                                         Plnom varchar2(20) Not Null

                                                                           Sal         number(7,2) nn_pilote_sal Not Null

La contrainte UNIQUE key

1.   Cette contrainte empêche la duplication des valeurs d'une colonne ou d'une ensemble de colonnes.

2.   Les valeurs nulles sont acceptées tant que la contrainte Not Null n'est pas spécifiée.

3.   Un index unique est mis en place implicitement pour contrôler l'unicité

Exemple

 Plnom varchar2(20) nn_pilote_Plnom Not Null  Constraint uk_ pilote_Plnom Unique

La contrainte PRIMARY KEY

1.   Permet de définir un identifiant unique des lignes d'une table

2.   Peut être définie sur une ou plusieurs colonne

3.   Permet de définir un identifiant unique des lignes d'une table

4.   un index unique est créé implicitement par

Oracle pour assurer l'unicité

Exemple

               Pl#    Number(4) Constraint pk_pilote_pl#            primary key,… -- niveau colonne

                                                      Pl#      Number(4),

                                         …

                                                                                                           Constraint pk_pilote_pl# primary key(pl#) 

-- niveau table 

PRIMARY KEY vs UNIQUE key

P.K.

   Unique

     toutes les valeurs sont distinctes      

oui 

                             oui

la colonne est définie en NOT NULL

oui 

  pas oblig.

     définit l'identifiant des lignes           

oui

    précisé une seule fois par table       

oui

     fait le lien avec REFERENCES       

oui

La contrainte FOREIGN KEY

1.    permet de définir une clé étrangère pour assurer l'intégrité de référence

2.    les valeurs de la clé étrangère doivent correspondre à celle de la clé primaire référencée

3.    les mots clés permettant de définir les clés étrangères

sont : FOREIGN KEY, REFERENCES et ON DELETE

CASCADE

4.    le mot clé REFERENCE utilisé permet de définir des contraintes d'intégrité de référence au niveau colonne 

5.    l'option DELETE CASCADE permet de propager

les suppressions ;

6.    le mot clé FOREIGN KEY associé à REFERENCES

permet de définir une contrainte d'intégrité de référence au niveau table ;

7.    une Foreign Key peut être défini sur une ou plusieurs colonnes.

La contrainte FOREIGN KEY (suite)

Exemple

Niveau colonne :

Pilote# number(4) constraint fk_vol_pilote# 

REFERENCES Pilote(pl#),

        …           

Niveau Table :

constraint fk_vol_pilote#  FOREIGN KEY (pilote#)

                                                               REFERENCES Pilote(pl#),

                         …

La contrainte CHECK

1.      permet d'assurer l'intégrité de domaine

2.      permet de définir une condition que doit vérifier chaque ligne

3.      Certaines expressions ne sont pas autorisées par exemple :

-Faire référence aux pseudo colonnes CURRVAL, NEXTVAL, LEVEL et ROWNUM

                                                                                                         -Appeler les fonctions SYSDATE, UID, USER et

USERENV

        - introduire des requêtes qui se réfèrent au valeurs d'autres lignes

Exemple

                                             Niveau colonne :

                       sal          number(7,4) not null  

                                                                                CHECK(sal < 70000.0)

                                           Niveau table :

                                                    ha        number(4),

                                                    hd             number(4)         NOT NULL,

                                        …

                                                                CONSTRAINT ck_ha CHECK(HA > HD),

Exemple général

Définition du schéma de la base de données aérienne.

create table pilote(

                pl# number(4)      primary key,           plnom varchar2(12)      not null unique,              dnaiss     date                 not null,                  adr         varchar2(20)  default ’PARIS’,          tel          varchar2(12),  

                                                     sal      number(7,4) not null  

                                                                          CHECK(sal < 70000.0)

                        );

create table avion(

                av#          number(4)              primary key,          avtype varchar2(10) 

                                                                CONSTRAINT chk_type

                                                                        CHECK(avtype IN (’A300’, ’A310’, 

                                        ’A320’, ’B707’, ’Caravelle’,                                   ’B727’, ’Concorde’),          cap number(4)              not null,

  loc  varchar2(20) not null,   remarq long            );

Exemple général

create table vol(

  vol# number(4) PRIMARY KEY,   pilote# number(4) 

                                                                CONSTRAINT fk_pilote 

                                REFERENCES PILOTE(PL#)                                     ON DELETE CASCADE,

                avion# number(4) NOT NULL,                  vd    varchar2(20),                 va          varchar2(20),                 hd          number(4)      NOT NULL,                   ha    number(4), 

                                       dat    date,

                       CONSTRAINT ck_ha CHECK(HA > HD),

                                                FOREIGN KEY (avion#) 

                REFERENCES AVION(AV#)          );

Exemples

Exemple 1 : index non unique

Create index idx_sal_pilote ON pilote (sal);

Exemple 2 : index unique

Create Unique index idx_plnom_pilote 

ON pilote (plnom);

Exemple 3 : index concaténé

Create index idx_plnom_sal_pilote 

ON pilote (plnom, sal);

La modification d'une table est possible à travers la clause ALTER TABLE.

ALTER TABLE [<nom_user>.] <Table>

La clause ALTER TABLE permet :

1.   d'ajouter de nouvelles colonnes

ALTER TABLE [<nom_user>.] <Table>    ADD <def_col>

Exemple

       Alter table Pilote

              Add (age number(3)) ;

2.   d'ajouter de nouvelles contraintes d'intégrité

ALTER TABLE [<nom_user>.] <Table>

  ADD <table_contrainte> 

Exemple

       Alter table Pilote

                Add chk_pilote_age Check (age Between 25 AND

70);

La clause ALTER TABLE permet (suite):

Note : 

-     NOT NULL est la seule contrainte pouvant être ajoutée par MODIFY. Des valeurs nulles ne doivent pas déjà avoir été stockées

-     La taille d’une colonne ne peut être réduite si des données existent dans la table.

4.    de modifier les paramètres de stockages (voir cours administration)

5.    d'allouer explicitement des extensions dans des

fichiers précis(voir cours d'admnistration)

La clause ALTER TABLE permet (suite):

6.     d'activer/désactiver/supprimer        une contrainte d'intégrité

ALTER TABLE [<nom_user>.] <Table>          ENABLE <clause> | DISABLE <clause> |       DROP <clause> avec <clause> :

                                                                                         UNIQUE (col1[,col2 ])  [CASCADE] | 

                                                    PRIMARY KEY             [CASCADE] |

                                                                                                         CONSTRAINT <nom_contrainte> [CASCADE]

Paramètres et mots clés :

                                                                          ENABLE : active une contrainte

                                                                              DISABLE : désactive une contrainte

                                                                           DROP : supprime une contrainte

                                                                                                          CASCADE: désactive les contraintes dépendantes

La clause ALTER TABLE permet (suite):

6. d'activer/désactiver/supprimer une contrainte d'intégrité

(suite)

Exemple

1.   Désactivation d'une contrainte

Alter Table Pilote Disable 

                      Constraint pk_pilote_pl# cascade ; 

2.   Activation d'une contrainte

Alter Table Pilote enable 

                      Constraint pk_pilote_pl# ; 

3.   Suppression d'une contrainte

Alter Table Pilote Drop

                      Constraint pk_pilote_pl#; 

Restrictions aux modifications des tables 

1. AJOUT

-        on peut ajouter une colonne de type NOT NULL uniquement si la table est vide

-        on peut ajouter une contrainte uniquement au niveau table

2. MODIFICATION

-        on peut retrécir une colonne uniquement si elle est vide

-        on peut passer une colonne de NULL autorisé à NOT NULL uniquement si la colonne ne contient pas de valeur

NULL

-        on ne peut modifier une contrainte

3. SUPPRESSION

- on peut supprimer une contrainte par son nom

Synonyme d'une table

Un synonyme est utilisé pour la sécurité et la facilité de manipulation. 

Syntaxe

CREATE [PUBLIC] SYNONYM 

              [<user>.]<nomSynonyme>

              FOR [<user>.]<nomTable> ;

Paramètres et mots clés

       NomSynonyme : nom du synonyme

NomTable : nom de la table à rebaptiser.

Public : le synonyme est accessible par tous les users.

ATTENTION : son utilisation abusive augmente le temps d'exécution des requêtes.

Notes sur le synonyme:     

-        sert à référencer les objets sans indiquer leur propriétaire

-        sert à référencer les objets sans indiquer leur base

-        fournit un autre nom à un objet : alias

-        un synonyme privé doit avoir un nom distinct dans le schéma d'un utilisateur

-        un synonyme public peut avoir le nom de la table dans son schéma.

Création d'une séquence (suite)

Paramètres et mots clés (suite)

       MAXVALUE n  : spécifie la valeur maximale d'une séquence

 NOMAXVALUE : valeur maximale 10^27 pour une  séquence ascendante et –1 pour une séquence  descendante

MINVALUE n  : spécifie la valeur minimale d'une  séquence

 NOMINVALUE : valeur minimale de 1 pour une  séquence ascendante et -(10^26) pour une séquence  descendante

       CYCLE : continue à générer les valeurs même si le min ou le max sont attteints

NOCYCLE : plus de génération de valeur si min ou  max sont atteints

       CACHE n 

NOCACHE : spécifie le nombre de valeurs à

préallouer

Exemple de création d'une séquence

Create Sequence seq_pilote_pl#

INCREATE BY 1

START WITH 1

MAXVALUE 2000

CACHE 10

NOCYCLE;

NEXTVAL et CURRVAL

 Nextval : rend la prochaine valeur disponible de la séquence

       Currval : rend la valeur courante de la séquence

Utilisation des Séquence

-- insertion dans une table

INSERT INTO Pilote 

Values (seq_pilote_pl#.nextval, 'Bill', … );

-- consultation de la valeur courante

Select seq_pilote_pl#.currval From dual;

Les vues (suite)

-        dba_data_files: informations sur les fichiers de la base de données

-        dba_free_space : espace restant libre dans les fichiers de la base

-        dba_users : informations sur l'ensemble des users de la base

-        dba_tables : informations sur l'ensembles des tables de la base

-        dba_views : texte de toutes les vues de la base

-       

-        user_catalog   : tables, vues, dont l'utilisateur est propriétaire

-        user_free_space : Extent libre dans un tablespace accessible par le user

-        user_indexes : Descriptions des index de l'utilisateur

-        user_tables : tables dont l'utilisateur est propriétaire

Les vues (suite)

-        user_views : textes des vues de l’utilisateur

-        user_users : info sur le user courant

-       

Notes :

USER_* sont des vues donnant des informations sur les objets dont l'utilisateur est propriétaire

ALL_* sont des vues donnant des informations sur les objets auxquels l'utilisateur a accés

DBA_* sont des vues sur tous les objets de la base

-        les vues commençant par dba_ sont accessibles par le

DBA

-        les vues commençant par all_ et user_ sont accessibles le DBA et l'utilisateur.

Les synonymes

-        cat                  : synonyme de la vue user_catalog

-        clu                   : synonyme de la vue user_clusters

-        cols                 : synonyme de la vue user_tab_columns

-        dict                 : synonyme de la vue DICTIONARY

-        ind                   : synonyme de la vue user_indexes

-        seq                 : synonyme de la vue user_sequences - syn                 : synonyme de la vue user_synonyms

-        tab                   : synonyme de la vue user_tables

Les tables dynamiques ou de performances

-        v$process                 : informations sur les processus en cours

-        v$bgprocess     : descriptions des processus d'arrière plan

-        v$licence         : informations sur la validité des licenses

-        v$lock              : informations sur les verrous et les ressources

-        v$parameter     : informations sur les valeurs actuelles des 

                             paramètres              

-        v$session                 : informations sur les sessions courantes - v$transaction         : informations sur les transactions en cours

-       

Note : 

-        ce sont les tables dynamiques de gestion des performances - ces tables commences par un v$.

Contrôle du déroulement d'une transaction

Les clauses de contrôle du déroulement des transactions sont :

               COMMIT [WORK]

              SAVEPOINT savepoint_id               

              ROLLBACK [WORK] [TO savepoint_id]

COMMIT :

-        valide l'ensemble des modifications depuis le début de la transaction

-        libère les verrous

ROLLBACK  :

-        restitue les données à leur valeur de début de transaction

-        libère les verrous

ROLLBACK  TO SAVEPOINT :

1. Pose d'étiquette : SAVEPOINT nom 2 . Annulation partielle : 

       ROLLBACK TO [SAVEPOINT] nom

Note :    - l'utilisation de WORK est facultative

  - le paramètre SAVEPOINT dans fixe le nombre de points de sauvegardes :     "savepoints" (par défaut 5)

Verrous ligne (TX) :

implicitement, un verrou exclusif est posé lors d'un ordre :  

-        INSERT  ou  - UPDATE ou

-        DELETE ou

-        SELECT FOR UPDATE

Les autres utilisateurs ne pourront accéder aux ressources concernées jusqu'à la libération des verrous.

Verrous table (TM) :

Si une transaction pose un verrou ligne exclusif, il est également posé automatiquement un verrou table sur la table concernée.

Un verrou table est posé lors d'un ordre : 

-        INSERT  ou  - UPDATE ou

-        DELETE ou

-        SELECT FOR UPDATE ou

-        LOCK TABLE

Ce verrou table évite la pose de tout verrou exclusif sur la table pour des opérations de DDL (Data Definition

Language)

Ainsi il évite qu'un autre utilisateur puisse modifier ou supprimer la table lors d'une interrogation, modification ou suppression de lignes.

Les différents types de Verrous :

X :           Verrou EXCLUSIF permet aux autres d’interroger

une table mais leur interdit les modifications dans la table et la pose d’un verrou S

S :          Verrou SHARE favorise la lecture dans une table mais interdit les MISES A JOUR (X, SRX, RX)

RS:         Verrou ROW SHARE (SHARE UPDATE) permet

l'accès concurrent à une table. Aide à se prémunir d'un verrou X

RX:        Verrou ROW EXCLUSIVE se comporte comme

RS mais interdit la pose de verrous S, SRX ou X

SRX:     Verrou SHARED ROW EXCLUSIVE. Favorise vos MAJ sans risquer S, SRX, RX ou X       

Commande SQL

Mode de verrouillage

(niveau table)

Actions autorisées

Actions interdites

SELECT

FROM table

aucun

Toutes

On peut poser les verrous suivants :

RS   row share

RX row exclusive

S     share

SRX  share row          exclusive X       exclusive

aucune

-      UPDATE table, - INSERT INTO 

table, 

-      DELETE FROM

table, 

-      LOCK TABLE 

table    IN       row exclusive mode

Row exclusif  (RX)

-  SELECT

-  INSERT 

-  UPDATE

-  DELETE

On peut poser les verrous suivants :

RX, RS

Accès en        mode exclusif  en lecture et écriture: S, SRX, X

avec les ordres : 

LOCK TABLE IN

- share mode - share row  exclusive - exclusive mode

-      SELECT  

FOR UPDATE,

-      LOCK             TABLE table             IN       ROW SHARE MODE

Row Share (RS)

-  SELECT

-  INSERT 

-  UPDATE

-  DELETE

On peut poser les verrous suivants : RX, RS, S, RSX

Accès en mode exclusif en écriture 

 X

avec l'ordre :

LOCK TABLE In exclusive mode

Commande SQL

Mode de verrouillage

(niveau table)

Actions autorisées

Actions interdites

Lock table table in share mode

Share (S)

-   SELECT 

-   SELECT FOR

UPDATE

On peut poser les verrous suivants : 

RS, S

-      INSERT

-      UPDATE

-      DELETE

Verrous :

RX, SRX, X

avec les ordres :

LOCK TABLE IN

-      share row  exclusive mode - exclusive mode

-      row exclusive

mode

lock table table in share row  exclusive mode

Share Row

Exclusif 

(SRX)

-  SELECT

-  SELECT FOR

UPDATE

Verrous autorisés :

RS

-      INSERT

-      UPDATE

-      DELETE

Verrous :

RX, S, SRX, X

avec les ordres :

LOCK TABLE IN

-      share mode - share row  exclusive mode - exclusive mode

-      row exclusive

mode

lock table table in exclusive  mode

Exclusif (X)

- SELECT

Aucun verrou n'est autorisé

Tout sauf les  requêtes.

Commande SQL

Verrous  ligne

Mode de verrouillage de la table

SELECT

--

--

INSERT

oui

RX

UPDATE

oui

RX

DELETE

oui

RX

SELECT  

FOR UPDATE

oui

RS

LOCK table IN 

ROW SHARE MODE

--

RS

LOCK table IN 

ROW EXCLUSIVE 

MODE

--

RX

LOCK table IN 

SHARE EXCLUSIF 

MODE

--

SRX

LOCK table IN 

SHARE MODE

--

S

LOCK table IN 

EXCLUSIVE MODE

--

X

DDL / DCL

--

X

Transaction 1

Temps

Transaction 2

LOCK TABLE pilote

IN ROW SHARE MODE ;

1

2

DROP TABLE pilote ;

?

3

LOCK TABLE pilote

IN EXCLUSIVE MODE 

NOWAIT

?

4

select sal from pilote where = ’Paris’

FOR UPDATE OF sal ;

UPDATE pilote

set sal = 12000.9 where adr = ’Paris’; (attente de T2)

5

6

ROLLBACK ;

(libération des lignes verrouillées par T2)

?

7

LOCK TABLE pilote

IN       ROW        EXCLUSIVE

MODE

8

9

LOCK TABLE pilote

IN EXCLUSIVE MODE 

NOWAIT;

?

10

LOCK TABLE pilote

 IN SHARE ROW EXCLUSIVE 

MODE NOWAIT ;

?

11

LOCK TABLE pilote

 IN SHARE MODE NOWAIT; 

?

12

UPDATE pilote

set sal = 12000.9 where adr = ’Paris’; ?

13

ROLLBACK;

?

SELECT sal

FROM pilote

WHERE adr=’Paris’ FOR UPDATE of sal;

?

14

15

UPDATE pilote

set sal = 12000.9 where adr = ’Paris’; (attente T1)

ROLLBACK;

16

17

?

ROLLBACK;

LOCK TABLE pilote

IN SHARE MODE ;

?

18

19

LOCK TABLE pilote

IN EXCLUSIVE MODE 

NOWAIT ;

?

20

LOCK TABLE pilote

IN SHARE ROW EXCLUSIVE 

MODE NOWAIT ;

?

21

LOCK TABLE pilote

IN SHARE MODE ;

?

22

select sal from pilote where = ’Paris’; ?

23

select sal from pilote where adr= ’Paris’ FOR UPDATE OF sal ;

?

24

UPDATE pilote

set sal = 12000.9 where adr = ’Paris’; (attente T1)

ROLLBACK ;

25

26

X lignes modifiées

(après libération du verrou par T1)

ROLLBACK ;

LOCK TABLE pilote

  IN            SHARE

EXCLUSIVE MODE;

?

ROW

27

28

LOCK TABLE pilote in EXCLUSIVE MODE NOWAIT;

?

29

LOCK TABLE pilote

in SHARE ROW EXCLUSIVE MODE NOWAIT;

?

30

LOCK TABLE pilote in SHARE MODE NOWAIT;

?

31

LOCK TABLE pilote in ROW EXCLUSIVE MODE NOWAIT;

?

32

LOCK TABLE pilote IN ROW SHARE MODE;

?

33

select sal from pilote where adr= 'Paris'; ?

34

select sal from pilote where adr= 'Paris' FOR UPDATE OF sal ;

?

35

UPDATE pilote

set sal = 12000.9 where adr = 'Paris'; (attente T1)

UPDATE pilote

set sal = 12000.9 where adr = 'Paris'; (attente T2)

36

deadlock

?

37

38

?

ROLLBACK;

LOCK TABLE pilote IN EXCLUSIVE MODE;

?

39

40

LOCK TABLE pilote

IN EXCLUSIVE MODE 

NOWAIT;

?

41

LOCK TABLE pilote

IN SHARE ROW EXCLUSIVE

MODE NOWAIT;

?

42

LOCK TABLE pilote IN SHARE MODE NOWAIT;

?

43

LOCK TABLE pilote

IN ROW EXCLUSIVE MODE 

NOWAIT;

?

44

LOCK TABLE pilote

IN ROW SHARE MODE 

NOWAIT;

?

45

select sal from pilote where adr= ’Paris’ ?

46

select sal from pilote where adr= ’Paris’ FOR UPDATE OF sal ;

?

UPDATE pilote

set sal = 12000.9 where adr = ’Paris’; ?

47

COMMIT;

48

49

?

Syntaxe

CREATE [OR REPLACE] 

       [FORCE | NOFORCE] VIEW nom_de_vue 

               [(alias_colonne1, alias_colonne2, )]

                      AS subquery

WITH CHECK OPTION [CONSTRAINT constraint] ;

Mots clés et paramètres

OR REPLACE : permet de supprimer puis de recréer 

la vue si elle existe

FORCE : ignore les erreurs et crée la vue

WITH CHECK OPTION : permet d'assurer la 

cohérence des informations modifiées afin de laisser  dans la vue les lignes affectées par une modification

CONSTRAINT constraint : permet juste de nommer

 la contrainte de la vue

SQL :

est un langage ensembliste et non procédural

PL/SQL : 

est un langage procédural qui intègre des ordres SQL de gestion de la base de données

Instructions SQL intégrées dans PL/SQL : 

-        SELECT

-        INSERT, UPDATE, DELETE            - COMMIT, ROLLBACK, SAVEPOINT                - TO_CHAR, TO_DATE, UPPER,

Instructions spécifiques à PL/SQL : 

-        définition de variables

-        traitements conditionnels

-        traitements répétitifs

-        traitement des curseurs

-        traitement des erreurs

Un bloc PL/SQL est divisé en 3 sections : 

DECLARE 

        Déclaration de variables, constantes,           exceptions, curseurs

BEGIN [nom_du_bloc]

       Instructions SQL et PL/SQL

EXCEPTION

       Traitement des exceptions

        (gestion des erreurs)

END [nom_du_bloc] ; 

Remarques : 

- les sections DECLARE et EXCEPTION sont facultatives. - chaque instruction se termine par un ; - Les commentaires :

                      -- sur une ligne

                             ou

   /* sur plusieurs        lignes */

* Variable de même type qu'un attribut d'une table de la base

Syntaxe :

              nom_var       table.colonne%TYPE;

Exemple :

               DECLARE

                        nom         pilote.plnom%TYPE;            BEGIN                

               END;

- Variable de même structure qu'une ligne d'une table de la base

Syntaxe :

              nom_var       table%ROWTYPE;

Exemple :

               DECLARE

                        ligne       pilote%ROWTYPE;          BEGIN                 

               END;

Remarque :

La structure ligne contient autant de variables que de colonnes de la table. Ces variables portent le même nom et sont de même type que les colonnes de la table.

Pour y accéder : 

        ligne.<nom_col1>         ligne.<nom_col2>

       ligne.<nom_coln>

Avec :

       opérateur :=  

              ou

        SELECT INTO

Exemple :  

        DECLARE 

                var1         VARCHAR2(10) := 'DUPONT';                  var2         NUMBER(5,2) := 100;

                var3        VARCHAR2(10);                  var4         DATE;

        BEGIN

               SELECT col1, col2

              INTO var3, var4

               FROM ;

        END;

Remarque : 

le SELECT doit ramener une et une seule ligne, sinon erreur.

Une variable est visible dans le bloc où elle a été déclarée et dans les blocs imbriqués si elle n'a pas été redéfinie.

DECLARE 

        var1         NUMBER(3);            var2         VARCHAR2(10);

BEGIN

                                                var1       NUMBER(3)

                                                var2       VARCHAR2(10)

        DECLARE

                var1         VARCHAR2(10);                  var3         DATE;

        BEGIN

                                                var1       VARCHAR2(10)

                                                var2        

                                                var3

        END;

        DECLARE

               var4       NUMBER(5,2);

        BEGIN

                                                var1 NUMBER(3)

                                                var2

                                                var4

        END;

                                                var1       NUMBER(3)

                                                var2       VARCHAR2(10)

END;

Exécution d'un traitement plusieurs fois, le nombre n'étant pas connu mais dépendant d'une condition.

BEGIN           LOOP [label]              instructions;  END LOOP [label];

END;

Pour sortir de la boucle, utiliser la clause : 

       EXIT [lable] WHEN condition

Exemple : insérer les 10 premiers chiffres dans la table result

        DECLARE

               nb  NUMBER := 1;

        BEGIN

          LOOP

                INSERT INTO result                  VALUES (nb);              nb := nb + 1;                   EXIT WHEN nb > 10;

          END LOOP;

        END ; 

Déclaration du curseur

déclaration dans la section DECLARE du bloc.

on indique le nom du curseur et l'ordre SQL associé

Syntaxe : 

       CURSOR nom_curseur IS ordre_select ;

Exemple : 

        DECLARE

                      CURSOR pl_nice IS

                             SELECT  pl#, plnom

                             FROM      pilote

                                WHERE adr=’Nice’;             BEGIN                         

                 END ; 

Ouverture du curseur

L'ouverture du curseur lance l'exécution de l'odre SELECT associé au curseur.

Ouverture dans la section BEGIN du bloc.

Syntaxe : 

       OPEN nom_curseur ; 

Exemple : 

        DECLARE

                      CURSOR pl_nice IS

                             SELECT  pl#, plnom

                             FROM      pilote

                             WHERE   adr='Nice';

                  BEGIN                         

                      OPEN pl_nice;

                 END ; 

Traitement des lignes

Après l'exécution du SELECT les lignes ramenées sont traitées une par une, la valeur de chaque colonne du SELECT doit être stockée dans une variable réceptrice. Syntaxe : 

       FETCH nom_curseur INTO liste_variables ;

Exemple : 

        DECLARE

                      CURSOR pl_nice IS

                             SELECT  pl#, plnom, sal

                                FROM      pilote                             WHERE adr='Nice';                    num           #%TYPE;                      nom          pilote.plnom%TYPE;                          salaire %TYPE;

                 BEGIN

                      OPEN pl_nice;

                       LOOP 

                                FETCH pl_nice INTO num,                                               nom,salaire;                                

                             EXIT WHEN sal > 10 000;

                       END LOOP;

                 END ; 

Fermeture du curseur 

Pour libérer la mémoire prise par le curseur, il faut le fermer dès qu'on n'en a plus besoin.

Syntaxe : 

                                                           CLOSE nom_curseur ;

Exemple : 

                                  DECLARE

                                                        CURSOR pl_nice IS

                                                                SELECT  pl#, plnom, sal

                                FROM      pilote                             WHERE adr='Nice';                   num            #%TYPE;                      nom           pilote.plnom%TYPE;

                                                        salaire %TYPE;

                                  BEGIN

                                                OPEN pl_nice;

                                        LOOP 

                                                                                FETCH pl_nice INTO num, nom,salaire;

                                                                EXIT WHEN sal > 10 000;

                                                END LOOP;

                                                 CLOSE pl_nice;

                                  END ; 

Exemple : 

               DECLARE

                      CURSOR pl_nice IS

                             SELECT  pl#, plnom, sal

                                FROM      pilote                             WHERE adr=’Nice’;                           num            #%TYPE;                      nom           pilote.plnom%TYPE;

                        salaire   %TYPE;

                 BEGIN

                      OPEN pl_nice;

                      FETCH pl_nice INTO num, nom,salaire;

                        WHILE pl_nice%FOUNDLOOP                             

                                FETCH pl_nice INTO num,                                               nom,salaire;

                       END LOOP;

                      CLOSE pl_nice;

                 END ; 

curseur implicite : SQL%ISOPEN

toujours à FALSE car ORACLE referme les curseurs après utilisation.

curseur explicite : nom_curseur%ISOPEN

TRUE  si le curseur est ouvert.

Exemple : 

        DECLARE

                      CURSOR pl_nice IS

                             SELECT  pl#, plnom, sal

                                FROM      pilote                             WHERE adr='Nice';                   num            #%TYPE;                      nom           pilote.plnom%TYPE;

                        salaire   %TYPE;

                 BEGIN

                       IF NOT(pl_nice%ISOPEN) 

                       THEN 

                             OPEN pl_nice;

                        END IF;                                 

                 END ; 

curseur implicite : SQL%ROWCOUNT

nombre de lignes traitées par INSERT, UPDATE, DELETE

        0 : SELECT INTO : ne ramène aucune ligne          1 : SELECT INTO : ramène 1 ligne

       2 : SELECT INTO : ramène plus d'une ligne

curseur explicite : nom_curseur%ROWCOUNT

traduit la nième ligne ramenée par le FETCH

Syntaxe erreur ORACLE non prédéfinie : 

DECLARE

  nom_erreur EXCEPTION;  on donne un nom à l'erreur   PRAGMA EXCEPTION_INIT(nom_erreur,code_erreur)                  on associe le nom de l'erreur à un code erreur

BEGIN

                   l'erreur Oracle est détectée par le système

EXCEPTION

                                                                                                           WHEN nom_erreur THEN traitement de l'erreur

END;

Remarque : 

on sort du bloc après le traitement de l'erreur.

Syntaxe erreur ORACLE prédéfinie : 

Certaines erreurs ORACLE ont déjà un nom. Il est donc inutile de les déclarer comme précédemment. On utilise leur nom dans la section EXCEPTION.

DECLARE

BEGIN

                   l'erreur Oracle est détectée par le système

EXCEPTION

                                                                                                           WHEN nom_erreur THEN traitement de l'erreur

END;

Exemple d'erreurs prédéfinies :

-    DUP_VAL_ON_INDEX

-    NO_DATA_FOUND

-   

-    OTHERS

L             Liste le contenu du buffer

L*          Liste la ligne courante

Ln          Liste la nième ligne qui devient courante

Lm n      Liste de la ligne m à la ligne n

i             Insertion après la ligne courante

i texte      Insertion d'une ligne dont le contenu est texte a              Ajoute du texte en fin de ligne courante

del         Supprime la ligne courante

C/ch1/ch2       Remplace la première occurrence de la                       chaîne 1 par la chaîne 2 dans la ligne courante

C/ch              Supprime la chaîne dans la ligne courante

clear buffer  Efface le buffer 

Pour : 

Ö  stocker des requêtes dans un fichier 

Ö  récupérer des requêtes se trouvant dans un fichier Ö stocker des résultats 

Les commandes d’E/S : 

ed nom_file         ouvre un fichier sous 

                             l'éditeur associé

save nom_file      sauve le contenu du buffer dans 

get nom_file        charge le fichier dans le 

                              buffer 

start nom_file ou @nom_file

                                ouvre le fichier , le charge                                   dans le buffer et exécute les commandes.

spool nom_file  ouvre un fichier d'impression (.lis ou .lst)

                                qui contiendra la trace écran spool off                 ferme le fichier d'impression spool out                id. plus envoi à l'imprimante

Exemple de requête paramétrée : 

SQL> SELECT * 

                                        FROM avion

                                                        WHERE av# = &num_avion

                                /

ENTER VALUE FOR num_avion : <saisie du user>

-- exécution de la requête avec la valeur saisie -- 

Remarque : 

&var : la variable est définie pour une exécution

&&var : la variable est définie pour la session

Complément : 

L'utilisateur pourra donner les valeurs lors de l'appel du script contenant les commandes SQL. Pour cela les variables devront s'appeler &1, &2, &9.

Exemple : fichier  

                        Select * from &1 where av# = &2; lancement : 

                                                         SQL> @test avion 20

     DEF                      

renvoie les valeurs de toutes les  variables définies

DEF var = valeur 

affecte une valeur à la variable de  façon permanente

     UNDEF var          

supprime la définition de la variable

Les commandes de dialogue 

Rappel : 

SQL> SELECT * 

                                             FROM avion

                                                      WHERE av# = &num_avion

                                   /

ENTER VALUE FOR num_avion : <saisie du user>

Les commandes de dialogue vont permettre de donner la main à l'utilisateur de façon plus conviviale.

prompt texte               affiche le message à l'utilisateur

accept var [PROMPT texte]

                                        attend que l'utilisateur rentre une                                           une valeur pour var

Exemple :

                                                                                       PROMPT entrez le numéro de l'avion

                                                                                           ACCEPT num_avion PROMPT 'Num : '

                                        SELECT * 

                                           FROM avion

                                                                         WHERE av# = &num_avion; 

Ö COL(umn)   nom_col

        [FORMAT format]       def. la taille et le format                   ex : A10 (alpha), 99990 (num.)

                                                                  [HEADING texte]      en_tête de la col.

                                                                        [JUSTIFY L|C|R]        cadre l'en-tête de la col.

        [NEW_VALUE var] met la nvelle val. dans var          [OLD_VALUE var] met l'ancienne val. dans var          [PRINT |NOPRINT] affiche ou pas la col.

                                             [NEWLINE]               passage à la ligne

Exemple : COL pilnom FORMAT A16 

                                                                        HEADING NOM DU | PILOTE

Remarques : 

COL renvoie toutes les informations de toutes les colonnes.

COL nom_col  id. pour une colonne

COL nom_col CLEAR supprime le «formatage» de la col. CLEAR COL Id. pour toutes les colonnes.

Ö TTITLE texte  

texte est le titre qui apparaîtra à chaque page du rapport

Exemple : TTITLE « AVIONS DE LA COMPAGNIE »

|  : pour un saut à la ligne 

-    : suite ligne suivante 

Remarque : TTITLE sans option génère : 

-    la date en haut à gauche

-    le n° de page en haut à droite

-    le titre centré

(nom valable pour TTILE avec options)

TTITLE [OPTIONS [texte|var]]   |     [ON | OFF]

Options : LEFT, CENTER, RIGHT, BOLD,

Variables : 

: n° de ligne

: n° de page

SQL.RELEASE : version d'ORACLE

: nom utilisateur 

Ö BTITLE texte  

Id. à TTILE mais en titre de bas de page

TTITLE : renvoie les informations sur le titre haut de page

BTITLE : renvoie les informations sur le titre bas de page

TTITLE ON |OFF : active ou désactive l'emploi d'un titre 

                                                                haut de page

BTITLE ON |OFF : active ou désactive l'emploi d'un titre 

                                                        bas de page

Ö BREAK ON  critere_de rupture  action

définit une rupture et déclenche une action chaque fois qu'elle se produit

avec : 

critere_de_rupture  : nom_col | ROW | REPORT

action                          : SKIP (n|PAGE) DUP|NODUP

exemple : BREAK ON PL# SKIP 2

==> saute deux lignes à chaque changement de pilote.

Remarque : bien entendu les données ramenées par le SELECT doivent être en cohérence avec le BREAK.

BREAK : donne les informations sur les ruptures définies

CLEAR BREAK : annule la rupture définie

Ö COMPUTE {AVG | COUNT | MAX | MIN |

NUMBER | STD | SUM | VAR }

OF col1 {col2 }

ON {col_break | ROW | REPORT }

Exemple :

COMPUTE SUM 

OF sal 

ON adr REPORT 

REMARQUE IMPORTANTE : 

définir le COMPUTE en cohérence avec le BREAK 

COMPUTE renvoie les informations sur le calcul

CLEAR COMPUTE annule les calculs définis

Exemple : 

BREAK ON avtype SKIP 1 ON REPORT  COMPUTE SUM OF cap ON avtype REPORT  select * from avion;

(somme des cap. par avtype puis pour tous les enregts.)

Ö impression

SET LINE  

n      

nb de car. / ligne

SET PAGES 

n      

nb lignes / page

SET NUM 

n      

largeur par def. des nb

SET NEWP 

n      

nb de lignes à sauter en haut de  chaque page

SET NULL 

ch 

valeur de la chaîne NULL

SET SPACE 

n      

nb expaces entre les colonnes

     SET UND     

car 

caractère de soulignement  des en-têtes de colonnes

Ö affichage, exécution

SET TERM         ON|OFF        affichage O/N

SET ECHO         ON|OFF        réaffichage de la cde SQL

SET FEED          ON|OFF        nb lignes ramenées

SET SHOW        ON|OFF        affichage des paramètres

                                                                        sql*plus

SET HEA            ON|OFF        affichage du titre des col.

SET PAUSE       ON|OFF |TEXTE        arrêt en fin de page 

                                                                                        (message)

SET SQLN          ON|OFF       numérotation des lignes du 

                                                                        buffer

SET SQLP           texte              prompt de SQL*Plus

SET TIME  

ON|OFF 

affichage de l'heure à  gauche du prompt 

SET VERIFY

ON|OFF

affichage des cdes avant et après substitution

SET TIMING

ON|OFF

information sur les temps  d'exécution

Ö divers 

SET       DEFINE       car 

                                                                                            caractère de substitution pour les paramètres 

                                            ( & par défaut) 

SET SCAN ON|OFF

                                                                                         Autorisation d'utilisation des paramètres 

SET SQLTERMINATOR        car

                                        Défaut : ;

SET HEADS       car|ON|OFF

                                       Défaut : |

SET SUFFIX      chaîne           extension des fichiers

SET AUTOCOMMIT ON|OFF

                                                                                             pour mettre en place un commit automatique