Fonctions de remplacement et conversions en SQL Oracle

4

.

F

K

B

O

U

B

I

.

F

K

B

O

U

B

I

16

(cid:2) Syntaxe : NULLIF(expr1,expr2)

(cid:2) Fonction : Afficher NULL si expr1=expr2, sinon afficher

(cid:2) Afficher le nom, le prénom et l’adresse de tous les clients.

Si la colonne adresse est null afficher ‘Adresse Inconnue’

expr1

(cid:2) Exemple

19

.

F

K

B

O

U

B

I

17

.

F

K

B

O

U

B

I

Select nom, prenom,nvl(adresse,'Adresse Inconnue')

from client;

(cid:2) Afficher le nom, le prénom et l’adresse de tous les clients.

Si la colonne adresse est null afficher ‘Adresse Inconnue’

Sinon préfixer l’adresse par le commentaire ‘Habite à : ‘

select nom, prenom,nvl2(adresse,'Habite à : '||adresse,

'Adresse Inconnue')

from client;

CASE expr WHEN comparision_expr1 THEN return_expr1

[WHEN comparision_expr2 THEN return_expr2

WHEN comparision_expr3 THEN return_expr3

ELSE else_expr]

END

.

F

K

B

O

U

B

I

18

.

F

K

B

O

U

B

I

20

08/04/2015

5

DECODE (column_name | expr, search1, result1

[search2, result2,]

[search3, result3,]

[…, …]

[résultat par défaut])

(cid:2) Les opérateurs ensemblistes combinent les résultats

d’une ou de plusieurs requêtes dans un seul résultat.

(cid:2) Une requête qui inclut un ou plusieurs opérateurs

ensemblistes est dite requête composée.

(cid:1) UNION

(cid:1) UNION ALL

(cid:1) INTERSECT

(cid:1) MINUS

.

F

K

B

O

U

B

I

21

.

F

K

B

O

U

B

I

(cid:2) Une requête SQL composée est exécutée du haut vers le

bas, il n’y a pas d’opérateur ensembliste prioritaire à un

autre (sauf s’il y a des parenthèses qui définissent un

ordre de priorité).

23

08/04/2015

.

F

K

B

O

U

B

I

6

22

(cid:2) Supposons que l’on dispose de la table JOB_HISTORY

où nous stockons les changements de jobs effectués

pendant la période de service de chaque employé.

(cid:2) Les colonnes de cette table sont :

(cid:1) EMPNO,

(cid:1) START_DATE qui est la date de changement de job,

(cid:1) END_DATE qui est la date de fin d’un job,

(cid:1) JOB qui est le poste qu’occupe l’employé de code EMPNO

entre les dates START_DATE et END_DATE,

(cid:1) et finalement DEPTNO.

.

F

K

B

O

U

B

I

24

(cid:2) Retourne toutes les lignes distinctes retournées par les

deux requêtes concernées.

(cid:2) Exemple

(cid:1) Afficher pour chaque employé les postes qu’il a occupés depuis

sa mise en service dans l’entreprise ?

SELECT EMPNO, JOB FROM EMP

UNION

SELECT EMPNO, JOB FROM JOB_HISTORY ;

.

F

K

B

O

U

B

I

(cid:1) Si jamais un employé a occupé le même poste dans deux

périodes différentes alors cette requête affiche une seule fois le

même couple (EMPNO, JOB).

(cid:1) Si on veut afficher les occurrences, on utilise l’opérateur UNION

ALL .

25

(cid:2) Retourne les lignes distinctes qui existent dans le résultat

de la première requête mais qui n’existent pas dans le

résultat de la deuxième.

(cid:2) Exemple

(cid:1) Afficher les employés qui n’ont jamais changé de job ?

SELECT EMPNO FROM EMP

MINUS

SELECT EMPNO FROM JOB_HISTORY ;

(cid:3) Remarque

(cid:1) Cette commande s’appelle différemment selon les SGBD :

(cid:1) EXCEPT : PostgreSQL

(cid:1) MINUS : MySQL et Oracle

.

F

K

B

O

U

B

I

27

08/04/2015

(cid:2) Retourne les lignes distinctes communes aux deux

requêtes concernées.

(cid:2) Exemple

(cid:1) Afficher les employés ayant occupé le poste actuel plus d’une

fois ?

.

F

K

B

O

U

B

I

SELECT EMPNO, JOB FROM EMP

INTERSECT

SELECT EMPNO, JOB FROM JOB_HISTORY ;

26

(cid:2) Le nombre de colonnes et leurs types de données doivent

être les mêmes pour chaque requête.

(cid:2) Les parenthèses peuvent être utilisées pour forcer la

priorité d’un opérateur.

