Cours du langage procédural PL/SQL

POLYTECH

Université d’Aix-Marseille

Plan du cours

Introduction

SQL comme LDD

Identificateurs

Types

Tables

SQL comme Langage de Requêtes

Interrogation

Ordonner les réponses

Fonctions de groupe et regroupement de lignes

Opérateurs de l’algèbre relationnelle

Sous-requêtes

Vues

Fonctions pour requêtes SQL

SQL comme LCD

Bibliographie

Livres :

G. Gardarin : Bases de données objet et relationnel. Eyrolles ed. 1999.

C. J. Date : Introduction aux bases de données. (8ième edition). Vuibert ed. 2004.

H. Garcia-Molina, J. D. Ullman, J. Widow : Database systems, the complete book. Prentice Hall ed. 2002.

Supports de cours :

Support de cours : J. Le Maitre :

http

Support de cours : C. Sabatier, Université de la Méditerranée.

SQL : Introduction

SQL : Structured Query Langage

langage de gestion de bases de données relationnelles pour

définir les données (LDD) intérroger la base de données (Langage de requêtes) manipuler les données (LMD) controˆler l’accès aux données (LCD)

SQL : Introduction

SQL : Quelques repères historiques

1974 SEQUEL (Structured English Query Language) ancêtre de SQL

1979 premier SGBD basé sur SQL par Relational Software Inc.

(rebaptisé Oracle)

1986 SQL1 1ière norme ISO

1989 ajout des contraintes d’intégrité de base

(clé primaire et clé étrangère)

1992 SQL2 2ième norme extension de SQL1