(cid:2) A l’exception de l’opérateur UNION ALL, le résultat est trié

selon la première colonne en ordre croissant par défaut.

(cid:2) La clause ORDER BY peut apparaître une seule fois à la

.

F

K

B

O

U

B

I

fin de la requête composée. Elle inclut les noms de

colonnes (de la première requête), leurs alias ou leurs

positions dans la clause SELECT.

28

7

(cid:2) Quels sont les utilisateurs qui ont créé des tables ou des vues ?

SQL> select distinct(owner) from dba_tables

2

3

UNION

select distinct(owner) from dba_views;

.

F

K

B

O

U

B

I

(cid:2) Quels sont les utilisateurs qui ont créé des tables et des vues?

SQL> select distinct(owner) from dba_tables

2

3

INTERSECT

select distinct(owner) from dba_views;

(cid:2) Quels sont les utilisateurs qui n’ont créé aucune table?

30

SQL> select username from dba_users

2

3

minus

select distinct(owner) from dba_tables;

29

(cid:2) Définitions

(cid:1) Une vue est une table virtuelle

(cid:1) Elle n'existe pas dans la base

(cid:1) Elle est construite à partir du résultat d'un SELECT.

(cid:1) La vue sera vu par l'utilisateur comme une table réelle.

.

F

K

B

O

U

B

I

(cid:2) Les vues permettent

(cid:1) Des accès simplifiés aux données

(cid:1) L'indépendance des données

(cid:1) La confidentialité des données

(cid:3) Restreint les droits d'accès à certaines colonnes ou à certains n-uplets.

Image d'usager d'un SGBD relationnel

SQL

V4

Virtuel

V1

V2

Réel

T1

T2

T3

T4

31

Physique

F1

F2

F3

08/04/2015

8

.

F

K

B

O

U

B

I

V3

.

F

K

B

O

U

B

I

32

(cid:2) Création d'une vue : CREATE VIEW

(cid:2) Syntaxe

emp (id_emp, Ename, salaire, status, deptno)

dept (DEPTNO, Dname, Loc, dirigeant)

08/04/2015

CREATE VIEW nom [(col, …)]AS

SELECT col1, col2, …

FROM tab

WHERE prédicat

[WITH CHECK OPTION]

.

F

K

B

O

U

B

I

(cid:2) La spécification des noms de colonnes de la vue est facultative

(cid:2) Par défaut, les colonnes de la vue ont pour nom les noms des

colonnes résultat de SELECT

(cid:2) Pour récupérer les données de vues, on procédera

comme si l'on était en face d'une table classique

SELECT * FROM Employes2 …

(cid:2) En réalité, cette table est virtuelle et est reconstruite à

chaque appel de la vue EMPLOYES2 par exécution du

SELECT constituant la définition de la vue

33

.

F

K

B

O

U

B

I

35

(cid:2) Créer une vue comportant le nom des employés, le statut,

.

F

K

B

O

U

B

I

le nom du service et le lieu de travail.

CREATE VIEW Employes2

AS SELECT Ename, Dname, Loc, Status

FROM emp, dept

WHERE emp.DEPTNO = dept.DEPTNO

AND STATUS >= 15;

(cid:2) Les options que l’on peut associer à une vue lors de sa

création :

(cid:1) WITH CHECK OPTION Respecte les conditions de la vue en

mise à jour

(cid:1) WITH READ ONLY N’autorise que la lecture

(cid:2) Suppression d'une vue

DROP VIEW nom-vue

(cid:2) Renommer une vue

RENAME ancien-nom-vue TO nouveau-nom-vue

34

.

F

K

B

O

U

B

I

36

9

(cid:2) WITH CHECK OPTION protège contre les disparitions de tuples de

(cid:2) WITH CHECK OPTION protège contre les disparitions de tuples de

vues:

(cid:2) UPDATE Employe2

SET STATUS = '5' WHERE Dname = 'S1' ;

serait alors rejeté.

S1...15

S2...20

S5...17

S7...5

Abort

S1...15

S2...20

S5...17

S7...5

Avec

CHECK OPTION

vues:

CREATE VIEW Employes2

AS SELECT Ename, Dname, Loc, Status

FROM emp, dept

WHERE emp.DEPTNO = dept.DEPTNO

AND STATUS >= 15;

(cid:2) UPDATE Employe2

SET STATUS = '5' WHERE Dname = 'S1' ;

serait alors rejeté.

S1...15

S2...20

S5...17

S7...5

S1...5

S2...20

S5...17

S7...5

Sans

CHECK OPTION

39

.

F

K

B

O

U

B

I

37

.

F

K

B

O

U

B

I

08/04/2015

10

.

F

K

B

O

U

B

I