(nouveaux types et nouveaux opérateurs

1999 SQL3 extension de SQL2

(introduction des types orientés objet)

SQL et Oracle

SQL : Quelques repères historiques Oracle est SGBD qui utilise SQL PL/SQL dit (L4G) langage procédural

nombreux programmes utilitaires :

SQL*PLUS SQL interactif

SQL*FORMS : saisie et visualisation des données avec des formulaires

SQL*REPORTWRITER : rapports imprimés

WebDB : interface avec le Web

···

SQL comme LDD

Identificateurs :

lettre suivie par : lettre ou chiffre ou  ou ? ou $ chaˆ?ne de caractères entre guillemets maximum 30 caractères

différent d’un mot clé

ASSERT, ASSIGN, AUDIT, COMMENT, DATE, DECIMAL,

DEFINITION, FILE, FORMAT, INDEX, LIST, MODE,

OPTION, PARTITION, PRIVILEGE, PUBLIC, SELECT,

SESSION, SET, TABLE pas de distinction entre majuscules et minuscules

SQL comme LDD

Tables :

relations d’un schéma relationnel stockées sous tables table : formée de lignes et de colonnes

SQL2 : nom d’une table précédé du nom d’un schéma ORACLE :

nom de schéma remplacé par le nom d’utilisateur qui a crée la table

par défaut le schéma est le nom de l’utilisateur connecté

SQL comme LDD

Tables :

SQL comme LDD

Tables : colonnes

toutes les données d’une colonne sont du même type identificateur unique pour les colonnes d’une même table

2 colonnes dans 2 tables différentes peuvent avoir le même nom nom complet d’une colonne comprend le nom complet de la table à laquelle elle appartient

exemple : DEPARTEMENTS.DEPARTEMENT ID ou

HR.DEPARTEMENTS.DEPARTEMENTID

SQL comme LDD

Types de données de SQL2 :

types pour les chaˆ?nes de caractères types numériques

types temporels (date, heure, ··· )

SQL2 n’a pas de type pour les données volumineuses (images , sons)

SQL2 ne permet pas à l’utilisateur de créer ses propres types

SQL comme LDD

SQL2 : Types pour les chaˆ?nes de caractères

CHAR(taille) chaˆ?nes de caractères de longueur fixe codage en longueur fixe : remplissage de blancs taille comprise entre 1 et 2000 octets

VARCHAR(taille max)

chaˆ?nes de caractères de longueur variable taille comprise entre 1 et 4000 octets

constantes chaˆ?nes de caractères entre guilllemets

SQL comme LDD

ORACLE : Types pour les chaˆ?nes de caractères

CHAR(taille) ou NCHAR(taille)

VARCHAR(taille max)

MAIS de préférence

VARCHAR2(taille max) ou NVARCHAR2(taille max)

constantes chaˆ?nes de caractères entre coˆtes

SQL comme LDD

ORACLE : Types pour les chaˆ?nes de caractères

exemples

NCHAR(5) : chaˆ?nes de 5 caractères

VARCHAR2(20) : chaˆ?nes de 20 caractères au plus

’Administration’, ’Marketing’

SQL comme LDD

SQL2 : Types numériques

types numériques pour les entiers :

SMALLINT pour 2 octets

INTEGER pour 4 octets types numériques pour les décimaux à virgule flottante :

REAL

DOUBLE PRECISION ou FLOAT

types numériques pour les décimaux à virgule fixe :

DECIMAL(nb chiffres, nbdécimales)

NUMERIC(nb chiffres, nb décimales) constantes exemples : 43.8, -13, 5.3E-6

SQL comme LDD

ORACLE : Types numériques

exemples

 ORACLE accepte tous les types numériques de SQL2 mais il les traduit dans ses propres types

 NUMBER : nombre en virgule flottante avec jusqu’à 38

chiffres significatifs

 NUMBER(nb chiffres, nbdécimales) : nombre en virgule fixe

SQL comme LDD

SQL2 : Types temporels

DATE

pour les dates

TIME

pour les heures, minutes et secondes

TIMESTAMP

pour un moment précis : date et heure, minutes et secondes

(précision jusqu’ à la microseconde)

SQL comme LDD

ORACLE : Types temporels le type DATE remplace DATE et TIME de SQL2

DATE correspond à une date avec une précision jusquà la seconde

constantes exemples : ’1/05/2007’ ou ’1 MAY 2007’

SQL comme LDD

SQL2 : Type boolén

pour enregistrer la valeur d’un bit exemples : BIT(1), BIT(4)  pas supporté par ORACLE

Base de donnée relationnelle : Manipulation : exemple (1)

exemple du cours précédent : BD vols-réservations

Voici 3 schémas de relations :

avions(NoAV, NOMAV, CAP, LOC) pilotes(NoPIL, NOMPIL, VILLE) vols(NoVOL, NoAV, NoPIL, V d, V a, H d, H a)

Base de donnée relationnelle : Manipulation : exemple (2) et voici les 3 tables correspondantes :

avions

pilotes

No AV

NOM AV

CAP

LOC

No PIL

NOM PIL

VILLE

100 101 102

airbus airbus carav

300 300 200

nice paris toulouse

1 2 3

laurent sophie claude

nice paris grenoble

vols

No VOL

No AV

No PIL

V d

V a

H d

H a

it100

100

1

nice

paris

7

8

it101

100

2

paris

toulouse

11

12

it102

101

1

paris

nice

12

13

it103

102

3

grenoble

toulouse

9

11

it104

101

3

toulouse

grenoble

17

18

SQL comme LDD : Exemple

 avions(noAV, NOM AV, CAP, LOC) no AV NUMBER(4) NOM AV VARCHAR2(20)

CAP NUMBER(4)

LOC VARCHAR2(15)

 pilotes(noPIL, NOM PIL, VILLE) no PIL NUMBER(4) NOM PIL VARCHAR2(20)

VILLE VARCHAR2(15)  vols(noVOL, no AV, no PIL, V d, V a, H d, H a) no VOL VARCHAR2(5) no AV NUMBER(4) no PIL NUMBER(4)

SQL comme LDD

ORACLE : Types pour objets larges

LOB : large objet formé par :

valeur du LOB : une grande donnée (jusqu’à 4 Go) index du LOB : structure d’accès le type localisateur du LOB pointeur vers l’endroit ou` il est stocké

Types pour objets larges

CLOB ou NCLOB : pour le stockage de grandes chaˆ?nes de caractères

BLOB : pour le stockage de grandes chaˆ?nes d’octets BFILE : pour le stockage de données binaires dans un fichier extérieur à la base

SQL comme LDD

ORACLE : Autres types

Les types chaˆ?nes d’octets

RAW(taille) : 2000 octets max

LONG RAW : 2 Go max

conversion automatique d’une chaˆ?ne d’octets en une chaˆ?ne de caractères représentant un nombre hexadécimal, et inversement

SQL comme LDD

ORACLE : Autres types

Le type adresse de ligne

 ROWID : une valeur de type est un nombre en base 64 dont

les chiffres sont représentés par : A-Z, a-z, 0-9, +, /, il s’écrit :

OOOOOOFFFBBBBBBLLL

OOOOOO : numéro de l’objet qui contient la ligne (6 chiffres en base 64)

FFF : numéro du fichier dans la BD (3 chiffres en base 64, le premier chiffre a le numéro 1 (AAB))

BBBBBB : numéro du bloc dans le fichier (6 chiffres en base 64)

LLL : numéro du ligne dans le bloc (3 chiffres en base 64), le premier chiffre a le numéro 0 (AAA))

SQL comme LDD

ORACLE : Absence de valeur

NULL : représente l’absence de valeur pour tous les types de données. Ce n’est pas une valeur

pour les types chaˆ?ne de caractères :

la chaˆ?ne vide ’ ’ représente aussi l’absence de valeur pour les types chaˆ?ne de caractères (taille fixe ou variable) une chaˆ?ne remplie de blancs n’est pas équivalente à la chaˆ?ne vide

pour les types numériques le nombre 0 ne représente pas l’absence de valeur

pour les types LOB

l’absence de valeur est l’absence de localisateur un LOB vide n’est pas une absence de valeur

SQL comme LDD

création de table

CREATE TABLE nom de table (liste de définition de colonne, [liste de contrainte de table]) ;

définition de colonne : := nom de colonne (nom de domaine ou type)

[liste de contrainte de colonne]

[DEFAULT valeur pardéfaut]

SQL comme LDD

création de table : contrainte de colonne

contrainte de colonne : := [CONSTRAINT nom] type de contrainte de colonne

type de contrainte de colonne : :=

PRIMARY KEY ou

NOT NULL ou

UNIQUE ou

CHECK(conditionsur valeur) ou

REFERENCES nom de table(nom de colonne)

SQL comme LDD

création de table : contrainte de table

contrainte de table : := [CONSTRAINT nom]

type de contrainte de table

type de contrainte de table : :=

PRIMARY KEY (liste de nom de colonne) ou

NOT NULL (liste de nom de colonne) ou

UNIQUE (liste de nom de colonne) ou

CHECK (conditionsur ligne) ou

FOREIGN KEY liste de nom de colonne REFERENCES nom de de table (liste de nom de colonne)

SQL comme LDD

Exemple : Création de la table avions à partir du schéma : avions(noAV, nomAV, CAP, LOC)

CREATE TABLE avions ( noAV NUMBER(4)

CONSTRAINT Cle P avions PRIMARY KEY,

NOMAV VARCHAR2(20),

CAP NUMBER(4)

CONSTRAINT Dom CAP avions CHECK (CAP ? 4),

LOC VARCHAR2(15) ) ;

SQL comme LDD : Exemple : Création de la table vols

Création de la table vols à partir du schéma :

vols(noVOL, noAV, noPIL, V d, V a, H d, H a)

Contraintes de colonnes ?

Contraintes de table?

SQL comme LDD : Exemple : Création de la table vols vols(noVOL, noAV, noPIL, V d, V a, H d, H a)

CREATE TABLE vols ( no VOL VARCHAR2(5)

CONSTRAINT Cle P vols PRIMARY KEY, no AV NUMBER(4)

CONSTRAINT Ref no AV vols REFERENCES avions, no PIL NUMBER(4)

CONSTRAINT Ref no PIL vols REFERENCES pilotes,

V d VARCHAR2(15) NOT NULL,

V a VARCHAR2(15) NOT NULL,

H d DATE,

H a DATE,

CONSTRAINT C1 vols CHECK (v d 6= v a),

CONSTRAINT C2 vols CHECK (h d < h a) ) ;

SQL comme LDD

suppression de table

DROP TABLE nom ;

Quand une table est supprimée, ORACLE :

efface tous les index qui y sont attachés quelque soit le propriétaire

efface tous les privilèges qui y sont attachés

MAIS les vues et les synomymes se référant à cette table ne sont pas supprimés

SQL comme LDD

modification de table

ALTER TABLE nom de table modification de table;

modification de table : :=

ADD COLUMN définition de colonne

ADD CONSTRAINT contrainte de table

DROP COLUMN nom de colonne

DROP CONSTRAINT nom de contrainte

SQL comme LDD

Exemple : Ajout d’un colonne à la table vols de schéma : vols(noVOL, noAV, noPIL, V d, V a, H d, H a) ALTER TABLE vols ADD COLUM COUT VOL NUMBER(8)

le schéma devient :

vols(noVOL, noAV, noPIL, V d, V a, H d, H a, COUT VOL)

SQL comme LDD

insertion de lignes dans une table

INSERT INTO nom de table [liste decolonnes] VALUES liste devaleurs; ou

INSERT INTO nom de table [liste decolonnes] requête ;

SQL comme LDD : Exemple

 ajouter un avion dans la table avions en respectant l’ordre des colonnes

INSERT INTO avions

VALUES (100, ’Airbus’, 200, ’Paris’);  ajouter un avion dans la table avions sans connaˆ?tre l’ordre

INSERT INTO avions (noAV, CAP, LOC, NOMAV)

VALUES (101, 200, ’Paris’, ’Airbus’) ;  ajouter un avion dans la table avions dont la localisation est

INDEFINI

INSERT INTO avions (noAV, NOMAV, CAP) VALUES (102, ’Airbus’, 200); ou

INSERT INTO avions

VALUES (102, ’Airbus’, 200, NULL);

SQL comme LDD

suppression de lignes d’une table

DELETE [FROM] nom de table [WHERE condition] ; Exemples :

vider la table avions

DELETE FROM avions; supprimer de la table avions tous les avions dont la capacité est inférieur à 100

DELETE FROM avions

WHERE CAP < 100;

SQL comme LDD

modification de lignes dans une table

UPDATE nom de table SET liste expressioncolonne

[WHERE condition] ;

expressioncolonne : := nomde colonne = expression ou nom de colonne = requête

Exemple :

modifier la capacité de l’avion numéro 100

UPDATE avions SET CAP = 300

WHERE noAV = 100 ;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

interrogation

requête : := SELECT [DISTINCT] projection

FROM liste de (nom de table [[AS] nom]) | (requête AS nom)

WHERE condition

[GROUP BY liste de nom de colonne]

[HAVING condition]

[ORDER BY liste de ((nom de colonne | rang de colonne) (ASC | DESC)]; requête : := requête ( UNION | INTERSECT | EXCEPT ) requête

requête : := (requête)

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

projection : :=

* | nom de table | liste de (terme de projection[[AS] nom]) terme de projection : := expression | agrégation

expression : := valeur | nom de colonne | expressionarithmétique | ···

agrégation : := COUNT(*)

opérateurd’agrégation([DISTINCT] expression)

opérateurd’agrégation : :=

COUNT | SUM | AVG | MAX | MIN

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

condition : := condition élémentaire

NOT condition | condition(AND | OR) condition | (condition) condition élémentaire : := reconnaissance de modèle | test de valeur nulle | comparaison

|

appartenance àun intervalle | appartenance à un ensemble | existence

reconnaissance de modèle : := expression [NOT] LIKE nom de chaˆ?ne

test de valeur nulle : := nom de valeur IS [NOT] NULL

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

comparaison : := expression (= | <>| > | < | <= | >= | ) expression | expression (= | <>) (| SOME | ALL ) requête

expression (> | < | <= | >=) (| SOME | ALL )

requête mono colonne

appartenance à un intervalle : := expression BETWEEN expression AND expression

appartenance à un ensemble : := expression (IN | NOTIN) (requête) |

(liste de expression) (IN | NOTIN) (requête)

ensemble de valeurs : := (liste de valeur) | requête mono colonne existence : := EXISTS (requête)

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Sélection de lignes

 toutes les lignes et toutes les colonnes

SELECT * FROM nom de table;

 pour connaˆ?tre toutes les caractéristiques des avions stockés dans la table

SELECT * FROM avions;

 toutes les lignes mais seulement certaines colonnes

SELECT liste de nom de colonne FROM nom de table;  pour connaˆ?tre les numéros de tous les vols

SELECT noVOL FROM vols;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Sélection de lignes

 suppression des lignes dupliquées

SELECT DISTINCT liste de nom de colonne FROM nom de table;

pour connaˆ?tre les numéros des pilotes qui conduisent au moins un avion

SELECT DISTINCT no PIL FROM vols ; colonnes calculées

SELECT expression [AS alias] FROM nom de table;  afficher une augmentation de 5% du coût de chaque vol

SELECT noVOL, ’5%’ ”%”,

COUT VOL*0.05 AUGM, COUTVOL*1.05 ”Nouveau coût” FROM vols;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes : Exemple de calcul

sur les chaˆ?nes de caractères afficher les trajets assurés par les vols sous la forme :

Ville de départ ?? > Ville d’arrivée

SELECT noVOL,

Vd || ^? ? ? >^? || Va TRAJET FROM vols;

sur les dates afficher les dates de départ et d’arrivée de chaque vol en décalant les dates

SELECT noVOL, Dd + 1/24 D d, D a + 1/24 Da

FROM vols;

 pour connaˆ?tre la durée en heures de tous les vols

SELECT noVOL, 24 *(D a -D d) durée

FROM vols;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Recherche par comparaison

SELECT liste de nom de colonne FROM nom de table

WHERE expression ;  pour connaˆ?tre tous les avions qui ont une capacité > 200 places

SELECT noAV

FROM avions

WHERE CAP > 200;

 pour connaˆ?tre tous les pilotes qui effectuent des vols qui

durent plus d’une heure

SELECT DISTINCT no PIL

FROM vols

WHERE (24 *(D a -D d) )> 1;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Recherche par ressemblance

SELECT liste de nom de colonne FROM nom de table

WHERE expression [NOT] LIKE motif [caractères spéciaux] ;

caractère spéciaux :

% : remplace 0, 1 ou plusieurs caractères : remplace 1 caractère

caractère d’échappement :

permet de traiter les caractère spéciaux comme de simples caractères

il n’y a pas de caractère d’échappement prédéfini

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Recherche par ressemblance : exemples

 pour connaˆ?tre la capacité de tous les avions Boeing

SELECT noAV, NOMAV, CAP

FROM avions

WHERE NOM AV LIKE ’Boeing%’ ;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Recherche avec condition conjonctive

SELECT liste de nom de colonne FROM nom de table

WHERE condition AND condition;

 pour connaˆ?tre tous les avions qui sont à Marseille et dont la capacité est de 300 places

SELECT noAV

FROM avions

WHERE LOC = ’Marseille’ AND CAP = 300;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Recherche avec condition disjonctive

SELECT liste de nom de colonne FROM nom de table

WHERE condition OR condition;

 pour connaˆ?tre tous les vols qui utilisent les avions 100 ou 101

SELECT noVOL

FROM vols

WHERE no AV = 100 OR no AV = 101;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Recherche avec condition négative

SELECT liste de nom de colonne FROM nom de table

WHERE NOT condition ;

 pour connaˆ?tre tous les pilotes qui n’habitent pas à Marseille

SELECT noPIL

FROM pilotes

WHERE NOT VILLE = ’Marseille’;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Recherche avec un intervalle

SELECT liste de nom de colonne FROM nom de table

WHERE expression BETWEEN expression AND expression ;

 pour connaˆ?tre tous les avions qui ont une capacité entre 200 et 300 places SELECT noAV

FROM avions

WHERE CAP BETWEEN 200 AND 300;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Recherche avec une liste

SELECT liste de nom de colonne FROM nom de table

WHERE expression [NOT] IN liste de expression ;

 pour connaˆ?tre tous les pilotes qui habitent soit à Marseille soit à Nice

SELECT noPIL

FROM pilotes

WHERE VILLE IN (’Marseille’, ’Nice’);

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Recherche avec une liste

SELECT liste de nom de colonne FROM nom de table WHERE expression (<>| > | < | <= | >= | ) ALL

liste deexpression ;

SELECT liste de nom de colonne FROM nom de table WHERE expression (<>| > | < | <= | >= | ) SOME liste de expression ; pour connaˆ?tre tous les vols dont le départ est à plus de 5 jours

et dont la durée est moins de 5 heures

SELECT noVOL, Dd, 24*(D a - D d) Durée

FROM vols

WHERE D d > ALL (sysdate +5, D a -5/24);

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Traitement de l’absence de valeur

sur les expressions numériques un calcul numérique ou de dates exprimé avec les opérateurs +, ?, ?, / n’a pas de valeur lorsqu’au moins une des composantes n’a pas de valeur

SELECT noAV, 2*CAP/3 AS CAPRED

FROM avions

WHERE no AV = 320;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Traitement de l’absence de valeur

sur les chaˆ?nes de caractères un calcul de chaˆ?nes exprimé avec l’opérateur || n’a pas de valeur lorsque toutes ses composantes n’ont pas de valeur la chaˆ?ne vide et l’absence de valeur sont confondues

SELECT no CL, NOM RUE CL || ” || VILLE CL AS ADR CL ou no CL, NOM RUECL || NULL || VILLECL AS ADRCL

FROM clients

WHERE no CL = 1035;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Traitement de l’absence de valeur

sur les comparaisons toute comparaison exprimée avec les opérateurs =, <>, >, <, <=, >=, LIKE qui comporte une expression qui n’a pas de valeur prend la valeur logique INDEFINIE  les comparaisons ignorent les lignes où il y a absence de valeur

SELECT * FROM pilotes

WHERE NAISSPIL <> 1960 AND VILLE <> ’Paris’ ;

 comparaisons indéfinies :

SELECT *

FROM avions

WHERE NULL = NULL OR ” = ” OR ” LIKE ’%’

OR ’A’ LIKE ” OR ’A’ NOT LIKE ”;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Traitement de l’absence de valeur

table de vérité pour le connecteur ?

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Traitement de l’absence de valeur

table de vérité pour le connecteur ?

table de vérité pour le connecteur ¬

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Traitement de l’absence de valeur

équivalences disjonctives expr NOT BETWEEN expr₁ AND expr₂ ? expr < expr₁ OR expr > expr₂

 expr IN (expr₁ ··· exprN )

? expr = expr₁ OR ··· OR expr = exprN

 expr op ANY (expr₁ ··· exprN ) ? expr op expr₁ OR ··· OR expr op exprN

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Traitement de l’absence de valeur

Les expressions suivantes :

expr NOT BETWEEN expr₁ AND expr₂ expr IN (expr₁ ··· exprN ) expr op ANY (expr₁ ··· exprN )

sont vraies ssi expr a une valeur et si au moins une des expressions expr₁, exprN a une valeur qui satisfait les comparaisons

SELECT NUM PIL FROM pilotes

WHERE VILLE IN (’Marseille’, ’Nice’, ”) ;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Traitement de l’absence de valeur

équivalences conjonctives expr BETWEEN expr₁ AND expr₂ ? expr >= expr₁ AND expr <= expr₂

 expr NOT IN (expr₁ ··· exprN )

? expr <> expr₁ AND ··· AND expr <> exprN

 expr op ALL (expr₁ ··· exprN )

? expr op expr₁ AND ··· AND expr op exprN

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Traitement de l’absence de valeur

Les expressions suivantes :

expr BETWEEN expr₁ AND expr₂ expr NOT IN (expr₁ ··· exprN ) expr op ALL (expr₁ ··· exprN )

sont vraies ssi expr a une valeur et si toutes les expressions expr₁, exprN ont une valeur qui satisfait la comparaison

SELECT NUM PIL FROM pilotes

WHERE VILLE NOT IN (’Marseille’, ’Nice’, ”) ;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Traitement de l’absence de valeur recherche de l’absence de valeur

SELECT liste de nom de colonne FROM nom de table

WHERE expression IS [NOT] NULL ;

 pour connaˆ?tre tous les vols auxquels on n’a pas encore affecté d’avions

SELECT noVOL

FROM vols

WHERE no AV IS NULL;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Traitement de l’absence de valeur

Donner une valeur à l’absence de valeur

NVL (expr₁, expr₂) = expr₁ si elle définie, expr₂ sinon expr₁ et expr₂ doivent être de même type

 pour qu’une capacité d’avion inconnue soit considérée comme nulle

SELECT noVOL, NVL(CAP,0) FROM avions;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Ordonner les réponses

SELECT liste de nom de colonne

FROM nom de table

[WHERE expression]

ORDER BY { expression | position } [ASC | DESC]

[{ expression | position } [ASC | DESC]];

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Ordonner les réponses

 pour connaˆ?tre les horaires des vols triés par ordre croissant des dates et heures de départ

SELECT noVOL, DATEd, DATEa

FROM vols

ORDER BY DATEd;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Les fonctions de groupe

 les fonctions de groupe calculent les résultats à partir d’une collection de valeurs.

COUNT (*) comptage des lignes

COUNT ([DISTINCT | ALL]) comptage des valeurs

MAX ([DISTINCT | ALL]) maximum des valeurs

MIN ([DISTINCT | ALL]) minimum des valeurs

SUM ([DISTINCT | ALL]) somme des valeurs

AVG ([DISTINCT | ALL]) moyenne des valeurs

STDDEV ([DISTINCT | ALL]) écart-type des valeurs

VARIANCE ([DISTINCT | ALL]) variance des valeurs

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Les fonctions de groupe

 pour connaˆ?tre le nombre d’avions

SELECT COUNT(*) NBR AV

FROM avions ;

 pour connaˆ?tre le nombre d’heures de vols du pilote 4020

SELECT SUM(24 *(Da - D d)) NBR H

FROM vols

WHERE noPIL = 4020 ;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Les regroupements de lignes

 les fonctions de groupe calculent les résultats à partir d’une collection de valeurs. SELECT liste d’expressions1

FROM nom de table

GROUP BY liste d’expressions2 ;

les expressions de liste d’expressions1 doivent être des expressions formées uniquement :

d’expressions de liste d’expressions2

de fonctions de groupe de constantes littérales

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Les regroupements de lignes

 pour connaˆ?tre le nombre d’avions affectés à chaque ville d’affectation d’un avion

SELECT LOC, COUNT(*) NBR AV

FROM avions

GROUP BY LOC;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Les regroupements de lignes

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Les regroupements de lignes

 pour connaˆ?tre le nombre de vols qui ont la même durée

SELECT 24*(D a - D d) DUR VOL, COUNT(*) NBR VOL

FROM vols

GROUP BY D a - D d;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Les regroupements de lignes

 regroupement de lignes sélectionnées

SELECT liste d’expressions1

FROM nom de table

WHERE condition

GROUP BY liste d’expressions2 ;

 pour connaˆ?tre le nombre d’avions différents utilisés par

chaque pilote assurant un vol

SELECT LOC, noPIL, COUNT(DISTINCT noAV) NBR AV

FROM vols

WHERE noPIL IS NOT NULL

GROUP BY noPIL ;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Conditions sur l’ensemble des lignes

SELECT liste d’expressions

FROM nom de table

[ WHERE condition ] HAVING condition sur lignes ;

les expressions de liste d’expressions et conditionsur lignes doivent être formées uniquement :

de fonctions de groupe de constantes littérales

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Conditions sur l’ensemble des lignes

 pour savoir si le pilote 4010 assure tous les vols avec un avion différent à chaque fois

SELECT ’OUI’ REP

FROM vols

WHERE noPIL = 4010

HAVING COUNT(*) = COUNT(DISTINCT noAV);

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Conditions sur l’ensemble des lignes

SELECT liste d’expressions

FROM nom de table

[ WHERE condition ] GROUP BY liste d’expressions2 HAVING condition sur lignes ;

les expressions de liste d’expressions et conditionsur lignes doivent être formées uniquement :

d’expressions de liste d’expressions2

de fonctions de groupe de constantes littérales

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Conditions sur l’ensemble des lignes

 pour connaˆ?tre les pilotes qui conduisent au moins deux avions

différents

SELECT noPIL

FROM vols

WHERE noPIL IS NOT NULL

GROUP BY noPIL

HAVING COUNT(DISTINCT noAV) >= 2 ;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Opérateurs ensemblistes

SELECT liste d’expressions1

FROM nom de table

[ WHERE condition ]

[ GROUP BY liste d’expressions2]

UNION | UNION ALL | INTERSECT | MINUS

SELECT liste d’expressions3 FROM nom de table

[ WHERE condition ]

[ GROUP BY liste d’expressions4] ;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Opérateurs ensemblistes

 pour connaˆ?tre les villes qui sont soit des villes de départ soit des villes d’arrivées d’un vol

SELECT V d VILL

FROM vols

WHERE V d IS NOT NULL

UNION

SELECT V a

FROM vols

WHERE V a IS NOT NULL;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Opérateurs ensemblistes

 pour connaˆ?tre le nombre de vols assurés par chaque pilote

SELECT noPIL, COUNT(*) NBRVOL

FROM vols

WHERE noPIL IS NOT NULL

GROUP BY noPIL

UNION ALL

(SELECT noPIL, 0

FROM pilotes

MINUS

SELECT noPIL, 0

FROM vols) ;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Produit cartésien

SELECT liste d’expressions

FROM listede(nom de table [ alias ])

[ WHERE condition ] ;

 pour connaˆ?tre le couˆt de chaque classe du vol V900 lorsqu’on les applique au vol V100

SELECT Classe, COEFPRIX * COUT VOL COUT

FROM defclasses D, vols V

WHERE D.noVOL = ’V900’ AND V.noVOL = ’V100’ :

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Opérateur de jointure naturelle

SELECT liste d’expressions

FROM listede(nom de table [ alias ])

WHERE expr comp expr [AND | OR expr comp expre ] ; ou

SELECT liste d’expressions

FROM

nom detable [ alias ] INNER JOIN nom detable [ alias ]

ON expr comp expr [ AND | OR expr comp expre ] ;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Opérateur de jointure naturelle

 pour connaˆ?tre le nombre de places de chaque vol qui a été affecté à un avion

SELECT noVOL, CAP

FROM vols, avions

WHERE = ;

Fonctions pour requêtes SQL

Opérateur de jointure naturelle : exemple (1)

equi-jointure sur no AV

Fonctions pour requêtes SQL

Opérateur de jointure naturelle : exemple (2)

projection sur no VOL, CAP

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Opérateur de semi-jointure externe

SELECT liste d’expressions

FROM nom de table1, nom de table2

WHERE exprtable1 comp (+) [ AND exprtable1 comp (+) ] ou

SELECT liste d’expressions

FROM

nom detable1 [ alias ]

LEFT JOIN | RIGHT JOIN nom detable2 [ alias ]

ON expr comp expr [ AND | OR expr comp expr ] ;

Fonctions pour requêtes SQL

SQL comme Langage de Requêtes

Opérateur de semi-jointure externe

 pour connaˆ?tre le nombre de places de chaque vol (même lorsqu’aucun avion n’est affecté au vol)

SELECT noVOL, CAP

FROM vols V LEFT JOIN avions A

ON V.noAV = A.noAV;

Fonctions pour requêtes SQL

Opérateur de semi-jointure externe : exemple (1)

equi-jointure sur no AV

Fonctions pour requêtes SQL

Opérateur de semi-jointure externe : exemple (2)

equi-jointure externe

Fonctions pour requêtes SQL

Opérateur de semi-jointure externe : exemple (2)

projection sur no VOL, CAP

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Sous-requêtes

imbrication de sous-requêtes dans la clause WHERE

SELECT projection FROM nom de table

WHERE condition

(SELECT projection

FROM nom detable

WHERE condition, ···);

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Sous-requêtes : donnant une seule ligne

SELECT projection

FROM nom de table

WHERE expr op

(SELECT projection

FROM nom detable

WHERE condition, ···);

op ? { = , <>, < , <= , > , >= }

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Sous-requêtes : donnant une seule ligne

 pour connaˆ?tre les vols qui utilisent le même avion que celui utilisé par le vol V101

SELECT noVOL

FROM vols

WHERE noAv = (SELECT noAv

FROM vols

WHERE no VOL = ’V101’ );

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Sous-requêtes donnant au plus une ligne

SELECT projection

FROM nom de table

WHERE (expr₁, ··· exprn) op

(SELECT (expr₁, ··· exprn)

FROM nom detable

WHERE condition, ···);

op ? {= , <>}

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Sous-requêtes donnant au plus une ligne

 pour connaˆ?tre le vols qui assure le même trajet que celui du vol V101 mais 2 jours plus tard

SELECT noVOL

FROM vols

WHERE (Vd, V a, D d, D a) =

(SELECT (V d, V a, D d+2, D a+2)

FROM vols

WHERE no VOL = ’V101’ );

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Sous-requêtes donnant 0, 1 ou plusieurs lignes

SELECT projection

FROM nom de table

WHERE expr NOT IN

(SELECT (projection FROM ··· );

SELECT projection

FROM nom de table

WHERE expr op ANY | ALL

(SELECT (projection FROM ··· );

op ? { = , <>, < , <= , > , >= }

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Sous-requêtes donnant 0, 1 ou plusieurs lignes

 pour connaˆ?tre le vols qui sont assurés par un pilote qui habite Paris

SELECT noVOL

FROM vols

WHERE noPIL IN

(SELECT noPIL

FROM pilotes

WHERE VILLE = ’Paris’ );

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Sous-requêtes donnant 0, 1 ou plusieurs lignes

 pour connaˆ?tre les pilotes qui n’assurent aucun vol

SELECT noPIL

FROM pilotes

WHERE noPIL NOT IN

(SELECT noPIL

FROM vols

WHERE no PIL IS NOT NULL );

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Sous-requêtes d’existence

SELECT projection

FROM nom de table [Alias]

WHERE [NOT] EXISTS

(SELECT (projection

FROM ··· );

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Sous-requêtes d’existence

 pour connaˆ?tre les avions qui sont conduits par au moins un pilote de Marseille

SELECT DISTINCT noAV

FROM vols

WHERE EXISTS

(SELECT *

FROM pilotes

WHERE no PIL = PIL

AND VILLE = ’Marseille’);

Fonctions pour requêtes SQL

Les vues

Les vues

une vue est une table virtuelle résultat d’une requête

roˆle d’une vue réduire la complexité syntaxique des requêtes définir les schémas externes. définir des contraintes d’intégrité.

définir un niveau additionnel de sécurité en restreignant l’accès à un sous ensemble de lignes et/ ou de colonnes.

Fonctions pour requêtes SQL

Les vues (exemple)

Création d’une vue ligne

Fonctions pour requêtes SQL

Les vues : création

Les vues

 création d’une vue de schéma externe

CREATE [OR REPLACE ] [ FORCE | NO FORCE ]

VIEW nom de table [(liste de nomde colonne)]

AS requête [WITH CHECK OPTION | WITH READ ONLY ] ;

 création de la vue correspondant aux vols qui partent de Paris

CREATE VIEW volsd Paris

AS SELECT no VOL, V a, H d, Ha

FROM vols

WHERE Vd = ’Paris’;

Fonctions pour requêtes SQL

Les vues : interrogation

interroger une vue interrogation de la vue correspondant aux vols qui partent de Paris

SELECT * FROM vols dParis;

 supprimer une vue DROP VIEW nom de vue ;

 suppression de la vue correspondant aux vols qui partent de Paris

DROP VIEW volsd Paris ;

Fonctions pour requêtes SQL

Requêtes avec vues

Exemples

 pour connaˆ?tre les pilotes qui assurent le plus grand nombre de vols

CREATE VIEW nbre de volspar pil

AS SELECT noPIL, count(*) AS NBR VOLS

FROM vols V

WHERE no PIL IS NOT NULL

GROUP BY no PIL ;

SELECT noPIL FROM nbre de vols parpil

WHERE NBR VOLS =

(SELECT max( NBR VOLS)

FROM nbre de volspar pil) ;

Fonctions pour requêtes SQL

Les vues : mise à jour

 opérations sur les vues

INSERT

UPDATE

DELETE

 restrictions : Ces instructions ne s’appliquent pas aux vues qui contiennent :

une jointure

un opérateur ensembliste : UNION, INTERSECT, MINUS une clause GROUP BY, CONNECT BY, ORDER BY ou START WITH

la clause DISTINCT, une expression ou une pseudo-colonne dans la liste de sélection des colonnes.

Fonctions pour requêtes SQL

Requêtes avec vues

 création de la vue pour la personne qui définit les vols

CREATE VIEW defvols

AS SELECT no VOL, V d, D d, V a, D a

FROM vols

WHERE no VOL IS NULL AND no PIL IS NULL;  définir un nouveau vol

INSERT INTO defvols VALUES

(’V999’, ’Marseille’, todate(’01/05/07 10 :30’, ’DD/MM/RR

HH :MI’), ’Paris’, todate(’01/05/07 10 :30’, ’DD/MM/RR HH :MI’));

Fonctions pour requêtes SQL

Requêtes avec vues

 supprimer un vol non affecté

DELETE FROM defvols

WHERE no VOL = ’V998’;  modifier un vol non affecté

UPDATE defvols

SET D d= D d + 1 / 24, D a= D a + 1 / 24 WHERE no VOL = ’V998’;

 connaˆ?tre les vols non affectés

SELECT * FROM defvols;

Fonctions pour requêtes SQL

Requêtes avec vues

 création de la vue pour la personne qui définit les vols

CREATE VIEW defvols

AS SELECT no VOL, V d, D d, V a, D a

FROM vols

WHERE no VOL IS NULL AND no PIL IS NULL;

 modifier un vol non affecté

UPDATE defvols

SET D d= D d + 1 / 24, D a= D a + 1 / 24

WHERE no VOL = ’V998’;  connaˆ?tre les vols non affectés

SELECT * FROM defvols;

Fonctions pour requêtes SQL

Requêtes avec vues

 création de la vue pour la personne qui affecte un avion et un pilote à un vol

CREATE VIEW affectvols

AS SELECT noVOL, no AV, no PIL

FROM vols;  affecter un avion et un pilote à un nouveau vol

UPDATE affectvols

SET no AV = 101, no PIL = 5050 WHERE no VOL = ’V999’

AND no AV IS NUL AND noPIL IS NULL ;  affecter un nouvel avion à un vol

UPDATE affectvols

SET no AV = 202

WHERE no VOL = ’V999’;

Fonctions pour requêtes SQL

Requêtes avec vues

 création de la vue pour la personne qui affecte un avion et un pilote à un vol

CREATE VIEW affectvols

AS SELECT noVOL, no AV, no PIL

FROM vols;  permuter l’affectation des pilotes de 2 vols

UPDATE affectvols A1

SET no PIL =

(SELECT no PIL

FROM affect vols A2

WHERE

( VOL = ’V100’ AND A2.noVOL = ’V200’)

OR

( VOL = ’V200’ AND A2.noVOL = ’V100’))

WHERE no VOL = ’V100’ OR no VOL = ’V200’ ;

Fonctions pour requêtes SQL

Les vues : contrôle de mise à jour

 création d’une vue de vérification : controˆle de l’insertion ou de la modificaion de ligne

CREATE VIEW nomde vue

AS requête

WITH CHECK OPTION ;

 vérification des contraintes de domaine (interdiction des valeurs inconnues)

CREATE VIEW aavions

AS SELECT * FROM avions

WHERE no AV > 0 AND CAP > 1

AND NOM AV IN (’Airbus’, ’Boeing’, ’Caravelle’) WITH CHECK OPTION ;

Fonctions pour requêtes SQL

Requêtes avec vues

 vérification des contraintes de domaine (autorisation des valeurs inconnues)

CREATE VIEW aaavions

AS SELECT * FROM avions

WHERE no AV > 0

AND (CAP IS NULL OR CAP > 1)

AND (NOM AV IS NULL OR IN (’Airbus’, ’Boeing’,

’Caravelle’))

WITH CHECK OPTION ;

Fonctions pour requêtes SQL

Requêtes avec vues

Contraintes de référence

1)    valider l’insertion dans la table référenant

2)    valider la suppression dans la table référencée

règle d’adéquation : les insertions et les suppressions se font

toujours au travers des vues

Fonctions pour requêtes SQL

Requêtes avec vues

Exemple : expression de clés étrangères de la relation vols

 validation des insertions dans vols

CREATE VIEW aavions

AS SELECT * FROM vols

WHERE no AV > 0

AND (no PIL IS NULL OR no PIL IN(SELECT noPIL FROM pilotes))

AND (NOM AV IS NULL OR IN (SELECT NOM AV FROM avions))

WITH CHECK OPTION ;

Fonctions pour requêtes SQL

Requêtes avec vues

Exemple : expression de clés étrangères de la relation vols

 validation des suppressions dans avions et dans pilotes

CREATE VIEW davions

AS SELECT * FROM avions A

WHERE NOT EXISTS ( SELECT * FROM vols V WHERE

A.noAV = V.noAV);

CREATE VIEW dpilotes

AS SELECT * FROM pilotes P

WHERE NOT EXISTS ( SELECT * FROM vols V WHERE

P.no PIL = V.noPIL);

Fonctions pour requêtes SQL

Requêtes avec vues

Exemple : ajout des contraintes de domaine de 2 fa¸cons

 agrégation

CREATE VIEW adavions

AS SELECT * FROM avions A

WHERE no AV > 0 AND CAP > 1

AND NOM AV IN (’Airbus’, ’Boeing’, ’Caravelle’)

AND NOT EXISTS ( SELECT * FROM vols V WHERE

A.noAV = V.noAV)

WITH CHECK OPTION ;

Fonctions pour requêtes SQL

Requêtes avec vues

Exemple : ajout des contraintes de domaine de 2 fa¸cons

 modulaire

CREATE VIEW aavions

AS SELECT * FROM avions WHERE no AV > 0 AND CAP > 1

AND NOM AV IN (’Airbus’, ’Boeing’, ’Caravelle’)

AS SELECT * FROM pilotes P

WITH CHECK OPTION ;

CREATE VIEW adavions

AS SELECT * FROM aavions

WHERE NOT EXISTS ( SELECT * FROM vols V WHERE

A.noAV = V.noAV);

Fonctions pour requêtes SQL

Requêtes avec vues

Expression de contraintes générales

Exemple : empêcher l’affectation d’un même avion à deux vols différents dont les tranches horaires se chevauchent

CREATE VIEW a vols

AS SELECT * FROM vols V1 WHERE NOT EXISTS (

SELECT * FROM vols V2

WHERE AV = AV

AND NVL(, ) >= NVL(, V1.D a)

AND NVL(, ) >= NVL(, V2.D a))

WITH CHECK OPTION ;

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions numériques (1)

SIGN(nombre) signe du nombre spécifié

ABS(nombre) valeur absolue

ACOS(nombre) arc cosinus

ASIN(nombre) arc sinus

ATAN(nombre) arc tangente

COS(nombre) cosinus

SIN(nombre) sinus

TAN(nombre) tangente

COSH(nombre) cosinus hyperbolique

SINH(nombre) sinus hyperbolique

TANH(nombre) tangente hyperbolique

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions numériques (2)

EXP(puissance) e élevé à la puissance

LN(nombre) logarithme naturel

LOG(base,nombre) logarithme en base quelconque

SQRT(nombre) racine carrée

POWER(nombre,puissance) puissance d’un nombre

MOD(dividende,diviseur) modulo (reste de la division)

CEIL(nombre) le plus petit entier plus grand que le nombre passé en argument

FLOOR(nombre) le plus grand entier plus petit ou égal au nombre passé en argument

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions numériques (3)

 ROUND(nombre [position]) arrondi à la position spécifiée. Un entier positif (resp. négatif) indique une position après (resp. avant) la virgule. Par défaut il y a arrondi à l’unité.

 TRUNC(nombre [position]) troncature à la position spécifiée (voir ROUND). Par défaut il y a troncature à l’unité.

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Conversion en chaˆ?ne de caractères (1)

TO CHAR(nombre[format[nlsparam]])

format est une chaˆ?ne de caractères formée des éléments suivants :

9 un chiffre quelconque

0 un chiffre ou un zéro si absence de chiffres

$ symbole monétaire américain

B remplace le nombre 0 par des blancs

MI signe du nombre post-fixé s’il est négatif, un blanc post-fixé sinon.

S signe du nombre

PR nombre mis entre ”<” et ”>” s’il est négatif, nombre mis entre 2 blancs sinon.

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Conversion en chaˆ?ne de caractères (2)

et des éléments suivants :

D caractère qui sépare la partie entière de la partie fractionnaire

G symbole de séparation de groupes de chiffres

C symbole monétaire international

L symbole monétaire local

. , caractères affichés tels quels

V9 9 multiplie le nombre par 10n o n est le nombre de 9 après V.

EEEE écriture scientifique du nombre

RM rm écriture en chiffres (entre 0 et 3999)

FM écriture scientifique du nombre

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Conversion en chaˆ?ne de caractères (3)

TO CHAR(nombre[format[nlsparam]]) nls param est une chaˆ?ne de caractères formée à partir des expressions suivantes :

NLS NUMERIC CHARACTERS = ”dg”

LS CURRENCY = ”symbolemonétaire local”

NLS ISO CURRENCY = territoire

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les chaˆ?ne de caractères : recherche de sous chaˆ?ne

INSTR(chaˆ?ne, souschaˆ?ne [position [n]])

INSTRB(chaˆ?ne, souschaˆ?ne [position [n]])

à partir d’une position (par défaut 1), exprimée en nombre de caractères ou d’octets, relative au début de la chaˆ?ne si position est positif, ou relative à sa fin si position est négatif, recherche la position de la nième (par défaut 1ère) occurrence de la sous chaˆ?ne, retourne 0 lorsque la recherche échoue.

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les chaˆ?ne de caractères : extraction de sous chaˆ?ne

SUBSTR(chaˆ?ne, position [longueur])

SUBSTRB(chaˆ?ne, position [longueur])

à partir d’une position, extrait une sous chaˆ?ne de longueur donnée (par défaut jusqu’à la fin de la chaˆ?ne).

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les chaˆ?ne de caractères : remplacement ou suppression

 REPLACE(chaˆ?ne,souschaˆ?ne [sous chaˆ?ne de remplacement]) remplace ou supprime toutes les occurrences d’une sous chaˆ?ne.

LTRIM(chaˆ?ne [ensemblecaractères à supprimer])

RTRIM(chaˆ?ne [ensemblecaractères à supprimer]) supprime à gauche (ou à droite) de la chaˆ?ne les occurrences des caractères à supprimer (par défaut 1 blanc).

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les chaˆ?ne de caractères : répétition

LPAD(chaˆ?ne,longueur [chaˆ?nerépétée]) RPAD(chaˆ?ne,longueur [chaˆ?nerépétée])

répétition, à gauche (ou à droite) de la chaˆ?ne, d’une autre chaˆ?ne (par défaut 1 blanc) jusqu’à obtenir la longueur spécifiée.

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les chaˆ?ne de caractères : minuscules, majuscules

LOWER(chaˆ?ne) met en minuscules la chaˆ?ne.

UPPER(chaˆ?ne) met en majuscules la chaˆ?ne.

INITCAP(chaˆ?ne) met en majuscules les premières lettres de chaque mot.

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les chaˆ?ne de caractères

LENGTH(chaˆ?ne) longueur de la chaˆ?ne en caractères

LENGTHB(chaˆ?ne) longueur de la chaˆ?ne en octets CHR(code [USING NCHARCS])

retourne le caractère du jeu de caractères de base ou du jeu de caractères local, qui correspond au code.  ASCII(chaˆ?ne) retourne le code décimal du premier caractère.

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les chaˆ?ne de caractères : conversion de type

TO NUMBER(chaˆ?ne) retourne le nombre correspondant

HEXTORAW(chaˆ?nehexa) retourne la chaˆ?ne d’octets correspondants

CHARTOROWID(chaˆ?ne) retourne le ROWID correspondant TRANSLATE(chaˆ?ne USING CHAR CS | NCHARCS)

conversion d’un type de chaˆ?ne de caractères basé sur le jeu de caractères de base dans un type de chaˆ?ne de caractères basé sur le jeu de caractères national, et inversement.

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les chaˆ?ne de caractères : traduction (le type est inchangé)

 TRANSLATE(chaˆ?ne,listesource, listedestination) traduit la chaˆ?ne en remplaant chacun de ses caractères figurant la liste source par son correspondant dans la liste destination.

CONVERT(chaˆ?ne[jeu de carac dest[jeu de carac source]]) traduction d’un jeu de caractères dans un autre. SOUNDEX(chaˆ?ne) retourne la chaˆ?ne phonétique.

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les dates : recherche

SYSDATE retourne la date courante

LAST DAY(date) retourne la dernière date du mois qui contient la date passée en argument.

NEXTDAY(date, nom jour ds semaine)

retourne la première date postérieure à la date passée en argument et qui correspond au jour de la semaine passé en argument.

Pour connaˆ?tre la date du prochain lundi.

SELECT NEXT DAY(SYSDATE,’Lundi’) ProchainLundi

FROM Dual ;

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les dates : calcul sur les mois

 ADD MONTHS(date, nbremois) retourne la date passée en argument augmentée d’un nombre de mois

 MONTHS BETWEEN(date1, date2) retourne le nombre de mois qui séparent les 2 dates, avec éventuellement une partie fractionnaire correspondant à une partie de 31 jours.

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les dates : calcul sur les mois

 SELECT

monthbetween(’16/03/99’,’01/02/99’) NBmois1, todate(’16/03/99’)-todate(’01/02/99’) NBjours1 monthbetween(’16/04/99’,’01/03/99’) NBmois2, todate(’16/04/99’)-todate(’01/03/99’) NBjours2 From Dual;

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les dates : conversion de date

 NEWTIME(date,timezone1,timezone2) retourne la date passée en argument convertie par changement de zone horaire.

AST,ADT Atlantique standard ou décalé

BST,BDT Bering standard ou décalé

CST,CDT Central standard ou décalé

EST,EDT Oriental standard ou décalé

GMT Greenwich

HST,HDT Hawaii-Alaska standard ou décalé

MST,MDT Mountain standard ou décalé

NST Terre-Neuve standard

PST,PDT Pacifique standard ou décalé

YST,YDT Yukon standard ou décalé

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les dates : conversion de date

TO CHAR(date[format[nlslangue]]) conversion d’une date en une chaˆ?ne de caractères.

TO DATE(chaˆ?ne[format[nlslangue]]) conversion d’une chaˆ?ne de caractères en date.

format est une chaˆ?ne de caractères constituée de mots clés nls langue est une chaˆ?ne de la forme :

’NLS DATE LANGUAGE = langue’

langue : french, american, arabic, german (46 langues supportées)

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les dates : conversion de date : formats (1)

SCC, CC siècle avec et sans signe.

SYEAR, YEAR année en lettres avec et sans signe.

SYYYY, YYYY année sur 4 chiffres avec et sans signe.

Y,YYY année sur 4 chiffres avec virgule.

RRRR année sur 4 chiffres ou 2 chiffres avec correction année 2000.

YYY année sur 3 chiffres (les 3 derniers).

YY année sur 2 chiffres (les 2 derniers).

RR année sur 2 chiffres avec correction année 2000.

Y le dernier chiffre de l’année.

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les dates : conversion de date : formats (2)

BC, AD indication BC (Before Christ) ou AD (Ano Domini).

Q N du trimestre de l’année (1-4).

MONTH mois en toutes lettres sur 9 caractères.

MON abréviation du mois sur 3 lettres.

MM N du mois dans l’année.

WW N de la semaine dans l’année (1-53).

IW N de la semaine ISO dans l’année (1-52 ou 1-53).

W N de la semaine dans le mois.

DAY jour en toutes lettres sur 9 caractères.

DY abréviation du jour (2 ou 3 lettres).

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les dates : conversion de date : formats (3)

DDD N du jour dans l’année. DD N du jour dans le mois.

D N du jour dans la semaine.

J jour du calendrier Julien.

AM, PM indication AM ou PM.

HH, HH12 heure sur 12 heures.

HH24 heure sur 24 heures.

MI minutes.

SS secondes par minute (0-59).

SSSSS secondes par jour (0-86399).

”chaˆ?ne” la chaˆ?ne est reproduite telle quelle

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les dates : formats de conversion

les caractères ? / , . ; : sont reproduits tels quels.

utilisation des majuscules et des nimuscules précise le format de sortie.

 exemple : ’MONTH’ donnera ’JUIN’ alors que ’Month’ donnera ’Juin’.

suffixes :

TH ajout du suffixe ordinal

SP nombre en toutes lettres

SPTH, THSP nombre en toutes lettres avec ajout du suffixe ordinal

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les dates : formats de conversion

modificateurs :

FM suppression de blancs et de zéros

FX obligation de respecter exactement le format, les blancs sont significatifs  afficher la date courante sous différentes formes :

SELECT to char(sysdate,’DAY DD MONTH YYYY’) fmt1, to char(sysdate,’FM DAY DD MONTH YYYY’) fmt2, to char(sysdate,’DD/MM/YYYY HH24 :MI :SS’) fmt3

FROM Dual;

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

Fonctions sur les dates : troncature et arrondi d’une date

 TRUNC(date[format])

retourne une date tronquée selon le format spécifié

 ROUND(date[format]) retourne une date arrondie selon le format spécifié

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

troncature et arrondi d’une date : format (1)

SCC, CC siècle (arrondi au milieu du siècle)

SYEAR, YEAR année (arrondi au 1er juillet)

SYYYY, YYYY

YYY, YY, Y

IYYY, IYY, IY, I année ISO (arrondi au 1er juillet)

Q trimestre (arrondi au 16ème jour du 2ème mois du trimestre)

MONTH, MON, MM, RMmois (arrondi au 16ème jour)

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

troncature et arrondi d’une date : format (2)

WW semaine (7 jours) définie à partir du 1er janvier (arrondi au 5ème jour)

IW semaine ISO (arrondi au vendredi)

DAY, DY, D premier jour de la semaine, dépend du pays : lundi/France, dimanche/US, (arrondi au 5ème jour)

DDD, DD, J jour, par défaut lorsque le format est absent

(arrondi à la demi-journée)

HH, HH12, HH24 heure (arrondi à la demi-heure)

MI minute (arrondi à 30 secondes)

Fonctions pour requêtes SQL

Fonctions diverses pour requêtes SQL

troncature et arrondi d’une date : exemple

 Le premier jour de la première semaine de l’année :

SELECT

to char(trunc(to date(’06/01/99’),’WW’),’FM Day DD’), to char(trunc(to date(’06/01/99’),’IW’),’FM Day DD’) from Dual;

SQL : langage de contrôle de données (LCD)

Sécurité des données

 confidentialité gestion des rôles et des utilisateurs attribution de privilèges aux rôles et aux utilisateurs définition de filtres (protection de donées confidentielles, contrôle d’intégrité)

pérennité gestion des transactions

intégrité

gestion des transactions

SQL : langage de contrôle de données (LCD)

transaction : séquence d’opérations manipulant des données vérifient les propriétés suivantes :

atomicité cohérence indépendance permanence

controˆle des transactions :

COMMIT : valide la transaction en cours

ROLLBACK : annule la transaction en cours

Gestion des utilisateurs et des privilèges

 création de roˆle

CREATE ROLE nom-de-rôle [IDENTIFIED BY mot-de passe ];  ajout, modification, suppression de mot de passe

ALTER ROLE nom-de-rôle [IDENTIFIED BY mot-de passe ] ;  suppression de roˆle

DROP ROLE nom-de-rôle;

Gestion des utilisateurs et des privilèges

 création d’utilisateur

CREATE USER nom-d’utilisateur [IDENTIFIED BY mot-de passe ] ;

 ajout, modification, suppression de mot de passe

ALTER USER nom-d’utilisateur [IDENTIFIED BY mot-de passe ] ;

 suppression de roˆle

DROP USER nom-d’utilisateur;

Gestion des utilisateurs et des privilèges

 attribution de privilèges

GRANT systeme-privileges | ALL [privileges ]

TO liste-roles-utilisateurs | PUBLIC

[WITH ADMIN OPTION ] ;

 systeme-privileges :

CREATE ROLE

CREATE SEQUENCE

CREATE SESSION

CREATE SYNONYM

CREATE PUBLIC SYNONYM

CREATE TABLE

CREATE USER CREATE VIEW

Gestion des utilisateurs et des privilèges

 attribution de privilèges sur des objets oracle

GRANT liste-droits

ON nom-composant

TO liste-roles-utilisateurs

[WITH GRANT OPTION ] ;  liste-droits :

SELECT

INSERT

UPDATE

DELETE

ALTER

REFERENCES

ALL [PRIVILEGES ]

Gestion des utilisateurs et des privilèges

 suppression de privilèges

REVOKE liste-systeme-privileges FROM liste-roles-utilisateurs

 suppression de privilèges sur des objets oracle

REVOKE liste-privileges

ON nom-composant

FROM liste-roles-utilisateurs

Gestion des utilisateurs et des privilèges

 attribution de roˆles

GRANT liste-roles

TO liste-roles-utilisateurs

[WITH ADMIN OPTION ] ;

 suppression de roˆles

REVOKE liste-roles

FROM liste-roles-utilisateurs