38

.

F

K

B

O

U

B

I

40

08/04/2015

SQL> CREATE VIEW Mes_Avions

2 AS select v.id_avion, nom_avion, destination

3 from avion a, vol v

4 where v.id_avion = a.id_avion;

Vue crÚÚe.

L’accès aux éléments d’une vue est le même que pour ceux d’une table ...

SQL> select * from mes_avions;

ID_AVION

NOM_AVION

DESTINATION

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

1

1

2

Caravelle

Caravelle

Boeing

Tahiti

Marquises

Tokyo

.

F

K

B

O

U

B

I

42

(cid:2) Du point de vue fonctionnel, les vues supportent toutes les

opérations SQL comme INSERT, UPDATE, DELETE,

SELECT.

(cid:2) Mais il existe des contraintes importantes

.

F

K

B

O

U

B

I

44

11

.

F

K

B

O

U

B

I

41

.

F

K

B

O

U

B

I

43

La vue du USER_VIEWS du dictionnaire de données permet d’afficher le

contenu de la vue.

SQL> select view_name, text

2 from user_views;

TEXT

VIEW_NAME

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

MES_AVIONS

select v.id_avion, nom_avion, destination

from avion a, vol v

08/04/2015

SQL> insert into mes_avions

2 values (11,'Coucou','Perou');

SQL> insert into mes_avions

2 values (11,'Coucou','Perou');

insert into mes_avions

*

ERREUR Ó la ligne 1 :

ORA-01776: Impossible de modifier plus d'une table de base via

une vue jointe

.

F

K

B

O

U

B

I

insert into mes_avions

*

ERREUR Ó la ligne 1 :

ORA-01776: Impossible de modifier plus d'une table de base via

une vue jointe

.

F

K

B

O

U

B

I

SQL> insert into mes_avions

2 (destination)

3 values ('Perou');

SQL> insert into mes_avions

2 (destination)

3 values ('Perou');

insert into mes_avions

*

ERREUR Ó la ligne 1 :

ORA-01400: impossible d'insÚrer NULL dans ("VOL"."NO_VOL")

insert into mes_avions

*

ERREUR Ó la ligne 1 :

ORA-01400: impossible d'insÚrer NULL dans ("VOL"."NO_VOL")

SQL> create view Mes_Vols

2 as select no_vol, vol_depart, destination

3 from vol

4 where vol_depart > to_date('10/09/2004', 'DD/MM/YYYY')

5 with check option ;

Vue crÚÚe.

SQL> select * from mes_vols;

NO_VOL

VOL_DEPA

DESTINATION

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

3

30/09/04

Tokyo

45

.

F

K

B

O

U

B

I

L’insertion d’un nouveau vol à travers la vue est contrôlée ...

SQL> insert into mes_vols

2 values (11,to_date('12/09/2004 20:30:00','DD/MM/YYYY HH24:MI:SS'), 'Perou');

insert into mes_vols

*

ERREUR Ó la ligne 1 :

ORA-01400: impossible d'insÚrer NULL dans ("VOL"."ID_AVION")

46

.

F

K

B

O

U

B

I

Recréer la vue Mes_Vols en y ajoutant la colonne ID_AVION de la table VOL

SQL> Drop view mes_vols;

Vue supprimÚe.

SQL> create view Mes_Vols

2 as select no_vol, vol_depart, destination, id_avion

3 from vol

4 where vol_depart > to_date('10/09/2004', 'DD/MM/YYYY')

5 with check option ;

Vue crÚÚe.

SQL> insert into mes_vols

2 values (11,to_date('12/09/2004 20:30:00', 'DD/MM/YYYY HH24:MI:SS'),

3 'Perou', 3);

1 ligne crÚÚe.

VOL_DEPA

SQL> select * from mes_vols;

NO_VOL

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

3

11

30/09/04

12/09/04

Tokyo

Perou

DESTINATION

ID_AVION

2

3

47

48

12

Recréer la vue Mes_Vols en y ajoutant la colonne ID_AVION de la table VOL

SQL> Drop view mes_vols;

Vue supprimÚe.

SQL> create view Mes_Vols

2 as select no_vol, vol_depart, destination, id_avion

3 from vol

4 where vol_depart > to_date('10/09/2004', 'DD/MM/YYYY')

5 with check option ;

Vue crÚÚe.

.

F

K

B

O

U

B

I

SQL> insert into mes_vols

2 values (11,to_date('08/09/2004 20:30:00','DD/MM/YYYY HH24:MI:SS'),

3 'Marquises', 1);

insert into mes_vols

*

ERREUR Ó la ligne 1 :

ORA-01402: vue WITH CHECK OPTION - violation de clause WHERE

49

(cid:2) Une séquence est un objet créé par l’utilisateur

(cid:2) Elle sert à créer des valeurs pour les clés primaires, qui

sont incrémentées ou décrémentées par le serveur Oracle

.

F

K

B

O

U

B

I

(cid:2) La séquence est stockée et générée indépendamment de

la table, et une séquence peut être utilisée pour plusieurs

tables

08/04/2015

.

F

K

B

O

U

B

I

.

F

K

B

O

U

B

I

50

CREATE SEQUENCE nomsequence

[INCREMENT BY entier ]

[START WITH entier ]

[MAXVALUE entier | nomaxvalue]

[MINVALUE entier | nominvalue ]

[CYCLE | NOCYCLE]

[CACHE entier | NOCACHE]

[order | noorder] ;

Cache : Spécifie combien de valeurs de la séquence de la base de

données peuvent être pré-allouées et mémorisées dans la mémoire

pour avoir un accès plus rapide. La valeur minimale pour ce

paramètre est 2. Pour les séquences cycliques, cette valeur doit être

inférieur que le nombre de valeurs dans le cycle.

51

52

13

SQL>

CREATE SEQUENCE dept_deptno_seq

INCREMENT BY 10

START WITH 10

NOCYCLE

NOCACHE;

SQL>

SELECT dept_deptno_seq.NEXTVAL FROM

dual;

NEXTVAL

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

10

SQL>

SELECT dept_deptno_seq.CURRVAL FROM

dual;

CURRVAL

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

10

SQL>

INSERT INTO dept (deptno, dname, loc)

VALUES

(dept_deptno_seq.NEXTVAL, ‘SUPPORT’,’NY’);

MODIFICATION

MODIFICATION

ALTER SEQUENCE nomsequence

[INCREMENT BY entier]

[MAXVALUE entier | NOMAXVALUE]

[MINVALUE entier | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE entier | NOCACHE];

[order | noorder]

SUPPRESSION

SUPPRESSION

DROP SEQUENCE nomsequence ;

SQL> ALTER SEQUENCE ma_sequence INCREMENT BY 20;

Séquence modifiée.

SQL> SELECT ma_sequence.nextval FROM dual;

NEXTVAL

---------

23

.

F

K

B

O

U

B

I

53

.

F

K

B

O

U

B

I

55

08/04/2015

.

F

K

B

O

U

B

I

SQL> CREATE SEQUENCE ma_sequence

START WITH 1

MINVALUE 0;

Séquence créée.

SQL> SELECT ma_sequence.nextval FROM dual;

NEXTVAL

---------

1

SQL> SELECT ma_sequence.nextval FROM dual;

NEXTVAL

---------

2

SQL> SELECT ma_sequence.nextval FROM dual;

NEXTVAL

---------

3

SQL> SELECT 'La valeur courante est ' || ma_sequence.currval FROM dual;

'LA VALEUR COURANTE EST'||MA_SEQUENCE.CURRVAL

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

La valeur courante est 3

54

(cid:2) Un index est un objet qui peut augmenter la vitesse de

récupération des lignes en utilisant les pointeurs

(cid:2) Les index peuvent être créés automatiquement par le

serveur Oracle ou manuellement par l’utilisateur

(cid:2) Ils sont indépendants, donc lorsque vous supprimez ou

modifiez un index les tables ne sont pas affectées

.

F

K

B

O

U

B

I

56

14

Syntaxe

CREATE INDEX nomINDEX

ON

table(column1, column2);

Exemple

.

F

K

B

O

U

B

I

57

(cid:2) Un synonyme est un nom alternatif pour désigner un objet

de la base de données.

(cid:2) C'est aussi un objet de la base de données

CREATE [PUBLIC] SYNONYM nomSYNONYM

FOR

object;

PUBLIC: spécifie que le nom peut être accéder par tous

les utilisateurs

object : le nom de l’objet pour lequel le synonyme est créé

.

F

K

B

O

U

B

I

59

08/04/2015

(cid:2) Quand créer un index

(cid:1) La colonne contient une large plage de valeurs

(cid:1) La colonne contient plusieurs valeurs nulles

(cid:1) Une ou plusieurs colonnes sont fréquemment utilisées dans la

clause WHERE ou pour les conditions de jointure

(cid:1) La table est grande et la plupart des requêtes recherchent

.

F

K

B

O

U

B

I

moins de 2-4% des lignes

(cid:2) Quand ne pas créer des index

(cid:1) La table est petite

(cid:1) Les colonnes ne sont pas souvent utilisées

(cid:1) Les requêtes recherchent plus de 2-4% des lignes

(cid:1) La table est mise à jour fréquemment

58

.

F

K

B

O

U

B

I

60

15